SQL - Combining Multiple Like Queries

SQL - Combining multiple like queries

You can use SIMILAR TO and separate the tags with | pipe '555123%|555321%|555987%'

eg:

SELECT * 
FROM phonenumbers 
WHERE number SIMILAR TO '555123%|555321%|555987%'

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%'

How to use SQL LIKE condition with multiple values in PostgreSQL?

Perhaps using SIMILAR TO would work ?

SELECT * from table WHERE column SIMILAR TO '(AAA|BBB|CCC)%';

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 + '%')

How can I introduce multiple conditions in LIKE operator?

Here is an alternative way:

select * from tbl where col like 'ABC%'
union
select * from tbl where col like 'XYZ%'
union
select * from tbl where col like 'PQR%';

Here is the test code to verify:

create table tbl (col varchar(255));
insert into tbl (col) values ('ABCDEFG'), ('HIJKLMNO'), ('PQRSTUVW'), ('XYZ');
select * from tbl where col like 'ABC%'
union
select * from tbl where col like 'XYZ%'
union
select * from tbl where col like 'PQR%';
+----------+
| col      |
+----------+
| ABCDEFG  |
| XYZ      |
| PQRSTUVW |
+----------+
3 rows in set (0.00 sec)

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

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.

Combine multiple OR and LIKE mySql

You can use the function FIND_IN_SET(). MySQL has a dedicated function FIND_IN_SET() that returns field index if the value is found in a string containing comma-separated values.

Example

DROP TABLE table_name;

CREATE TABLE table_name
(col1 VARCHAR(100));

INSERT INTO table_name VALUES ('38');
INSERT INTO table_name VALUES ('37,38,39');
INSERT INTO table_name VALUES ('37,38');
INSERT INTO table_name VALUES ('38,39');
INSERT INTO table_name VALUES ('48');

SELECT col1
FROM table_name
WHERE FIND_IN_SET(38, col1) > 0;

Your solution would be

SELECT col1, col2, col3
FROM table_name
WHERE FIND_IN_SET(38, col1) > 0 
  OR FIND_IN_SET(38, col2) > 0 
  OR FIND_IN_SET(38, col3) > 0 ;

---

Related Topics