Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Is there a MySQL equivalent of PHP's preg_replace?

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?

like image 981
Travis Avatar asked Nov 10 '09 03:11

Travis


People also ask

What is the difference between Str_replace and Preg_replace?

str_replace replaces a specific occurrence of a string, for instance "foo" will only match and replace that: "foo". preg_replace will do regular expression matching, for instance "/f. {2}/" will match and replace "foo", but also "fey", "fir", "fox", "f12", etc.

What is preg_ replace?

The preg_replace() function returns a string or array of strings where all matches of a pattern or list of patterns found in the input are replaced with substrings. There are three different ways to use this function: 1. One pattern and a replacement string.

What is regexp in MySQL?

REGEXP is the operator used when performing regular expression pattern matches. RLIKE is the synonym. It also supports a number of metacharacters which allow more flexibility and control when performing pattern matching. The backslash is used as an escape character.


1 Answers

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

like image 90
sagi Avatar answered Sep 30 '22 04:09

sagi