Regex errors in MySQL (same regex works in PHP)

Question

I'm working on a search function to take user input and search a mysql fields The idea is to split up the words a user searches for and look for each term in the field(s) in question.

The code used to build the regex I am using was copied from a working pure PHP script (the regex seems to work fine with PHP, so I suspect it is a difference in regex flavor)

Forgive me if this is an obvious problem, still getting a feel for regex.

First I will show you the queries I run and the errors I get

"SELECT * from `images` WHERE ( `name` REGEXP '^(?=.*gallardo).*$' OR `meta` REGEXP '^(?=.*gallardo).*$' ) ORDER BY `changestamp` DESC

Got error 'repetition-operator operand invalid' from regexp"

SELECT * from `images` WHERE ( `name` REGEXP '^(?=.*gallardo)(?=.*lambo).*$' OR `meta` REGEXP '^(?=.*gallardo)(?=.*lambo).*$' ) ORDER BY `changestamp` DESC

gives the same error

to compile this regex, I take user submitted input like "gallardo lambo" and run this PHP procedure

if(isset($_GET['keyword'])){

        $searchterms = explode(' ',$_GET['keyword']);

        $regstr = '^';

        foreach($searchterms as $i => $v)
        {
            if($v)
                $regstr .= '(?=.*'.preg_replace('/[-\[\]\/\{\}\(\)\*\+\?\.\\\^\$\|]/', "\\$&", $v).')'; 
        }

        $regstr .= '.*$';

    }

then I put it in the query like

"( `name` REGEXP '$regstr' OR `meta` REGEXP '$regstr' )"

When I use this method with php's preg_match(), it seems to work.

Any insight? Feel free to tell me that I have no idea what I'm doing.

Thanks!

My intuition tells me that using regex in sql query for search is probably slowwwww — Kristian
– Kristian, Commented May 4, 2012 at 23:04
(?= is PCRE syntax; MySQL uses Henry Spencer's reimplementation of v8 regex. — geekosaur
– geekosaur, Commented May 4, 2012 at 23:05
See stackoverflow.com/questions/39726749 for a possible workaround — Pekka
– Pekka, Commented Sep 27, 2016 at 14:14

eggyal · Accepted Answer · 2012-05-04 23:04:22Z

5

MySQL regex are more limited than PHP - they don't support back references or lookahead. See the manual.

You may want to look into fulltext search instead.

answered May 4, 2012 at 23:04

eggyal

127k18 gold badges220 silver badges244 bronze badges

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

Collectives™ on Stack Overflow

Regex errors in MySQL (same regex works in PHP)

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related