0

I am missing something about single quotes and in statements that would be very helpful.

Select *
from list
where first_name in ('JAMES','JACK');

returns the list of all people with the first name of James and Jack.

However, when doing:

Select *
from list
where first_name in (:names);

What do I have to set the names variable to? I've tried what seems like infinite combinations of '''JAMES'',''JACK''' ETC in order to get rid of the single quotes issue, but nothing seems to work.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
user2589339
  • 91
  • 10
  • 3
    You can not use IN clause like that on variable containing comma separated values. Your best bet is to write a function which returns table containing individual names as rows and use them inside the IN condition. See this thread http://stackoverflow.com/questions/11243188/passing-a-variable-into-an-in-clause-within-a-sql-function – Harsh Sep 06 '15 at 14:52
  • 1
    Also, `:names` does not seem like sql server parameter. parameters in sql server starts with an at sign: `@names`. – Zohar Peled Sep 06 '15 at 14:54
  • Here are some correct ways of quoting: http://stackoverflow.com/a/15442322/4780877 – Emacs User Sep 06 '15 at 14:57
  • another way of explaining what @Harsh wrote - the `In` operator expects a comma separated list of values, while you supply it with a single value that contains a comma separated list. – Zohar Peled Sep 06 '15 at 14:57
  • Ah, i'm also working with oracle for other parts so sometimes i do different variations. So I have to create a temporary table/view with say column title name and give it elements 'JAMES' and 'JACK' then use the in statement for that view? – user2589339 Sep 06 '15 at 15:00
  • Have a look at the link I posted in my earlier comment to see how to convert list of CSV strings into table valued function and use it in queries such as yours. – Harsh Sep 06 '15 at 15:05
  • 1
    The only other option would be to give a variable for each name you wanted to find, if you are writing a stored procedure there would be no problem with multiple variables... – russ Sep 06 '15 at 16:20
  • I would also propose to do something like this: `first_name in (@name1, @name2, @name3)` – Stefan Steinegger Sep 07 '15 at 12:42

1 Answers1

0

It would be best to parse this through as a parameter 

Declare @name as varcher (8000)
Set @name = 'Jack,James,John,Jill'

Select *
from list
where first_name in (@names);

or 

Select *
from list
where first_name in ('Jack','James','John','Jill');
Cladback
  • 157
  • 1
  • 1
  • 9