Looking for SQL code to transform the text string '12,13,14' to this list of numbers
+----+
| 12 |
+----+
| 13 |
+----+
| 14 |
+----+
Example:
12,13,14I 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];
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
FROM
(
SELECT CAST(('<X>' + REPLACE(@myString, ',', '</X><X>') + '</X>') AS XML) AS xmlRow
) x CROSS APPLY (
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.)
DECLARE @InList VARCHAR(100)
SET @InList = '12,13,14'
DECLARE @sql NVARCHAR(1000)
SET @sql = 'SELECT * ' + 'FROM MyTable ' + 'WHERE Id IN (' + @InList + ') '
PRINT @sql
--EXEC sp_executesql @sql
