Convert String to list in SQL

Question

I have a query like:

SELECT col1,col2 from table1 where col1 in (:var);

and this :var has a value like "'1234', '5678'" which is a string consisting of single quotes and commas in it. I want to convert this string to a type which can be given as input to the SQL 'in' operator, something like this:

SELECT col1, col2 from table1 where col1 in (STRING_SPLIT(:var));

You don't; that isn't how `STRING_SPLIT` works. You need to `JOIN` to the split value or use `IN` against a subquery (that uses `STRING_SPLIT`). Alternatively, switch to a table type parameter, and then you don't need to use `STRING_SPLIT` at all. — Thom A, Feb 17 '23 at 11:01
`DECLARE @var AS NVARCHAR(100) = '''1234'', ''5678'''; SELECT col1, col2 FROM table1 WHERE col1 IN (SELECT LTRIM(RTRIM(value)) FROM STRING_SPLIT(@var, ','))` — NIKUNJ PATEL, Feb 17 '23 at 11:02
How does this ref helps https://stackoverflow.com/questions/10914576/t-sql-split-string — Igor Micev, Feb 17 '23 at 11:03
@NIKUNJPATEL Thanks a ton, you saved me !!!!. Please add your solution as an answer. — Sufi, Feb 17 '23 at 11:13

score 1 · Accepted Answer · answered Feb 17 '23 at 11:15

1

This is the code as solution to achieve desired result in SQL server query.

DECLARE @var AS NVARCHAR(100) = '''1234'', ''5678'''; 
SELECT col1, col2  FROM table1  WHERE col1 IN (SELECT LTRIM(RTRIM(value)) FROM STRING_SPLIT(@var, ','))

answered Feb 17 '23 at 11:15

NIKUNJ PATEL

2,034
1
7
22

score 0 · Answer 2 · answered Feb 17 '23 at 11:08

You can't use STRING_SPLIT to expand a delimited literal string into multiple delimited literal strings. STRING_SPLIT('abc,def',',') doesn't result in 'abc','def', it results in a data set of 2 rows, containing the values 'abc' and 'def'.

If you want to pass a delimited string, you need to either JOIN/CROSS APPLY to STRING_SPLIT or use a subquery:

SELECT T1.Col1,
       T1.Col2
FROM dbo.table1 T1
     JOIN STRING_SPLIT(@YourVariable,',') SS ON T1.Col1 = SS.Value;

SELECT T1.Col1,
       T1.Col2
FROM dbo.table1 T1
WHERE T1.Col1 IN (SELECT SS.Value
                  FROM STRING_SPLIT(@YourVariable,',') SS);

You may, however, find even better performance with an indexed temporary table, if you are dealing with large data sets:

CREATE TABLE #temp (Value varchar(30) PRIMARY KEY); --Use an appropriate data type. I assume unique values in the delimited string

INSERT INTO #temp (Value)
SELECT SS.Value
FROM STRING_SPLIT(@YourVariable,',') SS;

SELECT T1.Col1,
       T1.Col2
FROM dbo.table1 T1
     JOIN #Temp T ON T1.Col1 = T.Value;

Finally, which may be better again, you could use a table type parameter. Then you would, like the above, just JOIN to that or use an EXISTS.

Convert String to list in SQL

2 Answers2