How to Efficiently Convert a Comma-Separated String to an SQL IN List?

Convert comma separated string to IN list in SQL WHERE clause

Question:

When you store a comma separated string as a parameter in a stored procedure, the task is to convert it into the correct IN list in the WHERE clause.

Best Practice Solutions:

To perform this conversion efficiently, create a custom SQL Server function:

<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) f, charindex(@delimiter, @param) t, 1 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>

Usage:

Fill the WHERE clause with the following statement:

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

Performance comparison:

This function provides higher performance compared to XML-based segmentation methods. Use a large test dataset:

• XML splitting time: 1 minute and 21 seconds
• f_split function takes 43 seconds

Performance differences may vary, highlighting the efficiency of custom split functions.

The above is the detailed content of How to Efficiently Convert a Comma-Separated String to an SQL IN List?. For more information, please follow other related articles on the PHP Chinese website!