SQL Multiple Like Statements

Combining LIKE and IN for SQL Server

Effectively, the IN statement creates a series of OR statements... so

SELECT * FROM table WHERE column IN (1, 2, 3)

Is effectively

SELECT * FROM table WHERE column = 1 OR column = 2 OR column = 3

And sadly, that is the route you'll have to take with your LIKE statements

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

SQL Multiple LIKE Statements

WITH CTE AS
(
SELECT VALUE
FROM (
        VALUES ('B79'), ('BB1'), ('BB10'), ('BB11'), ('BB12'), ('BB18'), ('BB2'), ('BB3'), ('BB4'), ('BB5'), ('BB6'), ('BB8'), ('BB9'), ('BB94'), ('BD1'), ('BD10'), ('BD11'), ('BD12'), ('BD13'), ('BD14'),
                ('BD15'), ('BD16'), ('BD17'), ('BD18'), ('BD19'), ('BD2'), ('BD20'), ('BD21'), ('BD22'), ('BD3'), ('BD4'), ('BD5'), ('BD6')
     ) V(VALUE)
)   
SELECT * 
FROM tbl_ClientFile T
WHERE EXISTS ( SELECT TOP 1 1 FROM CTE WHERE T.CLNTPOST1 LIKE CTE.VALUE + '%')

Multiple LIKE statements in SQL

Yes, you can do the following:

SELECT * FROM stores WHERE store_name LIKE '%$user_input%' 
AND store_name LIKE '%Walmart%'
ORDER BY store_name ASC

select query using multiple like conditions

Your query contains a mix of upper and lower string comparison targets. As far as I'm aware, SQL Server is not by default case-sensitive; is it possible this is what is tripping your query up? Check collation per this answer.

EDIT: Based on your updated question, can you not just use an AND clause that uses a NOT on the front?
In other words, add a 'AND not (x)' clause, where 'x' is the conditions that define the records you want to exclude? You'd need to nest the customer test, because it's an OR.
e.g.:

... payhistory.comment NOT LIKE 'Elit%'
AND not ((customer like 'FTS%' or customer like 'EMC%') AND batchtype = 'PC')

As a side note, I believe that a LIKE clause may imply an inefficient table scan in some (but not all) cases, so if this query will be used in a performance-sensitive role you may want to check the query plan, and optimise the table to suit.

Alternative solution for multiple LIKE

For these patterns you can use:

SELECT * 
FROM table 
WHERE column LIKE '4010[125]%'

SQL Server modestly extends LIKE patterns to include character matching.

How to optimize a query having multiple like conditions

I assume that search values comes dynamically and no way to predict of what going to be selected. Then, you cannot do a lot with constructions like:

WHERE df.filename like '%OA73030%'  or df.filename like '%OA73035%'
or df.FileName like '%OA77030%' or df.filename like '%OA77035%'

Since there is no way to rewrite it to:

WHERE df.filename like IN ('%OA73030%','%OA73035%','%OA77030%','%OA77035%')

However, you entire logic of your query can be very much optimized this way:

SELECT COUNT(*) AS totalProcessed
,       COUNT(CASE WHEN    di.CompletionDetail LIKE '%Not found%' OR di.CompletionDetail LIKE '%error%'  THEN 1 END) AS totalErrored
,       COUNT(CASE WHEN    di.CompletionDetail NOT LIKE '%error%' AND di.CompletionDetail NOT LIKE '%Not Found%'  THEN 1 END) AS totalErrored
,       COUNT(CASE WHEN    di.CompletionDetail NOT LIKE '%error%' AND di.CompletionDetail NOT LIKE '%Not Found%'  THEN 1 END) AS totalSuccess
FROM   DetailItem di
       INNER JOIN DownloadFile df ON di.DownloadFileID = df.DownLoadID
WHERE  di.CompletionTime between '2018-01-31' AND '2018-01-31 23:59:59'
       AND di.DetailItemName <> '99999999'
       AND df.CompanyID = 164
       AND (   df.filename LIKE '%OA73030%'
               OR df.filename LIKE '%OA73035%'
               OR df.FileName LIKE '%OA77030%'
               OR df.filename LIKE '%OA77035%' );

In this case the data engine have to search data only once for all aggregates instead of four times.

Next to that, your approach of trimming time is not SARGable:

( CONVERT(DATE, di.CompletionTime)) = '2018-01-31'

and it has to be rewritten to:

di.CompletionTime between '2018-01-31' AND '2018-01-31 23:59:59'

Otherwise possible index on CompletionTime will not be used

SQL Query Dynamically Create Multiple LIKE/OR Clause

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);

MySQL Like multiple values

The (a,b,c) list only works with in. For like, you have to use or:

WHERE interests LIKE '%sports%' OR interests LIKE '%pub%'

---

Related Topics