How do you search for a & (ampersand) in the tsql contains function?

Question

I have a table T which has a column C which contains text that have the character & within it, but if I do the following query, it returns nothing, although I may have 10 rows.

SELECT * FROM T WHERE Contains(C, 'a&b')

Answer 1

Could you use a LIKE instead of contains?

SELECT * FROM T WHERE C LIKE '%a&b%'

Answer 2

Use like instead:

SELECT * FROM T WHERE C like '%&%'

if you're looking just for &

or

SELECT * FROM T WHERE C like '%a&b%'

if you need to search for a&b

Answer 3

What I do is create a copy of the column that replaces & and other non-alphanumeric characters with _. Then I can do this.

SELECT * FROM T WHERE Contains(C_searchable, 'a_b')

Answer 4

Use double quotes to make it a search term:

SELECT *
FROM T
WHERE CONTAINS(C, '"a&b"')

Otherwise you are searching for something that has a AND b.