CODEWITHSUNDEEP
sqlsql-server-2008escaping
Amrit
Amrit

Reputation: 421

SQL Like Clause with escape single quote

Hi I am trying to run this query in SQL but it is not giving me the result :

SELECT        COUNT(*) AS Expr1
FROM            restaurantData
WHERE        (restaurantInfo LIKE '''%' + 'xyz' + '%''') OR
                         (restaurantName LIKE '''%' + 'xyz' + '%''') OR
                         (restaurantDescription LIKE '''%' + 'xyz' + '%''')

I have restaurantName column with value xyz Restaurant

Here is some sample data

Upvotes: 2

Views: 6465

Answers (2)

N West
N West

Reputation: 6819

When the string concatenation is performed here's what you are using to compare the results to:

'%Guu%'

When you put two single quote characters one after another in a SQL string, it is "escaped", so '' is used as '. If your data set includes the quote characters, you will match things like:

restaurantInfo 
--------------
'ABCGuuDEF'
'xyzGuuamn'

I believe you instead want to do:

'%''' + 'Guu' + '''%'

Which would match

restaurantInfo 
--------------
Asdf'Guu'
'Guu'Asdf

Or, if you are just looking for Guu without any quotes, just do

'%' + 'Guu' + '%'

Which would match

restaurantInfo 
--------------
Guu
GuuAbcd
AbcdGuu
Asdf'Guu'
'Guu'Asdf

Upvotes: 2

Stuart Ainsworth
Stuart Ainsworth

Reputation: 12940

Why the extra single-quotes?

   SELECT
            COUNT(*) AS Expr1 
    FROM  restaurantData 
    WHERE        (restaurantInfo LIKE '%Guu%') 
OR (restaurantName LIKE '%Guu%') 
    OR (restaurantDescription LIKE '%Guu%')

It looks like you're trying to build it dynamically; if that's the case, then I'd suggest you post how you're building it.

Upvotes: 4

Related Questions