Sql query with special characters - how to handle?

Question

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?

Answer 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%'