Search Suffix using Full Text Search in SQL Server 2005

A solution to search Suffix is given below as its a limitation in FTS that FTS cannot search suffix.

Its not recommended and not efficient as it requires another column but its a solution which can help you out in some cases.


1. Add a column in your table which stores the reverse of the string
like
SET NewColumnName = REVERSE(ColumnName)

2. CREATE PROCEDURE sps_searchSuffix(@searchString varchar(8000)) AS

SET @searchString = REVERSE(@searchString)

DECLARE @Q nVARCHAR(MAX)
SET @Q = 'SELECT * FROM TableName WHERE CONTAINS (ColumnName,''"'+@searchString+'*"'''+')'
EXEC SP_EXECUTESQL @Q


3. And call it like this if you want to search "garding" and you have a data like "regarding"

DECLARE @ST VARCHAR(500)
SET @ST = 'garding'
PRINT @ST
EXEC sps_searchSuffix @ST