0

I'm trying to rebuild a website which previously had a lot of case sensitive URLs to be all lowercase. As many of these are embedded in article texts which sit within a MySQL database (actually MariaDB) I figured I would be able to do a regex replace of some sort.

I have got pretty close using the below (not sure if this is MariaDB specific):

UPDATE field_data_body SET `body_value` = REGEXP_REPLACE(`body_value`, '(href=")([^"]*)', LOWER(CONCAT('\\1','\\2')))

This finds the links and I have tested it replaces the them. However, the selected segment isn't translated to lowercase, which leads me to assume I can't use the LOWER function within the REGEXP_REPLACE function.

An example of the text might be:

Lorem ipsum dolor sit amet, <a href="/Consectetur.htm">Consectetur</a> adipiscing elit, sed do eiusmod tempor incididunt ut labore et dolore magna aliqua.

So, any suggestions how I can apply the lowercase transformation to the regex? Thanks.

Chris Leather
  • 45
  • 11
  • regex is not meant to parse html.. Because HTML can have the same structure as XML i would advice MySQL [XML functions](https://dev.mysql.com/doc/refman/8.0/en/xml-functions.html) to select and update the HTML.. p.s this only work when the HTML is valid to the XML rules. – Raymond Nijland Mar 25 '19 at 16:32
  • 1
    Why cant you just lower-case the entire URL? – Gordon Linoff Mar 25 '19 at 16:35
  • Otherwise you need to make tricker [SQL](https://stackoverflow.com/questions/49560296/mysql-replace-how-replace-all-occurrences-of-a-char-in-every-distinct-substrin/49561238#49561238) and use a sql number generator and nested `substring_index()` functions to split on the html tags. – Raymond Nijland Mar 25 '19 at 16:35
  • @GordonLinoff This is the question... How do I lowercase the URL. I can lowercase the whole text but not just the bit I've pulled out using the regex – Chris Leather Mar 25 '19 at 16:57
  • Either I have asked the wrong question in the wrong place or I'm going to have to write a script to do this... Didn't seem like that difficult a thing to do on the face of it. Have edited the title to see if this helps... – Chris Leather Mar 27 '19 at 12:19
  • 1
    @GordonLinoff - I think he has text with embedded urls (think: web page body). The task is to modify only the urls. – Rick James Apr 18 '19 at 21:04
  • 1
    Some tasks are better done in a programming language other than SQL. I recommend you use PHP or whatever language you are familiar with. – Rick James Apr 18 '19 at 21:05
  • @RickJames - I did end up using a PHP script for this. I'm sure there must be an SQL way of doing this but I didn't have the luxury of spending anymore time on researching it. – Chris Leather Apr 19 '19 at 09:43
  • 1
    @ChrisLeather - There may be a way to do it in SQL, but I cringe at the complexity of it, while thinking about the relative simplicity of doing it in PHP. When I can't find my hammer, I will sometimes use my fist. Ouch. – Rick James Apr 19 '19 at 19:33

0 Answers0