Regular Expressions with Mysql Regular Expressions with Mysql

I must admit, this is something that I've never done until just recently; creating a regular expression in Mysql. I've typically been able to accomplish whatever I wanted to with a LIKE statement or some server-side code.

However, just recently I updated the plugin I use to output sample code. I was previously using a syntax of [codesyntax lang="php"] and now I needed to change it simply be [code]. This wasn't that simple since I've done hundreds of blogs with several different languages. In searching I had over 15 distinct differences hence, regular expressions to the rescue!

By default, Mysql has a built-in regular expression operator. Documentation is here: This works nicely for your standard regular expressions, but it's a bit lacking in functionality. A quick Google search brought me here: Now this is quite nice, it implements three different regular expression operations as UDFs (User Defined Functions): REGEXP_LIKE, REGEXP_SUBSTR, REGEXP_INSTR, and a fourth for actual replacement of data: REGEXP_REPLACE.

The neat part of these functions is they include an optional third and fourth parameter for the position and occurrence of the regular expression match. A nice addition when you only want to match to a specific occurrence of the string.

A nice simple regular expression as follows works nicely to grab them all:


Now I can nice and quickly use the REGEXP_REPLACE function to change them all to [code].

If you are familiar with regular expressions in Oracle, these UDFs are meant to mimic them as closely as possible.

Published on Oct 2, 2012

Tags: SQL Tutorials for Beginners, Intermediate and Advanced Users | regexp

Related Posts

Did you enjoy this article? If you did here are some more articles that I thought you will enjoy as they are very similar to the article that you just finished reading.


Learn how to code in HTML, CSS, JavaScript, Python, Ruby, PHP, Java, C#, SQL, and more.

No matter the programming language you're looking to learn, I've hopefully compiled an incredible set of tutorials for you to learn; whether you are beginner or an expert, there is something for everyone to learn. Each topic I go in-depth and provide many examples throughout. I can't wait for you to dig in and improve your skillset with any of the tutorials below.