SQL Query Dynamically Create Multiple LIKE/OR Clause-CodePudding

I am trying to create the answer

SELECT * 
FROM table
WHERE column LIKE 'Text%' 
   OR column LIKE 'Hello%' 
   OR column LIKE 'That%'

in below link:

Combining "LIKE" and "IN" for SQL Server

The problem is, in my example the values in the answer 'Text', 'Hello' and 'That' are not hard coded, they are populated from an application multi-select control and can be NULL value or a comma-separated string like this :

DECLARE @String_With_Commas nvarchar(255);
SET @String_With_Commas = N'Mercedes,BMW,Audi,Tesla,Land Rover';

I have tried below code, but it didn't work :

DECLARE @SearchString = CONCAT('''',REPLACE(@String_With_Commas, N',', N'%'' OR column LIKE '''));

And use it like :

WHERE column LIKE @SearchString   '%'   ''''

CodePudding user response：

Assuming you are using a fully supported version of SQL Server, a couple ideas:

JOIN to STRING_SPLIT:

SELECT *
FROM dbo.YourTable YT
     JOIN STRING_SPLIT(@YourVariable,',') SS ON YT.YourColumn LIKE SS.[value]   '%';

This will, however, return multiple rows if there can be multiple matches.

Use an EXISTS:

SELECT *
FROM dbo.YourTable YT
WHERE EXISTS (SELECT 1
              FROM STRING_SPLIT(@YourVariable,',') SS
              WHERE YT.YourColumn LIKE SS.[value]   '%');

This won't return the same row twice, if there are multiple matches.

From the comments on this answer, the requirement that the parameter be NULLable was omitted in the question. I would therefore suggest you use the EXISTS solution:

SELECT *
FROM dbo.YourTable YT
WHERE EXISTS (SELECT 1
              FROM STRING_SPLIT(@YourVariable,',') SS
              WHERE YT.YourColumn LIKE SS.[value]   '%')
   OR @YourVariable IS NULL
OPTION (RECOMPILE);