SQL Server 2012 - Fulltext search on nvarchar field with SHA hashes returns nothing

Question

I have a SQL table with 60 Million records with 2 columns: ID and Hash.

ID is a incremental int PK.

Hash is a nvarchar field with Index.

I created a Fulltext index on the Hash field like this:

CREATE FULLTEXT CATALOG hashes_catalog;
GO
CREATE FULLTEXT INDEX 
  ON dbo.hashes(hash LANGUAGE 1033) 
  KEY INDEX IXC_Hash ON hashes_catalog; 
GO

But when I try to do a FullText search there are no results, for example:

SELECT * FROM Hashes WHERE CONTAINS(hash,'1CE')

Returns nothing.

But data like this exists, this is some sample of data from the table:

1CefATjZSfzDK1bn15tFv5EHzQtxmCkNQL
1CEfatKXLUomearrh7JyKgv4w1Ci1jxM8B
1CefAtyhBVXda5324NwTkfBMkEZ9YcF6vN
1CEfAUbiB2AfqjGpg8r8hxuAxTdzrDPGmv
1CEFAUzKC2Ffi8HwMSfkqTDN8deBTjXnrD
1CEfavd9sVZmLsez8JHKUHHZ7ZEAaKbp6W
1CEFAVfD55it65d6MdQpo3mnnBhBviLTh4
1CEfAVjjGrBQCkLh6qBEfwX46G213DnNhc
1Cefavph9RxQdLfasHR25B3P9W98tCGGus
1CEfavqq739Ny9sH7F1qCS5GzSpVB1Yz5g
1CEFAw68XLVRwzQSP7HNW4kd5z3JRdcPgU

If I execute

SELECT * FROM Hashes WHERE Hash like '%1CE%'

There are 129184 results.

Any ideas how to put fulltext search working for fields like this? Is it even possible?

score 1 · Answer 1 · answered Mar 15 '15 at 14:14

If the hash begins with this term, then you can use the following syntax:

SELECT * FROM Hashes WHERE CONTAINS(hash,'"1CE*"')

Fulltext search is probably not the best bet for searches of this sort. It works by tokenizing your text and if all the text in the field is in one single "word" (with no punctuation), there isn't a major advantage to using fulltext over LIKE.

SQL Server 2012 - Fulltext search on nvarchar field with SHA hashes returns nothing

1 Answers1