MySQL like with IN clause

Question

Is it possible to extend a LIKE expression with an IN clause?

At the moment I got the following SQL:

select * 
from SECURITY_PERMISSION permission
where permission.PERMISSION_ID like '%_10498_%'
and permission.PERMISSION_ID like '%_OBJ_VIEW%'

I want to pass a list of Numbers to my like expression. Something like that:

select * 
from SECURITY_PERMISSION permission
where permission.PERMISSION_ID like '%_IN(list_of_numbers)_%'
and permission.PERMISSION_ID like '%_OBJ_VIEW%'

With like no, however you may be able to do this via regex (`|` operator) — Shadow, Jan 31 '19 at 07:45

score 1 · Accepted Answer · answered Jan 31 '19 at 07:47

1

That is bad database design. But you can use REGEXP instead:

WHERE permission.PERMISSION_ID REGEXP '_(123|456|789)_'

It is still bad database design.

answered Jan 31 '19 at 07:47

Salman A

262,204
82
430
521

yes it is a bad design ;-) but I it's not designed by me, I just have to work with it..... – LStrike Jan 31 '19 at 08:39
For more details you can check david-carroll answer from this question. https://stackoverflow.com/questions/1127088/mysql-like-in – nafischonchol Oct 14 '21 at 07:56

score 1 · Answer 2 · answered Jan 31 '19 at 07:51

1

You can also use REGEXP like this, for a field contains multiple values seperated by comma for example:

WHERE column REGEXP '[[:<:]]1|2|3|4[[:>:]]'

answered Jan 31 '19 at 07:51

abeyaz

3,034
1
16
20

MySQL like with IN clause

2 Answers2