How to locate last occurrence of character in string?

[Guest_imported]

How to locate last occurrence of character in string?

 

[tlbroadbent]

In SQL 7 and 2000 you can use the following query.

Select
ColA,
Loc=Case charindex('a',reverse(ColA))
When 0 Then 0
Else 1+len(rtrim(ColA))-charindex('a',reverse(ltrim(ColA)))
End
From table

Or if you only want to see those strings that match, use this query.

Select
ColA,
Loc=1+len(rtrim(ColA))-charindex('a',reverse(ltrim(ColA)))
From table
Where charindex('a',reverse(ColA))>0 Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.