5

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

Community
  • 1
  • 1
r_j
  • 1,348
  • 15
  • 35

2 Answers2

11

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

Use % around the value your looking for.

SELECT * FROM TABLE WHERE FIELD LIKE '%&%'
CL.
  • 173,858
  • 17
  • 217
  • 259
Matt
  • 14,906
  • 27
  • 99
  • 149
4

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

Panagiotis Kanavos
  • 120,703
  • 13
  • 188
  • 236