Is there a MySQL equivalent of PHP's preg_replace?

Question

I have a to match a field in MySQL, for which I thought I could use a regular expression, but it appears that MySQL doesn't have the functionality I need to do the job. Here's the scenario:

I have a variable in PHP called $url. Let's say this variable is set as the string "/article/my-article/page/2". I also have a table of URLs in MySQL from which I would like to pull content. The URLs stored in my table, however, include wildcards.

Previously, I had this set up so that the value stored in the table looked like this: "/article/%/page/%".

With that configuration, I could just run:

SELECT * FROM urls WHERE '$url' LIKE url

And this would match, which is the desired functionality.

What I'd like to do now, is allow a more advanced wildcard, such that instead of "/article/%/page/%", my MySQL data could be "/article/{{slug}}/page/{{page_no}}".

I want to create a SQL query that will match this data, using the same $url input. LIKE is no longer the correct comparison, since I'm not using the built-in "%" wildcard, but rather {{.*}}. Any ideas how to accomplish this?

Answer 1

There is a library of user defined functions that gives you preg_replace in MySQL: http://www.mysqludf.org/lib_mysqludf_preg/

Answer 2

It sounds like what you want to do is have the new syntax in the database where the URLs have placeholders you would pass to your php-based (sprintf) variable replacement code, but still be able to do the original comparisons to match the URL.

If I understand correctly you want to take a new URL format

/article/{{slug}}/page/{{page_no}}

and match it against something like

/article/my-article/page/2

The preg plugin sagi mentioned can do the substitution you need, which will turn one of your newly formatted URLs into the original format you used to determine the match using the LIKE syntax. The following query:

SELECT PREG_REPLACE('/({{.*?}})/', '%', `url`) FROM urls;

Would turn the new url (/article/{{slug}}/page/{{page_no}}) into what it was originally

/article/%/page/%

which can then be fed back through your original query, something like this:

SELECT * FROM urls
WHERE '/article/my-article/page/2' LIKE preg_replace('/({{.*?}})/', '%', `url`);

Some binary distributions like MAMP, XAMMP etc have the plugin already installed, but it isn't installed on a lot of systems like Macports / Ubuntu. Here are a couple of articles about installing the preg plugin. Hope it helps.

http://quickshiftin.com/blog/2011/04/installing-mysql-preg-plugin-osx-macports/

http://quickshiftin.com/blog/2011/12/installing-the-mysql-preg-plugin-on-ubuntu-with-apt-get/

Answer 3

The user sagi above mentions http://www.mysqludf.org/lib_mysqludf_preg/ but as this answer is very old as are most of the tutorials, I wanted to expand on this for the sake of newcomers to this question.

Firstly, the library is really great and speaking from experience I can say it seems to have been maintained and is still working flawlessly in 2015.

To get it installed and working, I could only find some very dated tutorials so thought I would share what I did that worked for me installing latest stable release (v1.1) on Ubuntu 14.04:

apt-get update
apt-get install libpcre3-dev libmysqlclient-dev build-essential libmysqld-dev libpcre3-dev
wget https://github.com/mysqludf/lib_mysqludf_preg/archive/lib_mysqludf_preg-1.1.tar.gz
tar -xzf lib_mysqludf_preg-1.1.tar.gz
cd lib_mysqludf_preg-1.1
./configure
make  install
make installdb
service mysql restart

You should now have all the following functions available to you:

lib_mysqludf_preg_info
preg_capture
preg_check
preg_replace
preg_rlike
preg_position

Answer 4

Starting from mysql 5.5, you can use RLIKE :

• either store url in REGEXP-style, and query with

  SELECT * FROM urls WHERE '$url' RLIKE url;

• or keep it in LIKE-style, and make a replacement (% by .* , _ by .):

  SELECT * FROM urls WHERE '$url' RLIKE REPLACE(REPLACE(url, '%', '.*'), '_', '.');

  To be complete, you would have to do other replacements to escape chars that are regexp-significant : ? \ () [] ... (see php function preg_quote)

Answer 5

I've found a solution. It's not ideal, but I'll put it in here in case a pure SQL solution never surfaces. I can leave the url field in MySQL equal to "/articles/%/page/%" and add another field called variables that stores a list of variable names to be used. That way I can use my original query, then replace the "%" characters in the return value with "{{variable}}" in PHP with sprintf after I've retrieved the data.

Still, I'd like to see this solved in SQL if possible, since I think the concept is valuable.