Build a where clause from comma separated list of values-CodePudding

I have a stored procedure that accepts a comma separated list of values as a parameter

SELECT * 
FROM users 
WHERE username IN (SELECT value FROM STRING_SPLIT( @userList, ','))

I want to change the where clause to use a like for each value

SELECT * 
FROM users 
WHERE username LIKE 'value1%' 
   OR username LIKE 'value2%' ...

I'm not sure how to go about this. Any ideas?

CodePudding user response：

You don't need to build a gigantic list of OR conditions, just join on LIKE:

SELECT u.username, f.value
FROM dbo.users AS u
  INNER JOIN SPLIT_STRING(@userList, ',') AS f
  ON u.username LIKE f.value   '%';

CodePudding user response：

It is simpler to use EXISTS:

WITH cte AS (SELECT value FROM STRING_SPLIT( @userList, ','))
SELECT u.* 
FROM users u 
WHERE EXISTS (SELECT 1 FROM cte c WHERE u.username LIKE c.value   '%');

CodePudding user response：

The real answer here is to not have a comma-separated list at all. Keep your data normalized and use a Table-Valued Parameter instead.

First create a Table type

Note the use of primary key, which gets you a free index. In this particular instance I don't think it will be used, because of the LIKE, but it would be useful if you were doing =

CREATE TYPE dbo.NameList TABLE (value varchar(100) PRIMARY KEY);

Then use it in your procedure

CREATE OR ALTER PROC dbo.GetUserList
  @userList dbo.NameList
AS

SELECT u.*
FROM users u
WHERE EXISTS (SELECT 1
    FROM @userList ul
    WHERE u.username LIKE ul.value   '%'
);

You use it like this

DECLARE @list dbo.NameList;
INSERT @list (value) VALUES ('value1'),('value2');

EXEC dbo.GetUserList @list;

And most client libraries can pass these parameters also.