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: http://dev.mysql.com/doc/refman/5.1/en/regexp.html. This works nicely for your standard regular expressions, but it's a bit lacking in functionality. A quick Google search brought me here: https://launchpad.net/mysql-udf-regexp. 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
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.
Jamie Munro is the author of ASP.NET MVC 5 with Bootstrap and Knockout.js,
Knockout.js: Building Dynamic Client-Side Web Applications,
20 Recipes for Programming MVC 3,
and 20 Recipes for Programming PhoneGap. He has been developing websites and web applications for over 20 years. Jamie began his writing career in 2009. As the success of Jamie's blog grew, he turned his writing passion to books about web development in hopes that his many years of experience could be passed on to his readers. 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.
(\[codesyntax)(.*?)(\])
Related Posts
Tutorials
Learn how to code in HTML, CSS, JavaScript, Python, Ruby, PHP, Java, C#, SQL, and more.