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

: Joel Lipman; Transact-SQL; 28 January 2013; Hits: 40454

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):

copy raw

String1.String2.String3.String4
1   5   10   15   20   25   30 -> length = 31

String1.String2.String3.String4
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):

copy raw

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"

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:

copy raw

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


SELECT SUBSTRING(@Haystack, LEN(@Haystack) - CHARINDEX(@Delimiter, REVERSE(@Haystack)), LEN(@Haystack))
-- yields "3.String4"

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):

copy raw

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!!!

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!!!

Done!

Untested but in theory:

copy raw

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

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

Category: Transact-SQL :: Article: 478

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

Credit where Credit is Due:

Related Articles

Joes Revolver Map

Joes Word Cloud

Accreditation

Donate & Support