question

hungrydba avatar image
hungrydba asked

sql 2014

The search results at my prod sql server ( MS SQL 2014 ) are not consistent.


The example is below,


1. SELECT * FROM item where contains(description , '"*28242*"') and itemnum ='5003940';

No row found


2. SELECT * FROM item where description like '%28242%' and itemnum ='5003940';

The expected row found ( 1 row )


Is this normal ?


How do we fix the issue ?

Many thanks


sql-server-2008administrationsql server 2014sql-server-2016
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

·
anthony.green avatar image
anthony.green answered

You can't do a suffix search on a full text catalog only prefix searches, so the leading * is throwing it off

It would have to be something like the below


SELECT * FROM item where contains(description , '"28242*"') and itemnum ='5003940';

10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.