MySQL LIKE IN()?
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.
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';
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.
Read more... Read less...
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 )
Regexp way with list of values
SELECT * FROM table WHERE field regexp concat_ws("|", "111", "222", "333");
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.
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: