Looking for SQL code to transform the text string '12,13,14' to this list of numbers

| 12 |
| 13 |
| 14 |


  • table0.params (varchar) field contains this text: 12,13,14
  • table1 has column ID (numeric).

I would like to form a query along the lines of:

SELECT * FROM table1 WHERE ID in table0.params

I am using Microsoft SQL Server Standard Edition (64-bit) version 10.50.1600.1

  • The correct solution is to properly normalize your data model. Do not store comma delimited values in a single column.
    – user1822
    Commented Sep 11, 2017 at 14:48
  • Microsoft SQL Server Standard Edition (64-bit) version 10.50.1600.1 Commented Sep 11, 2017 at 14:49
  • Also: dba.stackexchange.com/q/21078/1822
    – user1822
    Commented Sep 11, 2017 at 14:51
  • And stackoverflow.com/a/9811267/470530
    – Vérace
    Commented Sep 11, 2017 at 14:52

3 Answers 3


While this question, for the most part, has been asked and answered what at least feels like hundreds of times on here and StackOverflow, because it concerns int values only I will say that:

Given that you are dealing with only INT values (as the values contained in the delimited string), the fastest way I have come across is to use a splitter that is a) SQLCLR, and b) specialized to only return integers. Since we know what the output type should, in the end be, there is no need to waste the extra cycles on passing back a string (slower than passing back an INT) only to convert that string into an INT. So, for this use case, I created a function that does just this: splits a string and passes back a table of INT values. My testing (so far) shows it to be quite a bit faster than the standard splitter that passes back a table of strings (for the same input values, of course). You can find a pre-done SQLCLR function of this in the SQL# library (that I created). The function is SplitInts. There is also another version of it named SplitInts4k that should be used when the input string is guaranteed to be no more than 4000 characters. The difference is that the non-4k version uses NVARCHAR(MAX) as the input parameter datatype, and that is noticeably slower than NVARCHAR(4000).

You could use it as follows, though it is unclear, if table0 really is a Table and not an input parameter, how it relates to table1 if table0 has more than 1 row:

FROM table1 t1
         SELECT [SplitVal]
         FROM SQL#.String_SplitInts4k(table0.params, N',', 1, 0)

Or perhaps:

FROM table1 t1
INNER JOIN SQL#.String_SplitInts4k(@table0.params, N',', 1, 0) ints
        ON ints.[SplitVal] = t1.[ID];

Because I've not seen either of the following approaches listed yet, I figured I'd include them.

If you're running SQL 2016 or later, take a look at the STRING_SPLIT function.

If you're running SQL 2014 or earlier, you can utilize some XML trickery to split up the string into a table.

Both approaches are as follows:

DECLARE @myString NVARCHAR(400) = '12,13,14'

-- For SQL 2016 and later
SELECT CAST(value AS INT) AS value
FROM STRING_SPLIT(@myString, ',');

-- For SQL 2014 and earlier - Split String via XML
-- Split String via XML
SELECT y.val as value
    SELECT CAST(('<X>' + REPLACE(@myString, ',', '</X><X>') + '</X>') AS XML) AS xmlRow
    SELECT t.c.value('.', 'int') AS val
    FROM x.xmlRow.nodes('X') AS t(c)
) y

DBFiddle can be found here


If you're not against using dynamic SQL, the following example should serve your purpose. @Inlist is my substitute for your table0.params (I commented out the actual EXEC command.)


SET @InList = '12,13,14'


SET @sql = 'SELECT * ' + 'FROM MyTable ' + 'WHERE Id IN (' + @InList + ') '

PRINT @sql
--EXEC sp_executesql @sql

Not the answer you're looking for? Browse other questions tagged or ask your own question.