MySQL Replace string with variable

Question

I'm wondering, if there is any syntax to replace such a link in database:

<!-- m --><a class="postlink" href="link">text</a><!-- m -->

into something like:

[url=link]text[/url]

I know, I can make dump and relpace it in notepad++, but it would be better to make in in mysql for me.

Please make a clear description.Means what do you want with this text and database vice versa? @Flip Fratczak — Muhammad Ashikuzzaman, Jul 26 '14 at 11:20
That would be possible to achieve using a regular expression, but I think there isn't a search and replace function that receives a regular expression as an argument in MySQL. For that reason, try this: https://github.com/hholzgra/mysql-udf-regexp. — Luka, Jul 26 '14 at 11:21
Agree with @Luka. Regexp would be fine, unfortunately in MySQL it supports only checks (as discussed [here](http://stackoverflow.com/questions/986826/how-to-do-a-regular-expression-replace-in-mysql)) — Vladimir Chervanev, Jul 26 '14 at 12:55

score 0 · Accepted Answer · answered Jul 26 '14 at 12:46

Using INSTR and SUBSTR you can exclude Url and Title. For instance, create test data:

CREATE TABLE TableName (url varchar(255));
INSERT INTO TableName VALUES
      ('<!-- m --><a class="postlink" href="link">A</a><!-- m -->');

Query link between 'href="' and '">':

select 
  substr(url, instr(url, 'href="')+6, instr(url, '">')-instr(url, 'href="')-6) link 
  from TableName;

+------+
| link |
+------+
| link |
+------+

Query title between '">' and '</a>':

select 
  substr(url, instr(url, '">')+2, instr(url, '</a>')-instr(url, '">')-2) title 
  from TableName;

+-------+
| title |
+-------+
| A     |
+-------+

Update all required rows:

UPDATE TableName 
  SET url = concat('[url=', substr(...), ']', substr(...), '[/url]' )
  WHERE url like '%<a href=%';

MySQL Replace string with variable

1 Answers1