How FTS works with abbreviations

Question

question

Natalya_K asked Mar 1, '21

How FTS works with abbreviations

We've faced a problem how FTS works with abbreviations. There are two rows with values "R.W. Mead & Sons" and "r.w. Mead & Sons1" in the full-text indexed column. The following query returns only "r.w. Mead & Sons1"

SELECT * FROM Table_Name WHERE CONTAINS ([Column_Name],N'"r.w. Mead*"')

How to do that the query returns both rows "R.W. Mead & Sons" and "r.w. Mead & Sons1"?

Microsoft SQL Server 2016 (SP2-CU15) is on a server. A stoplist is off for the index. LCID= 1033.

This is how data is saved in the index

r.w. Mead & Sons1

R.W. Mead & Sons

Any help is appreciated. Thanks!

sql-server indexing full-text

1614589436307.png (107.4 KiB)

1614589855707.png (94.2 KiB)

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Answer 1 · 2021-03-03T16:27:25Z

Kev Riley answered Mar 3, '21

That's because the period has a special meaning in full-text indexes - it's used as a punctuation wordbreaker

The easiest way around this is to remove the period from your search string before querying, like

declare @SearchString varchar(50)
set @SearchString = '"r.w. Mead"'
set @SearchString = replace(@SearchString,'.',' ')

SELECT * FROM Table_Name WHERE CONTAINS ([Column_Name],@SearchString)

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

question