Welcome to Joel Lipman .Com

Preparing Content...


Loading...

Our Website Development Notes

We hope this helps!

Articles // SQL // Transact-SQL

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

Monday, 28th January 2013
42,273 Reads
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(@Delimiter, REVERSE( @Haystack ))  
  16. -- yields "8"  
  17.  
  18.  
  19. SELECT SUBSTRING(@Haystack, CHARINDEX(@Delimiter, REVERSE(@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(@Delimiter, REVERSE(@Haystack))  
  2. -- yields "23"  
  3.  
  4.  
  5. SELECT SUBSTRING(@Haystack, LEN(@Haystack) - CHARINDEX(@Delimiter, REVERSE(@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(@Delimiter, REVERSE(@Haystack)) + 2  
  2. -- yields "25"  
  3.  
  4.  
  5. SELECT SUBSTRING(@Haystack, LEN(@Haystack) - CHARINDEX(@Delimiter, REVERSE(@Haystack)) + 2, LEN(@Haystack))  
  6. -- yields "String4" Yay!!!


Done!

Untested but in theory:
  1. SELECT REVERSE( SUBSTRING( REVERSE( @Haystack ), 1, CHARINDEX( @Delimiter, REVERSE( @Haystack ) ) - LEN( @Delimiter ) ) )


Article Comments (3)

Thursday, 29th December 2016
1 Vote
Gravatar for Robert
Robert
Very helpful thanks! This worked for me as well, and is a little shorter. Not sure if there are drawbacks.
SELECT RIGHT(@Haystack,CHARINDEX(@Delimiter,REVERSE(@Haystack))-LEN(@Delimiter))

Tuesday, 21st June 2016
0 Votes
Gravatar for Anvesh
Anvesh
Nice Article !
This is my pleasure to read your article.
Really this will help to people of SQL Server Community.

I have also prepared one article about, Script to count number occurrences of string in SQL Server.
You can also visit my article, your comments and reviews are most welcome.

http://www.dbrnd.com/2016/02/sql-server-tsql-script-to-find-count-the-number-of-occurrences-of-a-string/

Sunday, 1st September 2013
0 Votes
Gravatar for San
San
Very helpful. In the last example that you list as being "Untested", you could have used LEFT instead of SUBSTRING.


Recent Comments

Gravatar for Art
Oracle: order by subquery missing right parenthesis
Hello, what if I would like to add rownum to that code, but in my case it's all is subquery? How can I do this ?

20 Jan


Gravatar for Robert
CharIndex Reverse - find occurrence starting from end of string in TSQL
Very helpful thanks! This worked for me as well, and is a little shorter. Not sure if there are drawbacks. SELECT RIGHT(@Haystack,CHARINDEX(@Delimiter,REVERSE(@Haystack))-LEN(@Delimiter))

29 Dec


Gravatar for Translation

27 Dec


Gravatar for Tibbe
JComments 2.3.0 with ReCaptcha in Joomla 2.5.x
Hi there, This looks like a great solution to get rid of spam comments. How to integrate this in Joomla 3.x? Step 2 I did in settings.xml, but no result in frontend. Kind Regards, Tibbe

16 Dec


Gravatar for sach|n
SSRS Repeat Headers in PDF Report
Yes its working for me..thnx Joel.

7 Oct