Are there any updates in SQL 2008 to allow a variable for the IN of a where clause?
Declare @InParams varchar(100)
Set @InParams = '1,2'
Select * from Category
Where CategoryID in @InParams
Well it is easy to do that in Postgres:
Select * from Category
Where CategoryID = Any(string_to_array('1,2', ',')::int[])
It's possible to replace """string_to_array('1,2', ',')::int[]"""
ReplyDeletewith something that doesn't need to explode and then typecast the string? like: """[1, 2, 3]""" ?
Hi Arthur, thanks for the observation. However, the proof of concept I'm showing is even the source input come from external, let's say you don't have any control and you cannot make the caller(other developer) modify their code and pass an array instead. The combo of string_to_array and typecasting will let you achieve what you want to do without too much fuss
ReplyDeleteAnother approach is:
ReplyDeletecreate table example as
select '1,2,3'::text as s;
select ('{' || s || '}')::int[] from example