Sql query with special characters - how to handle?

问题:

I've few emp names like

1. john,1
2. devil's
3. corn

something like this

Now when i'm searching for these names I'm using

select * from emp where empname like ('john,1,devil's,corn')

But I'm not getting expected values also I'm getting error because emp name contains special charecters like , and '.

Can someone help me out how to solve this?

回答1:

This assumes you have 3 discrete names in your example string

Exact match. you need to double up quotes.

select * from emp where
empname IN ('john,1' , 'devil''s', 'corn')

You can't LIKE/IN in SQL Server too.

select * from emp where
  empname like '%john,1%'
  OR
  empname like '%devil''s%'
  OR
  empname like '%corn%'

回答2:

if you use mysql:

select * from emp where empname like ('john,1','devil\'s','corn')

回答3:

select * from emp where empname like ('john\,1','devil\'s','corn\'') escape '\'

use keyword escape to mention escape character for the query.

回答4:

If you are looking for an empname of "devil's" then I agree with the use of the escape character that Sachin and Ratinho used.

However, the like clause is used for something that is like another. For example, if you are looking for something that starts with "devil's" then you might use

select * from emp where empname like ('devil\'s%')

which would match

devil's peak
devil's beard
devil's

but not

Devil's peak
deviled eggs
devil

If you want to match an empname with "devil's" in the middle, then you might use

select * from emp where empname like ('%devil\'s%')

回答5:

For most versions of SQL, you need to escape the single quote, for example.

select * from emp where empname like ('john,1,devil''s,corn')

Also, the above example is looking for a very specific string value, you need to include * or ? as wildcard characters, so to look for all empname's like devil's, use

select * from emp where empname like '%devil''s%'

Another example

select * from emp where empname like 'john,1%'