If you want to find last index of
character from given string, it seems to be very easy, and very easily you can
find example for that.
But Once I was come across
situation, when starting position of word is required from end of string.
Finally I got the below solution after lot of searching. So here is solution
for you, if you come across such situation just CTRL+C and CTRL+V :)
--Find last index of a word from string.
--For example: get the last index of 'SELECT' in the below query
--Use REVERSE and find the first one
DECLARE @Query varchar(MAX), @StringToFind varchar(MAX);
SET @Query= 'SELECT from SELECT where SELECT = SELECT Word'
SET @StringToFind = 'SELECT'
--Query for Start position
SELECT REVERSE(@Query) AS ReverseString,
REVERSE(@StringToFind) AS ReverseWord,
PATINDEX(REVERSE('%' + @StringToFind + '%'), REVERSE(@Query)) AS StartAtCharacterOutput of query: Start position
--Query for complete position
SELECT REVERSE(@Query) as ReverseString,
REVERSE(@StringToFind) as ReverseWord,
PATINDEX(REVERSE('%'+@StringToFind+'%') , REVERSE(@Query))+LEN(@StringToFind) AS CompleteAtCharacter
Output of query: Complete position
Any suggestions are most welcome.