Finding the "&" character in SQL SERVER using a like statement and Wildcards

Question

I need to find the '&' in a string.

SELECT * FROM TABLE WHERE FIELD LIKE ..&...

Things we have tried :

SELECT * FROM TABLE WHERE FIELD LIKE '&&&'
SELECT * FROM TABLE WHERE FIELD LIKE '&\&&'
SELECT * FROM TABLE WHERE FIELD LIKE '&|&&' escape '|'
SELECT * FROM TABLE WHERE FIELD LIKE '&[&]&'

None of these give any results in SQLServer. Well some give all rows, some give none.

Similar questions that didn't work or were not specific enough.

Find the % character in a LIKE query

How to detect if a string contains special characters?

some old reference Server 2000

http://web.archive.org/web/20150519072547/http://sqlserver2000.databases.aspfaq.com:80/how-do-i-search-for-special-characters-e-g-in-sql-server.html

Answer 1

& isn't a wildcard in SQL, therefore no escaping is needed.

Use % around the value your looking for.

SELECT * FROM TABLE WHERE FIELD LIKE '%&%'

Answer 2

Your statement contains no wildcards, thus is equivalent to WHERE FIELD = '&'.

& isn't a special character in SQL so it doesn't need to be escaped. Just write

WHERE FIELD LIKE '%&%'

to search for entries that contain & somewhere in the field

Be aware though, that this will result in a full table scan as the server can't use any indexes. Had you typed WHERE FIELD LIKE '&%' the server could do a range seek to find all entries starting with &.

If you have a lot of data and can't add any more constraints, you should consider using SQL Server's full-text search to create and use and FTS index, with predicates like CONTAINS or FREETEXT