MySQL Like multiple values

Question

I have this MySQL query.

I have database fields with this contents

sports,shopping,pool,pc,games 
shopping,pool,pc,games 
sports,pub,swimming, pool, pc, games

Why does this like query does not work? I need the fields with either sports or pub or both?

SELECT * FROM table WHERE interests LIKE ('%sports%', '%pub%')

score 384 · Answer 1 · edited Jan 29 '21 at 12:58

384

Faster way of doing this:

WHERE interests LIKE '%sports%' OR interests LIKE '%pub%'

is this:

WHERE interests REGEXP 'sports|pub'

Found this solution here: http://forums.mysql.com/read.php?10,392332,392950#msg-392950

More about REGEXP here: http://www.tutorialspoint.com/mysql/mysql-regexps.htm

edited Jan 29 '21 at 12:58

Zer0

1,428
8
23

answered Sep 27 '11 at 09:31

jazkat

5,212
3
24
17

if you are passing in an unknown amount of keywords as a string (a|b|c...), the regexp is the only way to go if you want to do LIKE, is it? – frequent Jun 21 '12 at 12:53
1

Do you know if this can be done with a subquery? Let's say I have a column of words I need to search for, how can I replace 'sports|pub' with a subquery? – AdamMc331 Oct 25 '14 at 19:25
Hey, could you please tell me the REGEXP for LIKE instead of %LIKE%, I am trying to fetch exact strings... – Deepanshu Goyal Mar 24 '15 at 08:31
3

This solution blows the first one out of the water – Donato Apr 06 '15 at 21:31
is it possible to use something like this format? WHERE interests REGEXP 'ex1|ex2|ex3|ex4|ex5|ex6|.... ex100' ??? what's its limitation? – Mahdi Jazini Oct 06 '15 at 12:12
3

To get the regexp value from a column: `(select group_concat(myColumn separator '|') from..)` – daVe Nov 28 '15 at 01:06
are you sure `REGEXP` is faster of `LIKE` -> MySQL doesn't use index with REGEXP. This is a primary issue, nice article related -> http://www.dbasquare.com/2012/03/31/mysql-queries-with-regexp/ – WonderLand Mar 18 '16 at 04:31
Thanks Jazkat. REGEXP one works perfectly & it's more reliable – SUN May 22 '17 at 07:10
Cool, It is indeed faster, for my query 'like' runs forever and results in connection timeout. But this REGEXP returns me result in less than 5 seconds! – Akshay Lokur Nov 17 '17 at 09:40
Super solution! Much preferred! Thanks! – MrrMan Apr 20 '18 at 10:15
in SQL, REGXP is much lower than LIKE – Yoni Melki Apr 28 '20 at 13:01
REGXP is very slow in execution – Martin T. Apr 01 '21 at 07:42
Do you know a way to do that in SQL Server? – Felipe Salles Lopes Aug 25 '21 at 19:31
For case sensitive, use BINARY: `WHERE interests REGEXP BINARY 'sports|pub'` – Gael Mar 02 '22 at 00:26

score 154 · Accepted Answer · answered Nov 13 '10 at 11:10

154

The (a,b,c) list only works with in. For like, you have to use or:

WHERE interests LIKE '%sports%' OR interests LIKE '%pub%'

answered Nov 13 '10 at 11:10

Andomar

225,110
44
364
390

5

This wouldn't be beneficial in multiple (let's say 5 or more dynamic searchable query), thus, it'd be better to use regexp. – Shayan Ahmad Dec 14 '18 at 15:03
1

@ShayanAhmad What do you mean by beneficial? In terms of creating the query or query exection time? Isn't LIKE a lot more otpmized than REGEXP ? – XCS Aug 23 '20 at 21:23

score 43 · Answer 3 · answered Jan 16 '16 at 11:38

43

Why not you try REGEXP. Try it like this:

SELECT * FROM table WHERE interests REGEXP 'sports|pub'

answered Jan 16 '16 at 11:38

Ahmad Hussain

2,397
19
26

6

Yeah!! I want it the opposite way. So it is `SELECT * FROM table WHERE interests NOT REGEXP 'sports|pub'` (>‿◠)✌ – Pathros Nov 04 '16 at 16:11
6

How this answer is different from `jazkat` answer submitted 5 years before yours? – Vaidas Sep 26 '17 at 08:20
@Vaidas - thank you - was asking myself the same question ... :D – theFriedC Jul 04 '18 at 12:45
it's sort and simple (●'◡'●) – rajkanani Apr 07 '22 at 07:09

score 32 · Answer 4 · edited Aug 17 '20 at 10:11

32

You can also use REGEXP's synonym RLIKE as well.

For example:

SELECT *
FROM TABLE_NAME
WHERE COLNAME RLIKE 'REGEX1|REGEX2|REGEX3'

edited Aug 17 '20 at 10:11

Ruben Helsloot

11,812
5
19
40

answered May 09 '18 at 07:37

iamharish15

1,560
14
18

11

Just note for everyone that RLIKE and REGEXP are synonym – Intacto Aug 19 '19 at 19:11

score 10 · Answer 5 · edited May 08 '17 at 03:00

10

Don't forget to use parenthesis if you use this function after an AND parameter

Like this:

WHERE id=123 and(interests LIKE '%sports%' OR interests LIKE '%pub%')

edited May 08 '17 at 03:00

Jin Kwon

18,308
12
99
160

answered Sep 17 '15 at 14:21

Luan

101
1
2

Alexis Dufrenoy · Answer 6 · 2018-01-23T09:54:29.233

8

Your query should be SELECT * FROM `table` WHERE find_in_set(interests, "sports,pub")>0

What I understand is that you store the interests in one field of your table, which is a misconception. You should definitively have an "interest" table.

edited Jan 23 '18 at 09:54

answered Nov 13 '10 at 11:10

Alexis Dufrenoy

11,484
11
80
123

2

I think it should be `SELECT * FROM table WHERE find_in_set(interests, 'sports,pub')`, but this technique is likely to outperform regex in most situations. – Chris Strickland Sep 14 '17 at 04:02

score 8 · Answer 7 · answered Sep 26 '19 at 13:16

8

Or if you need to match only the beginning of words:

WHERE interests LIKE 'sports%' OR interests LIKE 'pub%'

you can use the regexp caret matches:

WHERE interests REGEXP '^sports|^pub'

https://www.regular-expressions.info/anchors.html

answered Sep 26 '19 at 13:16

vadim

903
12
28

this is not working, i have values like "1, 2,3, 30, 20" . So when I run query WHERE interests REGEXP '3^|2^' it will not return any data. – pratik vekariya Oct 23 '21 at 07:22

score 2 · Answer 8 · edited May 09 '18 at 08:32

2

Like @Alexis Dufrenoy proposed, the query could be:

SELECT * FROM `table` WHERE find_in_set('sports', interests)>0 OR find_in_set('pub', interests)>0

More information in the manual.

edited May 09 '18 at 08:32

4b0

20,627
30
92
137

answered Oct 20 '17 at 09:02

Franc Drobnič

885
10
13

score 1 · Answer 9 · answered Aug 20 '19 at 14:48

More work examples:

SELECT COUNT(email) as count FROM table1 t1 
JOIN (
      SELECT company_domains as emailext FROM table2 WHERE company = 'DELL'
     ) t2 
ON t1.email LIKE CONCAT('%', emailext) WHERE t1.event='PC Global Conference";

Task was count participants at an event(s) with filter if email extension equal to multiple company domains.

MySQL Like multiple values

9 Answers9

Linked

Related