Sql-server – Full-Text Search against serial numbers

full-text-searchmicrosoft-dynamicssql server

Using Dynamics 365 with MSSQL. There is an option to enable Full Text Search, however once it is enabled we cannot search by partial serial numbers.

For instance, KN567228 shows in the full text catalog but trying to search for 7228 will return no results. The Stoplist is empty, so that isn't the cause. This is a unique situation for Full-Text Search because there really isn't a thesaurus or wordbreaker that could break down serial numbers in a meaningful way. But I thought the search would work for partial text (like searching for 7228 in KN567228).

Should I just not be using Full-Text Search in this scenario?

Best Answer

Full-Text Search would be able to efficiently Search for a word with a prefix (Prefix Term) or Search for a specific word or phrase (Simple Term), but not a suffix as you described. You can achieve what you need using LIKE.

As you can see in Eugene Elutin's answer for a Using Full-Text Search thread:

partial and suffix match in T-SQL can be only done using LIKE, which will do full table scan to find relevant rows.

Here's one way of doing the search you described

Full-text search only supports prefix search. In some circumstances, reversing the string, indexing that, and then searching for the reverse of the suffix can be made to work reasonably well. Fundamentally, though, FTS is not designed for the purpose you require.

But that approach would fail to find this other serial KN5672281 when trying to search for 7228.

Related Question