MySQL LIKE IN()?

Problem

My current question is as follows:

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

I looked around and couldn’t find anything that works like a LIKE IN() – I imagine it functioning like this:

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

Do you have any suggestions? Is it possible that I’m misinterpreting the situation – perhaps there’s some arcane command I’ve never seen?

MySQL 5.0.77-community-log

Asked by Michael Wales

Solution #1

A REGEXP might be more efficient, but you’d have to test it, for example.

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

Answered by Paul Dixon

Solution #2

P:

To use Wildcards to create multiple LIKE filters, follow these steps:

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

Wildcards are treated as values within REGEXP quotes and between the | (OR) operator. To function as percent 1740 percent, REGEXP usually requires wildcard phrases like (.*)1740 (.*).

Use some of these alternatives if you require greater flexibility over where the wildcard is placed:

To complete LIKE with Controlled Wildcard Placement, follow these steps:

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

There are faster ways to find precise matches, but they necessitate a deeper understanding of Regular Expressions. NOTE: It appears that not all regex patterns operate in MySQL statements. You’ll have to put your designs to the test to determine what works.

Finally, to implement many LIKE and NOT LIKE filters, follow these steps:

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 created a second WHERE filter for the NOT set. I tried employing negating patterns, forward-looking patterns, and other techniques. These formulations, however, did not appear to provide the anticipated results. I used 9999$ in the first example to signify an 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.

In terms of performance, I did some quick tests against an existing table and discovered no significant changes between my modifications. More databases, larger fields, higher record counts, and more complex filters, on the other hand, may cause performance issues.

As is customary, apply logic above since it makes sense.

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

Answered by David Carroll

Solution #3

Using regexp with a list of values

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

Answered by user136379

Solution #4

You can do this by creating an inline view or a temporary table, filling it with your values, and issuing the following command:

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, on the other hand, can return many rows for a fiberbox with a value of ‘1740, 1938,’ thus this query may be more appropriate:

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
        )

Answered by Quassnoi

Solution #5

In mysql, there is no operation that is analogous to LIKE IN.

You’ll have to do it this way if you wish to utilize the LIKE operator without a join:

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

That query will not be optimized by MySQL, FYI.

Answered by gahooa

Post is based on https://stackoverflow.com/questions/1127088/mysql-like-in