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

SQL Server Administration
1

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.

2

So, you solved your problem?

3

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.

4

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.

5

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.