CODEWITHSUNDEEP
sqlt-sql
Victor
Victor

Reputation: 1271

Using a String Array in an IN statement

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?

Upvotes: 0

Views: 105

Answers (4)

tclark333
tclark333

Reputation: 567

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.

Upvotes: 0

Charles Bretana
Charles Bretana

Reputation: 146499

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.

Upvotes: 0

MichaelEvanchik
MichaelEvanchik

Reputation: 1766

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)

Upvotes: 0

juergen d
juergen d

Reputation: 204784

You need dynamic SQL for that.

exec('select * from your_table where columnID in (' + @varsArray + ')')

And BTW it is bad DB design to store more than 1 value in a column!

Upvotes: 3

Related Questions