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

Wheelbarrow

WHERE FIND_IN_SET(f.fiberbox, "1740,1938,1940") – Donalt

FIND_IN_SET does not accept wildcards like % – Tuscarora

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

Thief

I like this answer - quick, simple, got all of the "options" in one line like I wanted (easy to edit). On the small result set I am targeting, no decrease in performance at all. – Wheelbarrow

Over 1 million rows in my table. REGEX arround 0.0009 and LIKE arround 0.0005. If more then 5 REGEX, arround 0.0012... – Mcnary

I had an issue where REGEXP was prohibitively slow, but I needed the flexibility of REGEXP to narrow my result set further than LIKE could provide. I came up with a hybrid solution where I used both LIKE and REGEXP; despite the REGEXP portion being sufficient to give me the correct results, using LIKE as well allowed MySQL to reduce the result set considerably before having to use the slower REGEXP criteria. – Congratulant

To get the regexp value from a column: (select group_concat(myColumn separator '|') from..) – Bunker

i thought (Regexp) it's another part of the mysql extension,... but the fact it isn't. Because it is a part of the main program. very good then :D – Lawgiver

Adding to the performance data. On MySql 5.5 in a table with 229M rows, a 1 term left anchored 3 char search: REGEXP: 16s, LIKE: 8.5s; 2 terms: REGEXP: 22.1s, LIKE: 9.69; '^(hemoglobin|hematr?ocrit).*' vs 3 term like: REGEXP: 36.3, LIKE: 9.59. – Chickenlivered

@JesseClark - Can you possibly do a test for something like REGEXP '1024|9999|8888|7777' vs Like '%1024%', etc. To my understanding like '1024%' can be indexed while LIKE '%1024%' cannot be and REGEXP can't ever be, so comparing the performance output for the 2 will be a very strong hint for me how to deploy my current application. Thank you very much. – Gantt

Will . . . where field REGEXP '1740' treat as . . . where field like '%1740%'? Or should I add (.*) surround the REGEXP one to make it the same as %1740%? – Cchaddie

I am getting an error when one of the values in regex has characters. Here is my Query. SELECT * FROM users WHERE skills regexp 'C++|Javascript' – Seringa

@AmarjitSingh you will need to escape any special characters, e.g. C\+\+|Javascript. AFAIK MySQL has no built-in escaping function, but your programming langauge might, e.g. php has php.net/manual/en/function.preg-quote.php – Thief

@PaulDixon Thanks. But I need to know characters that can Interfere with Mysql's regexp operator. I am using Node JS in this project. – Seringa

I have asked a question here. #494535 – Seringa

I have a question can we use OR with REGEXP for searching in multiple fields ? – Terpsichore

This does work as expected, but the query speed is not really comparable with the usage of multiple LIKE conditions combined with OR. – Sorrel

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.

Pubescent

Keep in mind that a field with the value NULL won't match REGEXP. You can use IFNULL to solve this problem. WHERE IFNULL(field, '') NOT REGEXP '1740 | 1938' – Tobit

@DanyMarcoux What if i want to use (.*) but it should act like FIELDNAME LIKE '%%', how to use it with regexp, so that when an empty string is passed. it should fetch all the records.. – Introjection

The WHERE field NOT LIKE '%1940 %' OR field NOT LIKE 'test %' will always return all rows. That may perhaps have contributed to not yielding the desired results you mentioned? – Sarraute

Answer 3

Regexp way with list of values

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

Brickkiln

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
        )

Savoyard

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.

Stig

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

Merrymerryandrew

Answer 7

This would be correct:

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

Delacroix

im getting operand should container 1 columns(s) on this approach – Opuscule

Answer 8

Flip operands

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

Chickaree

when you have some field explicitly would equal something eg. an enum for grads 'a','b','c' but not ab,ac or bc create table x(en enum('a,b,c')));insert into x values('a'),('b')en is only a or b doing this method by flipping oprands select * from, x where 'a,c' like concat('%',en,'%') can be safer in SQL Injunction no need to escape charactors like $^ etc. – Chickaree

This is NOT equivalent and WILL NOT WORK for general cases. If you knew that field can only be exactly a, b or c then you should use field IN ('a', 'b', 'c'). But in general cases, this can NEVER replace field LIKE '%a%' OR field LIKE '%b%' OR ... because field itself can be something like magic which would make 'magic' LIKE '%a%' true but the expression 'a,b,c' LIKE '%magic%' false. – Protuberance

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.

Tamah

RLIKE is a synonym for REGEXP at least for MariaDB RLIKE – Whirl

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

Granger

This is an incorrect regular expression. [...] is a character set, meaning that any of the characters in the set are enough to be seen as a match. So any value with the digits '0, 1, 3, 4, 7, 8, 9 or the | pipe character will match this. – Matador

@MartijnPieters is right. The proper way to format the REGEXP string is to remove the surrounding brackets. Just use something like WHERE fiberbox REGEXP '1740|1938|1940' – Photovoltaic

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_,
    '%')

Dowable

Answer 12

You can use like this too:

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

Tunesmith

No, you can't, because this tests for exact string matches, not for substrings. – Matador