Setup lib_mysqludf_preg on Ubuntu 16.04
Using regular expressions in MySQL is something I have wanted for a while. I usually worked around it in the application, but I finally reached the point where I needed this feature directly in the database.
I am running a number of queries and reports out of MySQL databases. We are using a tool called Redash. It connects to the database and can run queries against the data. With the query results, it can create data aggrigations and visualizations. While most of the data is clean and consistant, there is some free entry data that is generally numeric (with the occasional $ or space), but sometimes will contain text values such as ('declined to state'). I only care about the numeric data entered. MySQL out of the box only has the ability to test if a text value matches a regular expressions. I need to be able to format the data on the way out stripping out all non numeric characters, and formatting the result appropriatly.
I struggled with how to approch this problem. Do I go back to the database and fix the results and enforce data entry standards? Optimally, yes, but there is way to much data to fix right now, plus this involved a number of end users. Long term, this is what will happend. Short term, I ran across a MySQL extension (lib_mysqludf_preg) that provices the ability to use Perl Compatible Regular Expression (PCRE) function directly in queries. It does appear to have been updated in 5 years or so as of 2018, but It works perfectly with MySQL 5.7.
- Ubuntu 16.04 Server
- Mysql 5.7.21
Assuming you have a working MySQL install, start by installing the necessary tools.
sudo apt install build-essential libmysqld-dev libpcre3-dev git autoconf libtool
Then cloning/download the repository.
Go into the directory.
Update the configuration. It seems a lot has changed in 5 years. The autoreconf program automatically runs autoheader, aclocal, automake, autopoint and libtoolize as required (src).
autoreconf -f -i
Make and install
sudo make install
Load the new UDFs (User Define Functions) into MySQL. You will need your MySQL root password.
mysql -u root -p < installdb.sql
Using PCRE function in MySQL
There are now 6 new avalable function in MySQL. The definitions are available on the Github as well, but I am copying them here for completeness
PREG_RLIKE( pattern , subject )- test whether subject matches pattern, which is a perl compatible regular expression.
PREG_CAPTURE(pattern, subject [, capture-group] [, occurence] )- capture a named or numeric parenthesized subexpression from a pcre pattern. Capture from a specific match of the regex or the first match is occurence not specified.
PREG_CHECK( pattern )- test whether the given pattern is a valid perl compatible regular expression.
PREG_POSITION(pattern, subject [, capture-group] [, occurence] )- get the position in subject of a named or numeric parenthesized subexpression from a pcre pattern. Capture from a specific match of the regex or the first match if occurence not specified.
PREG_REPLACE(pattern, replacement, subject [ ,limit ] )- perform a regular expression search and replace using a PCRE pattern.
LIB_MYSQLUDF_PREG_INFO()- obtain information about the currently installed version of lib_mysqludf_preg.