SQL LIKE Command trouble searching for '

Question

Hi I am facing a problem with the like command in SQL,

I want to search for special characters within a column . The special characters are a single quotation mark ' and { and }..

I have tried placing these special characters under [] but still it doesn't work for '

I have also used the except option but that was also of no help..

Waiting for a response soon

Answer 1

When you specify a value which has single quote, you need to double it.

SELECT  *
FROM    dbo.Northwind
WHERE   Summary LIKE 'single''quotes%'

Answer 2

Try using this-

select * from <table> where <column> like '%''%' 

Answer 3

SQL Server escaping is a pain because there are various ways to escape characters, each with different meaning and use case.

A single quote is escaped with another single quote: WHERE myfield LIKE '%''%'.

Answer 4

The general solution is to escape the special character like so:

SELECT .... WHERE my_column like '%\'%' ESCAPE '\'