Using STRING_SPLIT() for multiple values in a MS SQL Server query

I needed to pass in multiple values into my SQL’s WHERE portion of the query. Ideally I’d use the IN clause and pass a comma-delineated list. For example:

SELECT * FROM myTable
WHERE category IN ('value1','value2','value3')

The Problem

I had trouble dynamically building the SQL statement because each individual value needs to be in single quotes and the setting of the variable that contains all these pieces would have to also be in single quotes.

I tried “escaping” the values, but that didn’t work.

The Solution: Use STRING_SPLIT( )

First declare a variable with a comma-separated list of values. The only quotes needed is what surrounds the entire definition of the variable.

No need to surround each individual piece in quotes.

DECLARE @categories VARCHAR(MAX) = 'value1,value2,value3';

Then you can use the STRING_SPLIT() function to turn those values into a recordset/array. Once it’s in that format, you can use it in your IN clause as criteria.

The not-so-obvious part is that it still needs to be in a SELECT … FROM statement!

Here’s the final format of the SQL:

SELECT *
FROM myTable
WHERE criteria IN (
     SELECT value FROM STRING_SPLIT(@categories, ',') 
)

Note: you can only do this in SQL Server version is at least 130. You can verify the compatibility level by running this:

SELECT database_id, name, compatibility_level FROM sys.databases

Like this style of writing? 👍👍 Interested in creating a PHP web application and already know what a “variable” is and the concept of an “if” statement? Check out my book: Web Development for Intermediate Programmers — with PHP