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

You might want to have a look at mysql pattern matching http://dev.mysql.com/doc/refman/5.1/en/pattern-matching.html

Answer 2

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.

Answer 3

You can use regex in MySQL