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

User · Answer

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

User · Answer

This would be correct   SELECT   FROM table WHERE field regexp concat ws        111    222    333

User · Answer

Flip operands    a b c  like      field

User · Answer

You can use like this too  SELECT   FROM fiberbox WHERE fiber IN  140     1938     1940

User · Answer

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

User · Answer

Regexp way with list of values  SELECT   FROM table WHERE field regexp concat ws       111    222    333

User · Answer

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

User · Answer

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

User · Answer

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

User · 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

User · Answer

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

[sql] MySQL LIKE IN()?

Examples related to sql

Examples related to mysql