Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.
Sign up to join this community
SELECT *
from table
WHERE column LIKE "AAA%"
OR column LIKE "BBB%"
OR column LIKE "CCC%"
I have more than 10 values to select, so it is not appropriate to write like the above example. Is there any way to select possible values in one sentence? I am using MySQL.
This case is a good candidate for using REGEXP instead of LIKE.
SELECT *
from table
WHERE column REGEXP '^(AAA|BBB|CCC)'
REGEXP is case insensitive. For case sensitive searches use REGEXP BINARY.
SELECT *
from table
WHERE column REGEXP BINARY '^(AAA|BBB|CCC)'
REGEXP can't benefit from indexes, so it may only be an efficient option if LIKE wouldn't use indexes either. With multiple LIKE statements that could individually access an index (like in your case if column is indexed and the like has a wildcard at the end), performing UNION of individual queries with a single LIKE would probably be most effective in a large dataset.
However, if your query filter primarily by, say, a range of dates, then a single REGEXP may be faster than multiple LIKEs ORed.
For more information about REGEXP in MySQL see this and this.
REGEXP may be faster than a 10-way UNION; it depends on various things.
Apr 11, 2016 at 18:52
LIKE 'AAA%'). Just remove the caret symbol, which is an anchor to the start of the line (^) and the test will be over the whole string. Note that it won't make use of indexes when you search like that.
Apr 17, 2016 at 1:38
Here is a method using Dynamic SQL
SET group_concat_max_len = 1048576;
SELECT GROUP_CONCAT(CONCAT('SELECT * FROM table WHERE column LIKE ''',prfx,'%''')
SEPARATOR ' UNION ')
INTO @sql FROM (SELECT 'AAA' prfx UNION SELECT 'BBB' UNION SELECT 'CCC') pfxkeys;
PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;
So that you understand what this is doing, please note what @sql becomes:
mysql> SELECT @sql\G
*************************** 1. row ***************************
@sql: SELECT * FROM table WHERE column LIKE 'AAA%' UNION SELECT * FROM table WHERE column LIKE 'BBB%' UNION SELECT * FROM table WHERE column LIKE 'CCC%'
Let me format the output a little more
SELECT * FROM table WHERE column LIKE 'AAA%' UNION
SELECT * FROM table WHERE column LIKE 'BBB%' UNION
SELECT * FROM table WHERE column LIKE 'CCC%'
The only thing you need to focus on in scripting the pfxkeys subquery.
In your example, you need
(
SELECT 'AAA' prfx UNION
SELECT 'BBB' UNION
SELECT 'CCC'
) pfxkeys
If you have more prefix keys like DDD and EEE, you just add to this subquery:
(
SELECT 'AAA' prfx UNION
SELECT 'BBB' UNION
SELECT 'CCC' UNION
SELECT 'DDD' UNION
SELECT 'EEE'
) pfxkeys
UNION may be faster than REGEXP; it depends on a lot of things.
Apr 11, 2016 at 18:53
SELECTs will find (in this case), change UNION to UNION ALL. (UNION defaults to UNION DISTINCT, which needs a de-dupping pass.)
Apr 11, 2016 at 18:54
prfx in every SELECT; this may make the code generation a tiny bit easier.
Apr 11, 2016 at 18:55
The comparison values can be held in a separate table and joined to the data table. This assumes the comparision clauses do not overlap, as you sample shows. In other words, each data row will match at most one filter condition.
This is the data:
create table data (c1 char(10));
insert data(c1) values ('aa1'),('aa2'),('bb1'),('bb2'),('cc1'),('cc2');
An these are the 10+ comparison values
create table comparison (c2 varchar(5));
insert comparison (c2) values ('aa%'), ('bb%');
So the query becomes:
select *
from data as d
inner join comparison as l
on d.c1 like l.c2;
