SQLTeam.com | Weblogs | Forums

Help with Reverse String Search


#1

Is there a reverse string search function in SQL Server 2008 or higher? I want to search a character from the end to the beginning of a string. The function needs to return the position at which the character is found. If there is no such built-in function, could someone please show me the code on how to do it programmatically?

Thanks very much! :slightly_smiling_face:


#2
DECLARE	@MyString varchar(100) = 'ABC123XYZ'

SELECT	CHARINDEX('XYZ', @MyString)
	, CHARINDEX('ZYX', REVERSE(@MyString))
	, CHARINDEX(REVERSE('XYZ'), REVERSE(@MyString))

#3

Thank you so much Kristen!

So there's no built-in function and I have to reverse the string before I search with CharIndex. It would have been nice -- and faster too -- if there's already a built-in function to do this. :slight_smile:


#4

Well - you could always create a computed column as the REVERSE of the actual column, index the computed column and then perform a normal CHARINDEX/PATINDEX on that reversed column.


#5

Good idea, Jeff! Thanks!