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

问题:

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')

回答1:

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.

回答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

回答3:

Could you use a LIKE instead of contains?

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

回答4:

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')