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

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



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.
Leave a Comment
  • Please add 7 and 1 and type the answer here:
  • Post
Wiki - Revision Comment List(Revision Comment)
Sort by: Published Date | Most Recent | Most Useful
Comments
  • Richard Mueller edited Revision 3. Comment: Make headings for TOC

  • Ed Price - MSFT edited Revision 1. Comment: TOC and tags

Page 1 of 1 (2 items)
Wikis - Comment List
Sort by: Published Date | Most Recent | Most Useful
Posting comments is temporarily disabled until 10:00am PST on Saturday, December 14th. Thank you for your patience.
Comments
  • Why don't you talk about PATINDEX as well?

  • Although I understand the concept, it's not really something we use in our environment at work.

  • Ed Price - MSFT edited Revision 1. Comment: TOC and tags

  • Richard Mueller edited Revision 3. Comment: Make headings for TOC

  • Hi Jonny

    Using the LEFT and CHARINDEX together is exactly what I need, however, when trying to use it in the following statement I get an error

    SELECT LEFT(t.[tda grade],CHARINDEX('.',t.[tda grade])-1)

    (Where t.[tda grade] has the value "SEO..CSF National" - all I want returned is "SEO")

    Msg 537, Level 16, State 3, Line 1

    Invalid length parameter passed to the LEFT or SUBSTRING function.

    Any ideas?

  • Hi Richard,

    I can't seem to find any issues with the statement when I run "SELECT LEFT('SEO..CSF National',CHARINDEX('.','SEO..CSF National')-1)", so I'm assuming it's something to do with the resultset that the statement is operating on.

    Ensure that your statement does include a where statement:

    SELECT LEFT(t.[tda grade],CHARINDEX('.',t.[tda grade])-1)

    WHERE t.[tda grade] = 'SEO..CSF National'

    If the value you're looking for in CHARINDEX does not exist in the string then the value passed to the LEFT function will be invalid, hence the error. Expanding on my answer, it seems you need to return the value "SEO" from that string, so why not just use SELECT LEFT(t.[tda grade],3) WHERE t.[tda grade] = 'SEO..CSF National' or SUBSTRING(t.[tda grade],1,3) WHERE t.[tda grade] = 'SEO..CSF National'??

  • Thanks for the prompt response Jonny.

    The field contains multi values, such as "SCS Band 1..CSF National", which is why I thought this method would be ideal.  What you've suggested did make me examine the data, and I've found one value which doesn't have a '.', therefore the result of the CHARINDEX was 0.  

    By adding the -1 to the expression meant the LEFT function was trying to start at -1, hence the error!

    Thanks for your help

Page 1 of 1 (7 items)