SQL Server Full Text Search ampersand (&)

have problem with ampersand (&)

How to search for the words (or sentences) that contain an ampersand (&).

For example, in the database are:

1: "Johnson & Johnson"
2: "AT&T"
3: "Sample & Sample"

How should I write a full text search query to search for individual records?

SELECT * from Companies c WHERE CONTAINS(c.CompanyName, '"AT&T"')

I know that character (&) is responsible for the logical AND operation. But I do not know how to encode it to search in text with use full text search.

Any idea?

Solution

Short version: You can't (or at least you can, but you may get more results than you expected)

Long version: The character '&' is treated as a "word breaker", i.e. when SQL Server encounters an '&' it treats it as the start of a new "word" (i.e. token). What SQL Server Sees when parsing "AT&T" is two tokens, "AT" and "T".

You can check this for yourself using sys.dm_fts_parser:

SELECT * FROM sys.dm_fts_parser('AT&T', 1033, 0, 0)

keyword     group_id    phrase_id   occurrence  special_term  display_term  expansion_type source_term
----------- ----------- ----------- ----------- ------------- ------------- -------------- -----------
0x00610074  1           0           1           Noise Word    at            0              AT
0x0074      2           0           1           Noise Word    t             0              T

This means that search for "AT&T" is pretty much exactly the same as just searching for "AT T".

This is by design, as far as I can see the only way to modify this behaviour would be to install your own word breaker, however this isn't something that I would recommend doing.