question

Natalya_K avatar image
Natalya_K asked

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-serverindexingfull-text
1614589436307.png (107.4 KiB)
1614589855707.png (94.2 KiB)
10 |1200 characters needed characters left characters exceeded

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

1 Answer

· Write an Answer
Kev Riley avatar image
Kev Riley answered

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 characters needed characters left characters exceeded

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

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

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