Multiple Column REGEX search in MySQL

Question

I am trying to search multiple columns in my Db using a regex. It works but using many and/or statments. I was wondering if it was possible to use something like this;

SELECT * FROM table REGEXP 'regex' IN (col1, col2, col3,.....)

This doesn't work, it was a guess at the syntax because I can't find anything similar by searching online. Is this a stupid idea or am I missing something very simple?

I think correct key word is REGEXP not REGEX

Jeribo
– Jeribo

2013-12-11 18:48:01 +00:00
Commented Dec 11, 2013 at 18:48 — Jeribo
– Jeribo, Commented Dec 11, 2013 at 18:48
Fulltext search it is called.

Mihai
– Mihai

2013-12-11 18:52:00 +00:00
Commented Dec 11, 2013 at 18:52 — Mihai
– Mihai, Commented Dec 11, 2013 at 18:52

anubhava · Accepted Answer · 2013-12-11 18:55:47Z

7

If you want to regexp search a value in multiple columns then you can do:

SELECT * FROM table where CONCAT(col1, col2, col3) REGEXP 'search-pattern';

answered Dec 11, 2013 at 18:55

anubhava

790k67 gold badges603 silver badges671 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

t1m0thy Over a year ago

OK, any idea of performance implications of using CONCAT()? isn't it a great deal slower? not an issue in this case but I'm here to learn!

anubhava Over a year ago

Performance will depend upon how many columns are you trying to concat. Besides this is like finding a needle in haystack. So speed will vary a lot depending upon size of haystack. IMO this is still better than writing 10 conditions with OR if you have 10 columns to search for.

Explosion Pills · Accepted Answer · 2013-12-11 18:52:50Z

2

The syntax for MySQL REGEX comparison is

expr REGEXP pattern_string

You cannot use it with IN. You would have to do:

SELECT * FROM `table` WHERE
col1 REGEXP 'regex'
OR col2 REGEXP 'regex'
OR col3 REGEXP 'regex'

You could also use RLIKE -- they are synonyms.

answered Dec 11, 2013 at 18:52

Explosion Pills

192k56 gold badges341 silver badges417 bronze badges

1 Comment

t1m0thy Over a year ago

Thanks, that's what I was doing It was just an idea!

Collectives™ on Stack Overflow

Multiple Column REGEX search in MySQL

2 Answers 2

2 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related