MySQL RegEx Search Query

Question

I am writing a custom search engine for my website. I am trying to make use of MySQL REGEXP feature. I would like to be able to search for a word separated by spaces to avoid the chances of getting suffixes or prefixes on a word. For example I am trying to search for "appreciate" I want appreciate, not appreciated or unappreciate or unappreciated. Any ideas on how I could do this with MySQL's REGEXP? My idea for this was to look for spaces like maybe so:

^appreciate$|^appreciate[:space:]|[:space:]appreciate$|[:space:]appreciate[:space:]

I am sure they is a better way of doing it and I have no idea if that even works

What about if I wanted to add a mutiple words and wanted padding incase somthing appears between them. For example: I want something like "Fish Stick" but want to allow for something such as "I love Fish and a Stick". My idea was: [[:<:]]Fish[[:>:]]?.{1,}[[:<:]]Stick[[:>:]] — user1108076
– user1108076, Commented Dec 20, 2011 at 15:50
This seems to have worked for me. Well it appears like the more I play with RegEx the easier it becomes to understand and write it. I honestly felt like it was impossible for me to learn it! — user1108076
– user1108076, Commented Dec 20, 2011 at 15:56

pgl · Accepted Answer · 2011-12-20 15:23:16Z

2

I think what you want is something like this:

SELECT 'I appreciate you' REGEXP '[[:<:]]appreciate[[:>:]]'; /* matches */

[[<:]] and [[>:]] are word boundaries. From the manual:

These markers stand for word boundaries. They match the beginning and end of words, respectively. A word is a sequence of word characters that is not preceded by or followed by word characters. A word character is an alphanumeric character in the alnum class or an underscore (_).

Edit: just to clarify, this also deals with situations where there's a newline character after the word, or a comma, etc

answered Dec 20, 2011 at 15:23

pgl

8,1512 gold badges26 silver badges31 bronze badges

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

1 Comment

user1108076 Over a year ago

Works perfect thank you! I didnt read up on but I saw those ones and couldnt understand at the time what 'word boundaries' ment. Thanks!

Eugen Rieck · Accepted Answer · 2011-12-20 15:12:56Z

0

What about:

^\s*appreciate(\s+.*)*$

Between the start and the word there may be 0+ whitespace parts
then comes the word
then if something comes after that, it has to start with whitespace

answered Dec 20, 2011 at 15:12

Eugen Rieck

65.6k10 gold badges75 silver badges97 bronze badges

1 Comment

pgl Over a year ago

MySQL's REGEXP function doesn't support \s.

Álvaro González · Accepted Answer · 2011-12-20 15:25:12Z

0

You can seek for non-alphabetic characters:

[^[:alpha:]]+

... or just word boundaries:

[[:<:]]foo[[:>:]]

Before making a choice, don't forget to make some tests with commas, dots and non-English chars. Also, take into account that MySQL does not fully support regular expressions in multi-byte strings (such as UTF-8).

answered Dec 20, 2011 at 15:25

Álvaro González

147k45 gold badges282 silver badges378 bronze badges

1 Comment

user1108076 Over a year ago

Works perfect thank you! I didnt read up on but I saw those ones and couldnt understand at the time what 'word boundaries' ment. Thanks!

Collectives™ on Stack Overflow

MySQL RegEx Search Query

3 Answers 3

1 Comment

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related