I have a column in database that stores an array of numbers in a nvarchar string and the values look like this
"1,5,67,122"
Now, I'd like to use that column value in a query that utilizes IN statement
However, if I have something like
WHERE columnID IN (@varsArray);
it doesn't work as it sees @varsArray as a string and can't compare it to columnID which is of INT type.
how can I convert that variable to something that could be used with a IN statement?
You need dynamic SQL for that.
exec('select * from your_table where columnID in (' + @varsArray + ')')
alternatively you can parse your variable with finding a split user defined function on the internet and enter each number into a temp table and then join the temp table
but the person who answered above is correct this is bad database design
create a table so you can join it based on some id, and all the answers will be in a table (temporary or not)
One other option is to use a User Defined Table-Valued function that takes a delimited string as input and generates a table with one row per value as its output (see This SO question), and then, using Cross Apply, join the table itself to the output of that UDF.
These techniques are useful to have in your trick-bag anyway.
Unfortunately we're forced to work against bad data designs sometimes...
I've seen this done by building and calling a SQL function that splits out the numbers and returns them in a table. Then you could run your columnID against that temp table to see if it exists.
