MySQL LIKE IN()?

Question

My current query looks like this:

SELECT * FROM fiberbox f WHERE f.fiberBox LIKE '%1740 %' OR f.fiberBox LIKE '%1938 %' OR f.fiberBox LIKE '%1940 %'

I did some looking around and can't find anything similar to a LIKE IN() - I envision it working like this:

SELECT * FROM fiberbox f WHERE f.fiberbox LIKE IN('%140 %', '%1938 %', '%1940 %')

Any ideas? Am I just thinking of the problem the wrong way - some obscure command I've never seen.

MySQL 5.0.77-community-log

Answer 1

A REGEXP might be more efficient, but you'd have to benchmark it to be sure, e.g.

SELECT * from fiberbox where field REGEXP '1740|1938|1940'; 

Answer 2

Paul Dixon's answer worked brilliantly for me. To add to this, here are some things I observed for those interested in using REGEXP:

To Accomplish multiple LIKE filters with Wildcards:

 SELECT * FROM fiberbox WHERE field LIKE '%1740 %'
                           OR field LIKE '%1938 %'
                           OR field LIKE '%1940 %';  

Use REGEXP Alternative:

 SELECT * FROM fiberbox WHERE field REGEXP '1740 |1938 |1940 ';

Values within REGEXP quotes and between the | (OR) operator are treated as wildcards. Typically, REGEXP will require wildcard expressions such as (.*)1740 (.*) to work as %1740 %.

If you need more control over placement of the wildcard, use some of these variants:

To Accomplish LIKE with Controlled Wildcard Placement:

SELECT * FROM fiberbox WHERE field LIKE '1740 %'
                          OR field LIKE '%1938 '
                          OR field LIKE '%1940 % test';  

Use:

SELECT * FROM fiberbox WHERE field REGEXP '^1740 |1938 $|1940 (.*) test';

• Placing ^ in front of the value indicates start of the line.

• Placing $ after the value indicates end of line.

• Placing (.*) behaves much like the % wildcard.

• The . indicates any single character, except line breaks. Placing . inside () with * (.*) adds a repeating pattern indicating any number of characters till end of line.

There are more efficient ways to narrow down specific matches, but that requires more review of Regular Expressions. NOTE: Not all regex patterns appear to work in MySQL statements. You'll need to test your patterns and see what works.

Finally, To Accomplish Multiple LIKE and NOT LIKE filters:

SELECT * FROM fiberbox WHERE field LIKE '%1740 %'
                          OR field LIKE '%1938 %'
                          OR field NOT LIKE '%1940 %'
                          OR field NOT LIKE 'test %'
                          OR field = '9999';

Use REGEXP Alternative:

SELECT * FROM fiberbox WHERE field REGEXP '1740 |1938 |^9999$'
                          OR field NOT REGEXP '1940 |^test ';

OR Mixed Alternative:

SELECT * FROM fiberbox WHERE field REGEXP '1740 |1938 '
                          OR field NOT REGEXP '1940 |^test '
                          OR field NOT LIKE 'test %'
                          OR field = '9999';

Notice I separated the NOT set in a separate WHERE filter. I experimented with using negating patterns, forward looking patterns, and so on. However, these expressions did not appear to yield the desired results. In the first example above, I use ^9999$ to indicate exact match. This allows you to add specific matches with wildcard matches in the same expression. However, you can also mix these types of statements as you can see in the second example listed.

Regarding performance, I ran some minor tests against an existing table and found no differences between my variations. However, I imagine performance could be an issue with bigger databases, larger fields, greater record counts, and more complex filters.

As always, use logic above as it makes sense.

If you want to learn more about regular expressions, I recommend www.regular-expressions.info as a good reference site.

Answer 3

Regexp way with list of values

SELECT * FROM table WHERE field regexp concat_ws("|",
"111",
"222",
"333");

Answer 4

You can create an inline view or a temporary table, fill it with you values and issue this:

SELECT  *
FROM    fiberbox f
JOIN    (
        SELECT '%1740%' AS cond
        UNION ALL
        SELECT '%1938%' AS cond
        UNION ALL
        SELECT '%1940%' AS cond
        ) с
ON      f.fiberBox LIKE cond

This, however, can return you multiple rows for a fiberbox that is something like '1740, 1938', so this query can fit you better:

SELECT  *
FROM    fiberbox f
WHERE   EXISTS
        (
        SELECT  1
        FROM    (
                SELECT '%1740%' AS cond
                UNION ALL
                SELECT '%1938%' AS cond
                UNION ALL
                SELECT '%1940%' AS cond
                ) с
        WHERE   f.fiberbox LIKE cond
        )

Answer 5

Sorry, there is no operation similar to LIKE IN in mysql.

If you want to use the LIKE operator without a join, you'll have to do it this way:

(field LIKE value OR field LIKE value OR field LIKE value)

You know, MySQL will not optimize that query, FYI.

Answer 6

Just note to anyone trying the REGEXP to use "LIKE IN" functionality.

IN allows you to do:

field IN (
'val1',
'val2',
'val3'
)

In REGEXP this won't work

REGEXP '
val1$|
val2$|
val3$
'

It has to be in one line like this:

REGEXP 'val1$|val2$|val3$'

Answer 7

This would be correct:

SELECT * FROM table WHERE field regexp concat_ws("|",(
"111",
"222",
"333"
));

Answer 8

Flip operands

'a,b,c' like '%'||field||'%'

Answer 9

Just a little tip:

I prefer to use the variant RLIKE (exactly the same command as REGEXP) as it sounds more like natural language, and is shorter; well, just 1 char.

The "R" prefix is for Reg. Exp., of course.

Answer 10

You can get desired result with help of Regular Expressions.

SELECT fiberbox from fiberbox where fiberbox REGEXP '[1740|1938|1940]';

We can test the above query please click SQL fiddle

SELECT fiberbox from fiberbox where fiberbox REGEXP '[174019381940]';

We can test the above query please click SQL fiddle

Answer 11

You can use like this too:

 SELECT
    * 
FROM
    fiberbox f
    JOIN (
    SELECT
        substring_index( substring_index( '1740,1938,1940', ',', help_topic_id + 1 ), ',',- 1 ) AS sub_ 
    FROM
        mysql.help_topic 
    WHERE
        help_topic_id <(
            length( '1740,1938,1940' )- length(
            REPLACE ( '1740,1938,1940', ',', '' ))+ 1 
        ) AS b 
        ) ON f.fiberBox LIKE concat('%',
    b.sub_,
    '%')

Answer 12

You can use like this too:

SELECT * FROM fiberbox WHERE fiber IN('140 ', '1938 ', '1940 ')