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 tried “escaping” the values, but that didn’t work.

The Solution: Use STRING_SPLIT( )

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