-1

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 + '%' + ''''
rick_87394
  • 1
  • 3
  • 2
    If you didn't use a comma separated list in a string but a proper table variable, you could easily use a join. – sticky bit Jan 21 '22 at 10:32
  • It can be a Run time-compiled Transact-SQL statement, in these cases, [sp_executesql](https://learn.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-executesql-transact-sql?view=sql-server-ver15) is one way to do it. Just be aware that these statements can expose applications to malicious attacks. – abestrad Jan 21 '22 at 10:35
  • The comma-separated string is coming as a multiple-filter selection form control on a web form. So basically user makes multiple selections on the form, I catch them as comma-separated-string and want to return the result by applying LIKE clause to all these values – rick_87394 Jan 21 '22 at 10:36
  • 3
    Most languages support table type parameters, @rick_87394 . I suspect you could change your application to use one instead. – Thom A Jan 21 '22 at 10:37
  • @Larnu, should I try to use table type parameters or try your answer? By the way, I am running the stored procedures from a .NET application with Dapper – rick_87394 Jan 21 '22 at 10:41
  • 2
    The solutions with a table type parameter would likely be identical, @rick_87394 , apart from you would use your table type parameter instead of `STRING_SPLIT`. The advantage, however, is that you don't need to rely on the function, and won't have to *assume* your values don't have a delimiter in their actual value. – Thom A Jan 21 '22 at 10:49

1 Answers1

2

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);
Thom A
  • 88,727
  • 11
  • 45
  • 75
  • This works when there is a value in the `@YourVariable`, but when `@YourVariable` is '' (NULL), it doesn't return any values. This is one of the filters in the `WHERE` clause similar to many other filter conditions and is being used with checking `NULL` values all the time like: `@YourVariable IS NULL OR (YT.YourColumn IN (select value from string_split(@YourVariable,',')))` which will return exact matches, but not similar ones with `LIKE` – rick_87394 Jan 21 '22 at 11:09
  • 1
    @rick_87394 handling the parameter being `NULL` isn't even touched in your question... If that was a requirement, if should have been in your question. Though to handle it, you just need an `OR`. (Though I would recommend also then adding `RECOMPILE` to your `OPTION` clause.) – Thom A Jan 21 '22 at 11:10
  • Should I use `OR` in the `JOIN` ? – rick_87394 Jan 21 '22 at 11:14
  • I would suggest you use the `EXISTS` solution if you need to handle `NULL` parameters, @rick_87394 . – Thom A Jan 21 '22 at 11:16
  • Thank you, but applying your suggestion will be a completely different solution to use `EXISTS` with `LIKE` and maybe require a different question cause I also couldn't figure out how to do that – rick_87394 Jan 21 '22 at 11:32
  • I've updated the answer to meet your omitted requirement, @rick_87394 , however, if that doesn't answer then you'll need to ask a [new question](https://stackoverflow.com/questions/ask) and ensure you explicitly explain what the problem you are *really* trying to solve is and not omit requirements. – Thom A Jan 21 '22 at 11:34
  • Ok sorry for the omitted requirement, I have now added it to the question and the answer is working as expected, thank you – rick_87394 Jan 21 '22 at 11:46