How to Efficiently Parse Comma-Separated Strings for SQL IN Lists?

Parse comma separated string into IN list in SQL WHERE clause

Assume that the stored procedure receives a comma-separated string parameter that represents multiple values, such as username or account number. In order to filter rows based on these values, we need to construct an IN list in the WHERE clause.

The recommended approach in SQL Server 2005 and above is to use table-valued functions, for example:

<code class="language-sql">CREATE FUNCTION [dbo].[f_split](
    @param NVARCHAR(MAX),
    @delimiter CHAR(1)
)
RETURNS @t TABLE (
    val NVARCHAR(MAX),
    seq INT
)
AS
BEGIN
    SET @param += @delimiter;

    WITH a AS (
        SELECT CAST(1 AS BIGINT) AS f, CHARINDEX(@delimiter, @param) AS t, 1 AS seq
        UNION ALL
        SELECT t + 1, CHARINDEX(@delimiter, @param, t + 1), seq + 1
        FROM a
        WHERE CHARINDEX(@delimiter, @param, t + 1) > 0
    )
    INSERT @t
    SELECT SUBSTRING(@param, f, t - f), seq FROM a
    OPTION (MAXRECURSION 0);
    RETURN;
END;</code>

To use this function to parse a string into a list of INs, construct a subquery:

<code class="language-sql">SELECT *
FROM yourtable 
WHERE account IN (SELECT val FROM dbo.f_split(@account, ','))</code>

Alternatively, you can use a simple but less efficient method of pattern matching using the LIKE operator:

<code class="language-sql">WHERE account LIKE '%,' + @account + '%,</code>

In contrast, utilizing the f_split function can provide better performance, especially when dealing with large data sets.

The above is the detailed content of How to Efficiently Parse Comma-Separated Strings for SQL IN Lists?. For more information, please follow other related articles on the PHP Chinese website!