Wikis - Page Details

First published by Johnny Bell Jnr
When: 18 Jun 2013 12:24 PM
Last revision by Richard Mueller (cMVP, Microsoft Community Contributo)
When: 8 Jul 2013 3:22 PM
Revisions: 5
Comments: 7

Options

Can You Improve This Article?

Positively! Click Sign In to add the tip, solution, correction or comment that will help other users.

Report inappropriate content using these instructions.

T-SQL: RIGHT, LEFT, SUBSTRING and CHARINDEX Functions

RIGHT and LEFT
CHARINDEX
SUBSTRING
USING THEM TOGETHER

This article explains the functionality and uses of the LEFT, RIGHT, SUBSTRING and CHARINDEX functions in SQL.

This article will leave you with sound knowledge and understanding that you can take away and questions will be asked no more.

We''ll start by explaining each function individually with examples; and then I will show a scenario where these functions can be used together.

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.

As you can see by this illustration, the LEFT function starts BEFORE the left-most character of a string and moves to the right, while the RIGHT function starts AFTER the right-most character and moves inwards to the left.



SELECT RIGHT('HELLO WORLD', 3);

SELECT LEFT('HELLO WORLD', 3);

Here's the result:

As you can see, the RIGHT function has expectedly taken the last three characters of the string passed into it, and the LEFT function has taken the first three. Pretty simple!

CHARINDEX

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');

Here's the result:

As you can see, the position of the space within "Hello World" is the 6th character. CHARINDEX can be a useful function for finding occurrences of a character within a table programmatically. I will build on this subject later on in this article.

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:

As you can see. SUBSTRING includes spaces as a position within a string. So executing this query shows a "window" of the string that has been passed to it. If we had executed the query as "SELECT SUBSTRING('HELLO WORLD',6,5)" then the results would have shown " WORL".

USING THEM TOGETHER

Now I'm going to show an example of how to use these together. Imagine you have a table with a column called "Name", within that column you have various names, with different lengths; but all have one thing in common, a space. You're asked to only display the forename, but because there are differing lengths you will need to find the occurring space in the string.

SELECT CHARINDEX(' ','JOHNNY BELL')

We can use the CHARINDEX function to find the position of the space within the row programmatically. In this instance, the position is "7". Now we've found that we just need to display everything left of that position. We can "wrap" this up within a LEFT statement to do this (simple right?!).

SELECT LEFT('HELLO WORLD',CHARINDEX(' ',

'HELLO
 WORLD'

)-1)

Notice how I've put a "-1" after the CHARINDEX function? This is because the CHARINDEX function is finding the space in the string, but we don't really want to include this in our resultset, so we're basically saying "find the position of the space minus one". A good way to see this is by looking at the illustration from earlier and count the positions that the function will step over. Remember that the LEFT function takes two arguments, so we need to state the expression within that as well. This will of course return the result "JOHNNY".

I hope this helps, thanks for reading and if you have any further questions then don't hesitate to comment below.

This entry participates in the TechNet Guru contributions for June contest.