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.

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

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".

Perhaps we should determine the position of the last needle first (reverse the haystack string and find needle):
DECLARE @Haystack VARCHAR(31);
SET @Haystack = 'String1.String2.String3.String4';
-- I want "String4" from Haystack

SET @Delimiter = '.';

PRINT @Haystack;
-- yields "String1.String2.String3.String4"

SELECT REVERSE( @Haystack );
-- yields "4gnirtS.3gnirtS.2gnirtS.1gnirtS"

SELECT CHARINDEX(@Delimiter, REVERSE( @Haystack ))
-- yields "8"

SELECT SUBSTRING(@Haystack, CHARINDEX(@Delimiter, REVERSE(@Haystack)), LEN(@Haystack))
-- 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:
SELECT  LEN(@Haystack) - CHARINDEX(@Delimiter, REVERSE(@Haystack))
-- yields "23"

SELECT SUBSTRING(@Haystack, LEN(@Haystack) - CHARINDEX(@Delimiter, REVERSE(@Haystack)), LEN(@Haystack))
-- 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):
SELECT LEN(@Haystack) - CHARINDEX(@Delimiter, REVERSE(@Haystack)) + 2
-- yields "25"

SELECT SUBSTRING(@Haystack, LEN(@Haystack) - CHARINDEX(@Delimiter, REVERSE(@Haystack)) + 2, LEN(@Haystack))
-- yields "String4"  Yay!!!


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

Add comment



0 Robert B Thursday, 29th December 2016, 6:46 pm
Very helpful thanks! This worked for me as well, and is a little shorter. Not sure if there are drawbacks.
SELECT RIGHT(@Haystack ,CHARINDEX(@Del imiter,REVERSE( @Haystack))-LEN (@Delimiter))
0 Anvesh M Tuesday, 21st June 2016, 9:19 am
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.

0 SanSettles Sunday, 1st September 2013, 10:22 pm
Very helpful. In the last example that you list as being "Untested", you could have used LEFT instead of SUBSTRING.