0

Short version: I need mysql code that will change

[href="http://a-random-domain.com"]hyperlink[/href]

into

<a href="http://a-random-domain.com">hyperlink</a>

  • without messing up the domain part, and
  • without accidentally converting any unrelated instances of "] that might occur in the text of a field.
  • Domain will be unique/different every time it is encountered.

Long version: I am migrating an old database for use with a new application. The old database has a text field that includes content such as:

This is a data field with a [href="http://somedomain.com"]hyperlink[/href] and more data and possibly other hyperlinks.

I need to update it to standard html, e.g.

This is a data field with a <a href="http://somedomain.com">hyperlink</a> and more data and possibly other hyperlinks.

Fixing the [href= and [/href] is simple enough using REPLACE

update table set field = replace(field, '[href=', '<a href=');

but I get tripped up on the "] closing bracket of the a href tag. And in the database there are other instances of "] that shouldn't be modified, so I can't just replace on "]

Does mysql have some sort of regex "lookahead" or other way to accomplish this?

Thanks much!

billrichards
  • 2,041
  • 4
  • 25
  • 35
  • 2
    Possibly look into http://stackoverflow.com/questions/986826/how-to-do-a-regular-expression-replace-in-mysql for such? If this is a one-shot deal, you might consider doing it client-side, though; e.g. a short Perl script to SELECT * / UPDATE each row. Inefficient, perhaps, but usable for a simple fix like this. – BRPocock Dec 12 '11 at 16:34
  • Thanks! Not sure this will work for me because the characters like "] only need to become "> when they are used as part of a hyperlink. If there are no mysql solutions offered, I will definitely pursue handling this out of mysql with a Perl-like script. Thanks again – billrichards Dec 12 '11 at 16:37
  • In your "short version", I think you mean `[href="http://a-random-domain.com"]hyperlink[/href]` rather than just `[href="http://a-random-domain.com"]`? – ruakh Dec 12 '11 at 16:44
  • ruakh you are correct - edited to fix. thank you – billrichards Dec 12 '11 at 17:04

1 Answers1

2

http://dev.mysql.com/doc/refman/5.1/en/regexp.html

MySQL supports regex-functions for 'evaluating' purpose only. So, you can't use it on replacing or manipulating data. It'll be better using high level script language for it.

lqez
  • 2,898
  • 5
  • 25
  • 55