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