Last Updated on Saturday, 06 April 2013
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
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"
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!!!
Done!
Untested but in theory:
- SELECT REVERSE( SUBSTRING( REVERSE( @Haystack ), 1, CHARINDEX( @Delimiter, REVERSE( @Haystack ) ) - LEN( @Delimiter ) ) )
Latest Posts
-
Joes Revolver Map (JRM)
-
Fri 17-May-13
Hmmm... Sounds like a problem with the identifier. Was it working before and has there been a change ...
-
Fri 17-May-13
Hello Joel: Yes, I do have it published on all pages of the site. I just went back to Revolver maps to ...
-
Fri 17-May-13
Hi Bill, From the developers of RevolverMaps, "the module would need to be published on every page ...
-
Fri 17-May-13
Hi Bill, I'll investigate further as you're not the first to say this happens. In the meantime, simply ...
-
Fri 17-May-13
Its a great extension. But when I set it up I only show my presence on the 3D map and no other visitors ...
-
Fri 17-May-13