SQL - Convert text to a list of numeric values Looking for SQL code to transform the text string '12,13,14' to this list of numbers 

+----+
| 12 |
+----+
| 13 |
+----+
| 14 |
+----+


Example:


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
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:

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


Or perhaps:

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