Menu
I want to write an SQL statement like below:
select * from tbl where col like ('ABC%','XYZ%','PQR%'); I know it can be done using OR. But I want to know is there any better solution.
682
select * from employee where name like '%aa%' or name like '%bb% or name like '%cc%'..... Next row NAME Column Contains aa bb cc dd..... each value in the NAME column and put in the multiple like operator using OR condition.
Using the LIKE operator, you can specify single or multiple conditions. This allows you to perform an action such as select, delete, and updating any columns or records that match the specified conditions. It is mainly paired with a where clause to set the conditions.
The SQL LIKE clause is used to compare a value to similar values using wildcard operators. There are two wildcards used in conjunction with the LIKE operator. The percent sign represents zero, one or multiple characters. The underscore represents a single number or character.
Not only LIKE, but you can also use multiple NOT LIKE conditions in SQL. You will get records of matching/non-matching characters with the LIKE – this you can achieve by percentile (%) wildcard character. Below use cases, help you know how to use single and multiple like conditions.
This is a good use of a temporary table.
CREATE TEMPORARY TABLE patterns ( pattern VARCHAR(20) ); INSERT INTO patterns VALUES ('ABC%'), ('XYZ%'), ('PQR%'); SELECT t.* FROM tbl t JOIN patterns p ON (t.col LIKE p.pattern); In the example patterns, there's no way col could match more than one pattern, so you can be sure you'll see each row of tbl at most once in the result. But if your patterns are such that col could match more than one, you should use the DISTINCT query modifier.
SELECT DISTINCT t.* FROM tbl t JOIN patterns p ON (t.col LIKE p.pattern); If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
