SQLTeam.com | Weblogs | Forums

Full text search of SQL server does not return result for strings similar to 0-38766-36120-3 for wildcard search



I have a product table where all the product related metadata is stored in a column called fieldvalue. one of the metadata is UPC code. Full text search is configured on Fieldvalue column.

When I do exact search, I can see the results

select ProductID from PCM_Products_Metadata where contains(FieldValue,'0-38766-36120-3')

but when I do wild card search on any UPC code as below I do not get any results

select ProductID from PCM_Products_Metadata where contains(FieldValue,'"0-38766-36120-3"')

Strangely, if remove 3 or * then I get correct result. Doing exact search is not an option as user can search on any string. I checked with below query to see the display_term which seems to be OK. Also, I have removed all special characters and numbers from stopwordlist

SELECT * FROM sys.dm_fts_parser ('"*0-56327-00564-8"', 1033, 0, 0)

Database used is SQL server 2012 and application is already in production.


So, you solved your problem?


No, this does not solve my problem because user can input full UPC or part UPC hence I will have to do wild card search only. I cannot remove * and also I can not remove last character from the string being searched.


You cannot perform a wildcard search with the asterisk as the first character in a full-text search. One way to accomplish what you are looking for is to create a derived column as the reverse of your column and persist the column - then create a full text index on that column.

Then you can perform a search on the reverse of the input with the wildcard at the end.


I am aware that SQL full text index is basically a starts with Search , My issue is not with Starting character but with ending character.
e.g if user put the UPC as 0-56327 then all the UPC starting with 0-56327 should be shown hence I will need to execute below query

select ProductID from PCM_Products_Metadata where contains(FieldValue,'"0-56327*"')

Lets say if user search for full UPC instead of part of it

select ProductID from PCM_Products_Metadata where contains(FieldValue,'"0-56327-00564-8*"')

In my case, first query is working but the query where I am searching for whole UPC with * in the end does not return any results.In case of Full UPC if I remove * or the last character i.e 8 then result is shown.

This is just one of the example of UPC metadata, user can input text related to other metadatas too so I will always have to put an * at the end and these metadata are dynamic so cannot do any hardcoding.