Tuesday, March 31, 2015
   
Text Size
Login

CharIndex Reverse - find occurrence starting from end of string in TSQL

What?
This is a quick note on finding the last occurrence of a string in a longer string. This has to be in Transact SQL for a SQL Server instance only and not filtered by other code.


Why?
I have a string such as the following (column positions added for demo purposes):

  1.  String1.String2.String3.String4 
  2.  1   5   10   15   20   25   30 -> length = 31 
I'd like to end up with just the last part of this, ie "String4". So I need to delimit based on the dot/period (.) and use substring in a sort of reversed form.

For argument's sake, I'm assigning this string to the variable "haystack".


How?
Perhaps we should determine the position of the last needle first (reverse the haystack string and find needle):

  1.  DECLARE @Haystack VARCHAR(31); 
  2.  SET @Haystack = 'String1.String2.String3.String4'
  3.  -- I want "String4" from Haystack 
  4.   
  5.  SET @Delimiter = '.'
  6.   
  7.  PRINT @Haystack
  8.  -- yields "String1.String2.String3.String4" 
  9.   
  10.   
  11.  SELECT REVERSE( @Haystack ); 
  12.  -- yields "4gnirtS.3gnirtS.2gnirtS.1gnirtS" 
  13.   
  14.   
  15.  SELECT CHARINDEX(@DelimiterREVERSE( @Haystack )) 
  16.  -- yields "8" 
  17.   
  18.   
  19.  SELECT SUBSTRING(@HaystackCHARINDEX(@DelimiterREVERSE(@Haystack)), LEN(@Haystack)) 
  20.  -- yields ".String2.String3.String4" 


Not quite right, as we got the last occurrence counting from the beginning rather than the end, so we still need to work out the starting point:

  1.  SELECT  LEN(@Haystack) - CHARINDEX(@DelimiterREVERSE(@Haystack)) 
  2.  -- yields "23" 
  3.   
  4.   
  5.  SELECT SUBSTRING(@HaystackLEN(@Haystack) - CHARINDEX(@DelimiterREVERSE(@Haystack)), LEN(@Haystack)) 
  6.  -- yields "3.String4" 


Almost there, counting backwards and forwards it seems the index is just two characters off (as I don't want to include the period):

  1.  SELECT LEN(@Haystack) - CHARINDEX(@DelimiterREVERSE(@Haystack)) + 2 
  2.  -- yields "25" 
  3.   
  4.   
  5.  SELECT SUBSTRING(@HaystackLEN(@Haystack) - CHARINDEX(@DelimiterREVERSE(@Haystack)) + 2, LEN(@Haystack)) 
  6.  -- yields "String4"  Yay!!! 


Done!

Untested but in theory:

  1.  SELECT REVERSE( SUBSTRING( REVERSE( @Haystack ), 1, CHARINDEX( @DelimiterREVERSE( @Haystack ) ) - LEN( @Delimiter ) ) ) 

Comments   

SanSettles
# SanSettles Sun, 1st September 2013
Very helpful. In the last example that you list as being "Untested", you could have used LEFT instead of SUBSTRING.
Like | Dislike | 0 Reply | Reply with quote | Quote
Add Comment

Name:

Email:

Website:

Message:


Help Develop!

Thank You! :c)



Connect: Google+

Visitors of the World

Latest Posts

  • Basic Android App using Google Maps and Current Location

    • Sat 28-Mar-15
      Hi Vsiqueira, I used to get a similar problem if I switched my android device off and on again.
      Joel L.  
    • Fri 27-Mar-15
      hi, im getting an erro, in the method MyLocation.getL ocation(), return null, how i can resolve ...
      vsiqueira  
    • Wed 25-Mar-15
      Hi Rishikesh, I use a database on a webserver with all the latitudes and longitudes of the points of ...
      Joel L.  
    • Tue 24-Mar-15
      At the starting asa I run the app. I want to show list of garages available near by my current location.
      Rishikesh
  • JComments 2.3.0 with ReCaptcha in Joomla 2.5.x

    • Mon 23-Mar-15
      Worked like a charm first time! :lol: Getting bored of the spam comments. This should stop them. :P
      Graham