SQL IN Statement splitting parameter

Question

SQL Server

I have a parameter that contains a comma delimited string:

'abc,def,ghi'

I want to use that string in a IN statement that would take my parameter like this:

select * from tableA where val IN ('abc','def','ghi')

Any ideas on how I would do this?

score 1 · Accepted Answer · answered Dec 04 '15 at 23:13

1

If using dynamic SQL is an option, this can be executed:

SELECT 'SELECT * FROM tableA WHERE val IN (' +
       '''' + REPLACE('abc,def,ghi', ',', ''',''') + ''')'

Basically, the REPLACE() function separates each item by ',' instead of just ,.

answered Dec 04 '15 at 23:13

Daniel Neel

1,197
13
28

Is there a way to write this without it being dynamic? – webdad3 Dec 05 '15 at 01:56
@webdad3 Not that I know of - anyone else know of an option? – Daniel Neel Dec 05 '15 at 03:06
1

@webdad3 my solution works without creating dynamic SQL – Rabbit Dec 08 '15 at 19:48

score 1 · Answer 2 · answered Dec 04 '15 at 23:25

1

The simplest way would be to do something like this:

SELECT *
FROM TableName
WHERE ',' + commaDelimitedString + ',' LIKE '%,' + FieldName + ',%'

But be careful about SQL injection. You might want to parameterize it.

answered Dec 04 '15 at 23:25

Rabbit

507
3
9

score 0 · Answer 3 · edited May 23 '17 at 12:07

You can use this SQL to 'pivot' a comma-separated string into a table;

DECLARE @badData TABLE (id INT NOT NULL, txt NVARCHAR(max));

INSERT INTO @badData
VALUES (1, 'foo,bar,baz'), (2, NULL);;

-- the idea is to recursively 'pop' a value from the start of the string, splitting it into 'head' and 'tail' components
WITH unpacked (id, head, tail)
AS (
  SELECT id, LEFT(txt, CHARINDEX(',', txt + ',') - 1), STUFF(txt, 1, CHARINDEX(',', txt + ','), '')
  FROM @badData

  UNION ALL

  SELECT id, LEFT(tail, CHARINDEX(',', TAIL + ',') - 1), STUFF(tail, 1, CHARINDEX(',', tail+ ','), '')
  FROM unpacked
  WHERE tail > ''
  )
SELECT id, head
FROM unpacked
ORDER BY id

You could stick this result into a common table expression, then write a where clause like

select * from tableA where val IN (select head from unpacked)

heavily plagiarised from https://stackoverflow.com/a/5493616/6722

score -3 · Answer 4 · answered Dec 04 '15 at 23:12

-3

Many programming languages have a split() function, for example in Ruby

'123,456,789'.split "," => ["123", "456", "789"]

answered Dec 04 '15 at 23:12

lee

417
3
13

This answer is not relevant to the case here. – M22an Dec 05 '15 at 02:48

SQL IN Statement splitting parameter

4 Answers4

Linked