8

How do I compare list of integers with integer columns in sql server. For example I have a column statusid as int. I want to get the result where statusid in 1,4,8,9. I kept all these integers in string and operate with the column statusid as below but i am facing error there. This string may be hard coded or parameter.

where Cast(statusid as varchar) in ('1,4,8,9');

Please suggest the solution.

Rajaram Shelar
  • 7,537
  • 24
  • 66
  • 107

2 Answers2

15

since the number are converted to string, the values on IN clause should each be wrap with single quotes

where Cast(statusid as varchar(20)) in ('1','4','8','9');
John Woo
  • 258,903
  • 69
  • 498
  • 492
  • Thanks for the answer. I am taking all the integers in the string. How it can be taken here... – Rajaram Shelar Jan 22 '13 at 14:30
  • can you expand this more, *taking all the integers in the string*? – John Woo Jan 22 '13 at 14:31
  • I want to search over the collection of integer values like 1,2,5 and so on. So I keep all required numbers(1,2,5..) in a string like '1,2,5' and want to compare like `where Cast(statusid as varchar) in ('1,2,5');` – Rajaram Shelar Jan 22 '13 at 14:36
  • unfortunately, you have to do some extra work. you need to create a user define function that breaks `'1,2,5'` into pieces so the value each values can be compared to you list of ints. – John Woo Jan 22 '13 at 14:45
0
WHERE statusid 
IN (1, 4, 8, 9);
Sandip Bantawa
  • 2,822
  • 4
  • 31
  • 47
  • I think with this solution it may depend on exactly where eraj is getting his list of numbers from - seems like an incomplete question from eraj to me. – Paul Jan 22 '13 at 12:54