Fulltext search numbers does not work in SQL Server 2012

Question

Fulltext search numbers does not work in SQL Server 2012.

I tried to create an empty_stoplist and repopulate the index. Can anyone tell me what I am doing wrong here?

CREATE FULLTEXT CATALOG Orders_FTS
WITH ACCENT_SENSITIVITY = OFF;
GO

CREATE FULLTEXT INDEX ON dbo.Orders
( 
     a Language 1031,
     b Language 1031,
     c Language 1031,
     d Language 1031
) 
KEY INDEX [PK_Orders]
ON Orders_FTS; 
GO

CREATE FULLTEXT STOPLIST EMPTY_STOPLIST;
ALTER FULLTEXT STOPLIST empty_stoplist DROP ALL;
ALTER FULLTEXT INDEX ON Orders SET STOPLIST EMPTY_STOPLIST;
ALTER FULLTEXT INDEX ON Orders SET STOPLIST = OFF;
ALTER FULLTEXT INDEX ON Orders START UPDATE POPULATION;

The SQL query:

SELECT
    T.*, R.RANK 
FROM  
    Orders As T 
INNER JOIN  
    CONTAINSTABLE(Orders, *, '"*007440147*"') AS R  On T.ID = R.[KEY]  
ORDER BY 
    RANK DESC, ID DESC

You can't use a wildcard at the beginning of the string. It's a limitation in SQL Server. Could that be the problem? If not, can you provide more detail about what is going wrong (error message? 0 results?) and what strings you are trying to match against? — Keith, Nov 13 '15 at 14:52
@Keith In fact wildcard works best. It simply does not return a result if I search for number parts (wildcard). I wonder this, because all fields I am search through are nvarchar fields. i used ```ALTER FULLTEXT STOPLIST empty_stoplist DROP ALL;``` to search for special chars like ```@``` or ```-``` which works very good but it does not have any effect on numbers. — Stephan Ahlf, Nov 13 '15 at 15:20
Can you provide examples of the terms in the table you are trying to match against? — Keith, Nov 13 '15 at 16:37

score 1 · Accepted Answer · edited May 23 '17 at 11:59

The problem is that leading wildcards (ex: *bcde) are not supported by SQL Server. (More here.) The query will execute without any errors but will always return 0 results. You can only use wildcards in the middle of a string (ex: ab*de) or the end of a string (ex: abcd*).

Usually this can be worked around by creating columns that contain the reverse string and searching on those columns (ex: Column1 = abcde, Column1Reverse = edcba, query has CONTAINS(Column1Reverse, '"edcb*"')).

However in your case you want to use a wildcard at the beginning and end of the string. I think your options are limited to:

If you don't need a leading wildcard, then don't use it. For example, if the text you are trying to match is 007440147xxx then using 007440147* in your query will work fine.
Use LIKE instead of CONTAINSTABLE, for example: SELECT * FROM Orders WHERE Column1 LIKE '%007440147%'. The downside to this approach is that you won't get a rank value and queries may take a long time to execute. (Then again, even if you could use a leading wildcard in full text searches, they would be slow.)
Redesign how the data is stored and queried. I can't offer any suggestions without understanding what these numbers mean and how they need to be queried.
Consider using another search product. I believe Lucene can perform leading wildcard searches but such searches tend to be slow.

Fulltext search numbers does not work in SQL Server 2012

1 Answers1