Advertisement
Advertisement


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

2009/07/14
1
282
7/14/2009 6:21:41 PM

Accepted Answer

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'; 
2009/07/14
468
7/14/2009 6:23:44 PM


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
        )
2009/07/14

Regexp way with list of values

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

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.

2009/07/14

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$'
2017/07/21

Flip operands

'a,b,c' like '%'||field||'%'
2012/12/06

Source: https://stackoverflow.com/questions/1127088
Licensed under: CC-BY-SA with attribution
Not affiliated with: Stack Overflow
Email: [email protected]