MySql Regexp to search from user input

Question

This one is killing me,i'm a total noob with regex. Im trying to make a site search and compare the entered text with the titles column in the database. Basically what i want to accomplish with regex is LIKE '%word1 word2 word3%' , but with some string variatons.

For example :

SELECT * FROM table WHERE title REGEXP 'w[ao]rd'

works just fine. It looks for word and ward. But when i want multiple words it returns nothing.

SELECT * FROM table WHERE title REGEXP 'w[ao]rd sec[or]nd th[io]rd'

I don't want to search for word OR second OR third i want to have them all in the title,just like in the upper case with LIKE.

I was thinking maybe to explode the search string the user inputs then in a foreach do a regexp word by word,but don't know how to concatenate all of the variations into one query.

EDIT: I forgot to mention that im searching in a utf8_unicode_ci collated table. So really im trying to do a [cčć]orak type of search.

CrayonViolent · Accepted Answer · 2011-02-07 16:15:21Z

2

   SELECT * FROM table WHERE title REGEXP 'w[ao]rd|sec[or]nd|th[io]rd'

edit:

"okay so you want them to all be in the title and in that order?"

   SELECT * FROM table WHERE title REGEXP 'w[ao]rd.*sec[or]nd.*th[io]rd'

This will look for those words (and the variations) and make them be in that order, so for example

word blahblah second more blah third

edited Feb 7, 2011 at 16:15

answered Feb 7, 2011 at 16:03

CrayonViolent

32.6k6 gold badges61 silver badges80 bronze badges

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

5 Comments

Doktor83 Over a year ago

thanks,but this is doing OR,and i said i don't need it that way.

CrayonViolent Over a year ago

well there are other ways to do it, like doing "where title regexp '..' OR ... but it still boils down to using some kind of OR syntax...because..how else do you expect to say "look for this OR this" ? That's the whole point of OR type syntax.

Doktor83 Over a year ago

ok i get that,but i tought this can be accomplished in one regexp code. So basically if u search for 'big red apple' i want it only to find sentences that have these words in the same order,and not big OR red OR apple.

CrayonViolent Over a year ago

okay so you want them to all be in the title and in that order? see edit

Doktor83 Over a year ago

yupp. but it looks like i found what was the problem. Very interesting indeed. Utf8 was making all the trouble.

Collectives™ on Stack Overflow

MySql Regexp to search from user input

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related