Jump to content
Larry Ullman's Book Forums

Regular Expressions

Recommended Posts



Could someone help me with a regular expression in MySQL?


If I test the following pattern in PHP:



the results are fine since I get a match for


l'eau OR l'eau coule

d'eau OR cours d'eau

les eaux OR les eaux ruissellent


but no match for

carreaux (the word ends with 'eaux', but "eau" or "eaux" is not the beginning of the word, and doesn't occur just after an apostrophe)

beaucoup ('eau' or 'eaux' is neither the beginning nor the end of the word)


Yet if I try the very same pattern in the database, using phpMyAdmin, I get no results at all although all the above examples are in the database.


Could someone explain why this is happening, and what I need to do in MySQL?


In case you wonder why I'm not using a FULLTEXT search, I plan to use regular expressions for words that are 1, 2 or 3 characters long, and a FULLTEXT search for longer words or expressions.


With thanks for your help,

Link to comment
Share on other sites

Thank you for answering, Larry. No, apparently it isn't a collation or character set issue (for once!). It's apparently a problem with word-boundaries since this query returns no result:

SELECT id_syntagme, syntagme_c FROM syntagmes WHERE syntagme_c REGEXP '\b(\')?((eau){1}|(eaux){1})\b'


whereas this query works… except that it returns all the entries containing "eau" or "eaux", including "carreaux" or "beaucoup" (which I need to exclude):

SELECT id_syntagme, syntagme_c FROM syntagmes WHERE syntagme_c REGEXP '(\')?((eau){1}|(eaux){1})'


Is there another way of specifying word-boundaries in a MySQL query?


With thanks for your help,

Link to comment
Share on other sites

I've just been looking at the MySQL 5.1 Manual, and I found the explanation here:




In the MySQL version of regular expressions, \b means a backspace. And word-boundaries are instead shown like this:

REGEXP '[[:<:]]word[[:>:]]'


So, rewriting my previous regular expression like this:

REGEXP '[[:<:]](\')?((eau){1}|(eaux){1})[[:>:]]'

returned just the results I wanted.

  • Upvote 1
Link to comment
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible.

Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.


  • Create New...