sqlsql-serverfull-text-searchfulltext-index

Full Text Search SQL can't find digital value in nvarchar field

I have a Stored Procedure, which uses full-text search for my nvarchar fields. And I'm stuck when I realized, that Full-Text Search can't find field if I type only numeric values of this field.

For example, I have field Name in my table with value 'Request_121'

If I type Запрос_120 or Request - it's okay

If I type 120 - nothing is found

What is going on?

Screenshots:

  1. No results found: https://gyazo.com/9e9e061ce68432c368db7e9162909771

  2. Results found: https://gyazo.com/e4cb9a06da5bf8b9f4d702c55e7f181e

Solution

  • You cannot find 121 word part in your full-indexed column because SQL Server treats Request_121 as a single term. You can verify this by running the fts parser manually:

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

    Returns:

    enter image description here

    while running:

    select * from sys.dm_fts_parser('"Request 121"', 1033, 0, 0)

    Returns:

    enter image description here

    Note, in the second example 121 was picked as separate search term.

    What you could do is to try using wildcards in your FTS query like:

    FROM dbo.CardSearchIndexes idx WHERE CONTAINS(idx.Name, '"121*"');

    However, again I doubt it will pick 121 being inside a non-breakable word part, only if you have 121 as standalone word. Play with sys.dm_fts_parser to see how SQL FTS engine breaks up your input and adjust your query accordingly.

    UPDATE: I've noticed that you use Cyrillic search terms together with English. Notice, when running FTS queries it's also important to know what Language was specified when FTS index was created for Name column. If the FTS language locale is Cyrillic then it will not find English term Request in the Name column.

    Note, in my dm_fts_parser examples above I have used 1033 (English) language id. Examine the LANGUAGE language_term operator in your CREATE FULLTEXT INDEX statement to check what language was used for FTS index.