Wednesday, October 6, 2010

Find the Last Apparition of a Value in String

Well, indeed, I spent few minutes today trying to find the last apparition of a value in string. All this in a  simple T-SQL query in SQLServer 2005. The reason I wanted this is that I need the last part of a string separated by a known delimiter.
I was having a string like this:
declare @a as nvarchar(50)
set @a = 'ssss$kkkk$ooooo$abcde'

I searched the Internet a bit, but with no success. But after few minutes I remembered a function that I seldom use, the reverse. From here, all was as simple as:
select charindex ('$', reverse('ssss$kkkk$ooooo$abcde') )
and further play with this to get the value I need

Update: Of course, using it to trim out the last part of the string is going to be something like this:
select Substring(@a, 1, Len(@a) - charindex ('$', reverse(@a)))