Get Last Part of a String in SQL

   Sometime we need to get the last part of a string which may contain a char delimiter as a separator or a substring as a separator. If the separator is a char delimiter, then the task goes simple, we just need to Reverse the string and the get the substring from begining to the to the first occurance of the delimiter and then again Reverse the Outputted substring.

    Let's say, we have a string "India,Britain,Australia,Canada,Italy", and want to get "Italy" as the last substring, then write the query as below :

DECLARE @String VARCHAR(MAX)

SET @String = 'India,Britain,Australia,Canada,Italy'

SET @String = REVERSE(@String)




SELECT REVERSE(SUBSTRING(@String,1,CHARINDEX(',',@String,1)-1))


 ----- Output :
 Italy

     If the string has delimiter char at the end of the string as well, that means our string is @String = "India,Britain,Australia,Canada,Italy," then increament the position by 1, as below:

DECLARE @String VARCHAR(MAX)

SET @String = 'India,Britain,Australia,Canada,Italy,'

SET @String = REVERSE(@String)







SELECT REVERSE(SUBSTRING(@String,2,CHARINDEX(',',@String,2)-2))


 ----- Output :

 Italy

   Now, if the separator is a pattern or substring, then make use of PATINDEX as below :

DECLARE @STRING VARCHAR(MAX)

SET @STRING = 'NOT FOUND ONMay  8 2012 12:07PM<br/>NOT FOUND
ONMay  8 2012
12:09PM<br/>May  8 2012  1:08PM<br/>May  8 2012 
1:17PM<br/>May  8 2012  1:25PM<br/>Cash Receipt Found In IMS
Done On : May  8 2012  2:01PM'

   

SELECT REVERSE(SUBSTRING(REVERSE(@STRING),1,PATINDEX('%>/rb<%',SUBSTRING(REVERSE(@STRING),1,LEN(@STRING)-1))-1))


----- Output :
Cash Receipt Found In IMS Done On : May  8 2012  2:01PM

     Again, if the string has separtor substring at the end as well, then increament the position by length of the separtor subsstring, see below:

DECLARE @STRING VARCHAR(MAX)

SET @STRING = 'NOT FOUND ONMay  8 2012 12:07PM<br/>NOT FOUND ONMay  8 2012
12:09PM<br/>May  8 2012  1:08PM<br/>May  8 2012 
1:17PM<br/>May  8 2012  1:25PM<br/>Cash Receipt Found In IMS
Done On : May  8 2012  2:01PM<br/>'



SELECT REVERSE(SUBSTRING(REVERSE(@STRING),6,PATINDEX('%>/rb<%',SUBSTRING(REVERSE(@STRING),6,LEN(@STRING)-6))-1))


 ----- Output :
Cash Receipt Found In IMS Done On : May  8 2012  2:01PM

Reference: Govind Badkur(http://govindbadkur.blogspot.com)