1
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.

  • Is the column indexed ??? – RolandoMySQLDBA Apr 11 '16 at 2:32
  • You could put the values into a table? – Vérace Apr 16 '16 at 9:26
  • No. i want to find a value by using keywords. – T.Su Apr 16 '16 at 13:30
5

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.

  • Is it possible to use REGEXP with value with space. for example, SELECT * from table WHERE column REGEXP '^(AAA aa|BBB bb|CCC)' – T.Su Apr 11 '16 at 16:44
  • @T.Su - yes, space is ok. It will match exactly one space, not table or carriage return, etc. REGEXP may be faster than a 10-way UNION; it depends on various things. – Rick James Apr 11 '16 at 18:52
  • It only came out which starts with those words. I want to select a sentence which include those words. those keywords might be anywhere of the sentences. So how can i select? – T.Su Apr 16 '16 at 13:47
  • Sure (your question implied you only wanted to check the start: 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. – Ziggy Crueltyfree Zeitgeister Apr 17 '16 at 1:38
  • SELECT * from table WHERE column REGEXP '^AAA aa|BBB bb|CCC' is it like that? – T.Su Apr 17 '16 at 22:37
1

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
  • This 10-table UNION may be faster than REGEXP; it depends on a lot of things. – Rick James Apr 11 '16 at 18:53
  • 2
    An optimization: Since there is no overlap in what the SELECTs will find (in this case), change UNION to UNION ALL. (UNION defaults to UNION DISTINCT, which needs a de-dupping pass.) – Rick James Apr 11 '16 at 18:54
  • It is OK to include prfx in every SELECT; this may make the code generation a tiny bit easier. – Rick James Apr 11 '16 at 18:55
0

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;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.