mySQL replace string + additional string with a static value

Question

Unlike PHP, I don't believe mySQL has any preg_replace() feature, only matching via REGEXP. Here are the strings I have in the code:

http://ourcompany.com/theapplestore/...
http://ourcompany.com/anotherstore/...
http://ourcompany.com/yetanotherstore/...

As you can see, there is a constant in there, http://ourcompany.com/, but there is also a variable string namely theapplestore, anotherstore, etc. etc.

I want to replace the constant string, plus the variable string(s), and then the trailing slash (/) after the variable string(s), with a single shortcode value, namely {{store url=''}}

EDIT If it helps, the store codes are always the same length, they are going to be sch131785 sch185399 sch634019 etc. i.e., they are all 9 characters long

How would I do this? Thanks.

Is the URL the full column's content or is there more to it? — chris85, Jul 21 '15 at 21:31
TAYQ, there is more than just that (it's a `TEXT` field). AND there is more than just one instance of this pattern. — Oliver Williams, Jul 22 '15 at 12:53
I can't see an easy way to do it in mysql in that case; there are no wildcards in the `replace` function. I'd pull the whole column into PHP, run the `preg_replace` on it, then send it back in an `update`. There are also addons to mysql that people have used for this. http://stackoverflow.com/questions/986826/how-to-do-a-regular-expression-replace-in-mysql ("brute force method" at the end is also an interesting idea) — chris85, Jul 22 '15 at 14:12
If I knew how to do it, I'd mark my own question as a possible duplicate of this: http://stackoverflow.com/questions/1705437/is-there-a-mysql-equivalent-of-phps-preg-replace :) — Oliver Williams, Jul 22 '15 at 15:17
The answer is a previous thread; wouldn't that be a duplicate? There are ways to do this but none with a simple native mysql function. You could also try, http://stackoverflow.com/questions/21001172/mysql-for-replace-with-wildcard. — chris85, Jul 22 '15 at 16:07

score 0 · Answer 1 · edited May 23 '17 at 12:06

0

I thought this might be useful: there is currently NO WAY to do this in mysql. Find using REGEXP, yes; replace, no. That said, there is another post with an extension library mentioned, sagi:

Is there a MySQL equivalent of PHP's preg_replace?

edited May 23 '17 at 12:06

Community

1
1

answered Jul 22 '15 at 15:20

Oliver Williams

5,966
7
36
78

score 0 · Answer 2 · answered Jul 27 '15 at 04:20

0

MariaDB-10.0.5 has REGEXP_REPLACE(), REGEXP_INSTR() and REGEXP_SUBSTR()

answered Jul 27 '15 at 04:20

Rick James

135,179
13
127
222

score -1 · Answer 3 · answered Jul 21 '15 at 21:15

-1

You can use following regex,

(ourcompany.com\/\w+\/)

Demo

Uses the concept of Group Capture

answered Jul 21 '15 at 21:15

apgp88

955
7
14

I appreciate your effort however this is more a matter of using MYSQL to replace the string, not recognize it. I have used various MYSQL expressions for replacing and evaluating strings but not one that does this. – Oliver Williams Jul 22 '15 at 12:55

mySQL replace string + additional string with a static value

3 Answers3