.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Win Surprise Gifts!!!

Top 5 Contributors of the Month

Home >> Articles >> DataBase >> Post New Resource Bookmark and Share   

 Subscribe to Articles

Find last index of Word from string (starting position or complete position) in SQL SERVER.

Posted By:shiv chandra       Posted Date: June 13, 2011    Points: 200    Category: DataBase    URL: http://www.dotnetspark.com  

Find last index of Word from string. Find starting position or complete position of word from string.

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 StartAtCharacter

Output 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.


 Subscribe to Articles


Further Readings:

Author: shiv chandra         Company URL: http://www.dotnetspark.com
Posted Date: June 13, 2011

Hi All,
This time i have try something new, try to give proper look to keywords and string as shown in SQL Server.
Please Let me know weather this look is good or I should go for normal one.

Post Comment

You must Sign In To post reply
Find More Articles on C#, ASP.Net, Vb.Net, SQL Server and more Here

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend