RIGHT and LEFT
These functions might sound fairly self explanatory, but unless you have a fundamental understanding of how they behave; you will experience some problems that will keep you scratching your head for a while.
SELECT RIGHT('HELLO WORLD', 3);
SELECT LEFT('HELLO WORLD', 3);
CHARINDEX is another simple function that accepts two arguments. The first argument is the character you are searching for; the second is the string. It will return the first index position that the character passed into the first argument is within the string.
Now let’s use our CHARINDEX function to find the position of the space in this string:
SELECT CHARINDEX(' ','Hello World');
SUBSTRING
I would consider SUBSTRING to be the most useful of all the functions mentioned today. It accepts three arguments, the string, a start position and how many characters it will “step over”. Let’s take a look at that illustration from earlier:
Now I’ll write a simple query to show the use of SUBSTRING:
SELECT SUBSTRING('HELLO WORLD',4,5)
And now the results:
Note: if you want the SUBSTRING Function to pick up any characters to the end, putting a large number at the end (bear in mind the field size) will accomplish this, e.g
SELECT SUBSTRING('HELLO WORLD',4,100)