MySQL Regular Expressions

I have a table that stores a bunch of keywords. The keywords can be either with or without quotes (“). That is, the keywords could be PageRank, “Web 2.0” and startups. My requirement was to sort these keywords but ignoring the quotes. That is, instead of displaying them as “Web 2.0”, PageRank , startups, I wanted them to show up as PageRank, startups, “Web 2.0”

That’s when I started looking at MySQL regular expressions. A few interesting things. First and foremost, it looked odd to me that the syntax is something like

“select stringcolumn regex ‘some-pattern'”

instead of

“select regex(stringcolumn,’some-pattern’)”

The second thing is in how I had to write my sql to achieve the above requirement. I ended up doing something like

select …, case when keyword regex ‘”.*”‘ = 1 then substr(keyword,2,length(keyword)-2) else keyword end okw from … where … order by owk;

This felt quite ugly, but perhaps a better hypothetical syntax could have been like

regex(keyword,'”(.*)”‘,”nvl($1,$0)”)

where the 3rd argument is an SQL expression evaluated with special bind variables captured from the regular expression. (Ignore the usage of nvl which is a Oracle syntax, but you get the point).

Advertisements

1 Comment

Filed under MySQL

One response to “MySQL Regular Expressions

  1. “select stringcolumn regex ’some-pattern’” ??

    No “regex”, but yes “regexp” or “rlike”.

    NOT REGEXP – Negation of REGEXP
    REGEXP – Pattern matching using regular expressions
    RLIKE – Synonym for REGEXP

    http://dev.mysql.com/doc/refman/5.0/en/regexp.html

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s