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