CODEWITHSUNDEEP
mysqlregex
t1m0thy
t1m0thy

Reputation: 2786

Multiple Column REGEX search in MySQL

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?

Upvotes: 4

Views: 6104

Answers (2)

anubhava
anubhava

Reputation: 785196

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

Upvotes: 7

Explosion Pills
Explosion Pills

Reputation: 191749

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.

Upvotes: 2

Related Questions