T-SQL CONTAINS with numbers and dots (.)

Question

Let's consider User.Note = 'Version:3.7.21.1'

SELECT * FROM [USER] WHERE CONTAINS(NOTE, '"3.7.2*"')

=> returns something

SELECT * FROM [USER] WHERE CONTAINS(NOTE, '"3.7*"')

=> returns nothing

If User.Note = 'Version:3.7.21'

SELECT * FROM [USER] WHERE CONTAINS(NOTE, '"3.7*"')

=> returns something

If User.Note = 'Version:3.72.21'

SELECT * FROM [USER] WHERE CONTAINS(NOTE, '"3.7*"')

=> returns nothing

I can't figure out how it works. It should always returns something when I search for "3.7*".

Do you know what's the logic behind this ?

PS: if I replace the numbers by letters, there's no problem.

Answer 1

I think your problem is being caused by the unpredictability of the word breaker interacting with the punctuation marks within the data. Full text search is based on the concept of strings of characters, not including spaces and punctuation. When the engine is building the index it sees the periods and breaks the word in weird ways.

As an example, I made a small table with the three values you provided...

VALUES (1,'3.7.21.1'),(2,'3.7.21'),(3,'3.72.21')

Now when I do your selects, I get results on all four... not the results I expect, though.

For me, this returns all three values

SELECT * FROM containstext WHERE CONTAINS(secondid, '"3.7.2*"')

and this returns only 3.7.21

SELECT * FROM containstext WHERE CONTAINS(secondid, '"3.7*"')

So let's run this and take a look at the contents of the full text index

SELECT * FROM sys.dm_fts_index_keywords(db_id('{databasename}'), object_id('{tablename}'))

For my results (yours are quite probably different) I've got the following display_term values

display_term     document_count
    21              3
    3               3
    3.7.21          1
    7               2
    72              1

So let's look at the first search criterion '"3.7.2*"' If I shove that into sys.dm_fts_parser...

select * from sys.dm_fts_parser('"3.7.2*"', 1033, NULL, 0)

...it's showing me that it's breaking with matches on

3
7
2

But if I do...

select * from sys.dm_fts_parser('"3.7*"', 1033, NULL, 0)

I'm getting a single exact match on the term 3.7 and sys.dm_fts_index_keywords told me earlier that I only have one document/row that contains 3.7

You might also experience additional weirdness because numbers 0-9 are usually in the system stopwords and can be left out of an index because they're considered to be useless. This might be why it works when you change to letters.

Also, I know you've decided to replace LIKE, but Microsoft has suggested that you only use alphanumeric characters in your full text indexes and, if you need to use non-alphanumeric characters in search criteria, you should use LIKE. Perhaps changing the periods to some alphanumeric replacement that won't be used in normal values?

Answer 2

Contains will only work if the column is in a full text index. If it it is not indexed you will need to use like:

SELECT * FROM [USER] WHERE NOTE like '3.7%' --or '%3.7%

Are you wanting to use CONTAINS because you think it will be faster?(It generally is)

The Microsoft document lists all the ways you can format and use CONTAINS(11 examples)

Here is the Microsoft doc on CONTAINS