MySQL REPLACE : How replace all occurrences of a char in every distinct substring delimited by the same head and tail

Question

I have this string:

<p><span><b>C10373 - FIAT GROUP AUTOMOBILES/RAMO DI AZIENDA DI KUEHNE + NAGEL</b></span>
<p>la somma pari a € 400+IVA per l’attività</p>
<p>TELE+ A 20.000 LIRE AL MESE </p>
<li>a mano o via fax al numero +39.00.0.0.0.00.</li>
<p>Il punteggio base sarà incrementato di un <strong>+ </strong>al ricorrere di ciascuna delle seguenti condizioni:</p>
<li><a href="/aaa/gare/CIGZB81E5568D+RDO1560277+Obblighi+contattuali-signed.pdf" title="">Obblighi contrattuali</a></li> 
<li><a href="/aaa/gare/CIGZB81E5568D+RDO1560277+Obblighi+contattuali-signed.pdf" title="">Obblighi contrattuali</a></li> 
<li><a href="/aaa/gare/CIGZB81E5568D+RDO_1560277Lettera_Invito.pdf" title="">Lettera di invito</a></li>
<li><a href="/aaa/gare/CIGZB81E5568D+RDO1560277+Obblighi+contattuali-signed.pdf" title="">Obblighi contrattuali</a></li> 
<li><a href="/aaa/gare/CIGZB81E5568D+RDO1560277+disciplinare+di+gara-signed.pdf" title="">Disciplinare di gara</a></li>
<li><a href="/aaa/gare/CIGZB81E5568D+determina+di+aggiudicazione+58+2017.pdf" title="">Determina di aggiudicazione</a></li>

that is a part of the html content of my record.

Now, my problem is that I tried to replace ONLY the '+' in the href tags, so I create in MySQL this script:

set @xxx = replace(
                @xxx,
                substr(@xxx,locate('<a href',@xxx)+3,locate('</a>',@xxx)-locate('<a href',@xxx)),
                replace(
                    substr(
                            @xxx, 
                            locate('<a href',@xxx)+3,
                            locate('</a>',@xxx)-locate('<a href',@xxx)
                        ),
                    '+',
                    ' ')
                );

where @xxx is the string in question. The head is <a href and the tail is </a>, and this never change.

But I obtain this result, with a changing only in the substrings that are the same of the first, because the script search only other substrings with the same central part :

<p><span><b>C10373 - FIAT GROUP AUTOMOBILES/RAMO DI AZIENDA DI KUEHNE + NAGEL</b></span>
<p>la somma pari a € 400+IVA per l’attività</p>
<p>TELE+ A 20.000 LIRE AL MESE </p>
<li>a mano o via fax al numero +39.06.85.82.12.56.</li>
<p>Il punteggio base sarà incrementato di un <strong>+ </strong>al ricorrere di ciascuna delle seguenti condizioni:</p>
<li><a href="/aaa/gare/CIGZB81E5568D RDO1560277 Obblighi contattuali-signed.pdf" title="">Obblighi contrattuali</a></li> 
<li><a href="/aaa/gare/CIGZB81E5568D RDO1560277 Obblighi contattuali-signed.pdf" title="">Obblighi contrattuali</a></li> 
<li><a href="/aaa/gare/CIGZB81E5568D+RDO_1560277Lettera_Invito.pdf" title="">Lettera di invito</a></li>
<li><a href="/aaa/gare/CIGZB81E5568D RDO1560277 Obblighi contattuali-signed.pdf" title="">Obblighi contrattuali</a></li> 
<li><a href="/aaa/gare/CIGZB81E5568D+RDO1560277+disciplinare+di+gara-signed.pdf" title="">Disciplinare di gara</a></li>
<li><a href="/aaa/gare/CIGZB81E5568D+determina+di+aggiudicazione+58+2017.pdf" title="">Determina di aggiudicazione</a></li>

I must replace all the '+' in the href tags WITHOUT replacing the '+' in the other places, like in '€ 400+IVA' or in 'al numero +39.00.0.0.0.00.' .

Thank you for the help in advance, I hope the question is not so cryptic ^^ .

You need an HTML parser. This is really, really hard to do in MySQL alone. — tadman, Mar 29 '18 at 15:56
this better be solved using regex in mysql. this might help[How to do a regular expression replace in MySQL?](https://stackoverflow.com/questions/986826/how-to-do-a-regular-expression-replace-in-mysql) — moghya, Mar 29 '18 at 16:05
it can be done in pure MySQL only without the use of a fullblown HTML parser or a UDF function.. check mine answer. — Raymond Nijland, Mar 29 '18 at 16:51

Raymond Nijland · Accepted Answer · 2018-04-06T14:35:09.887

it's possible in pure MySQL only without the use off a full blown HTML parser or a UDF that would import REGEX_REPLACE function into MySQL.

The trick is by using a number generator and by using a nested SUBSTRING_INDEX to split the large string on the html tags
This number generator will generate number from 1 to 10.000
So the "parser" will support up to 10.000 tags if you need more you need to add more

CROSS JOIN (
     SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9 UNION SELECT 10
    ) AS record_[number]

Query

SELECT 
 (@number := @number + 1) AS number
FROM (    
  SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9 UNION SELECT 10
) AS record_1
CROSS JOIN (
  SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9 UNION SELECT 10
) AS record_2
CROSS JOIN (
 SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9 UNION SELECT 10
) AS record_3
CROSS JOIN (
  SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9 UNION SELECT 10
) AS record_4
CROSS JOIN ( SELECT @number := 0 ) AS init_user_param

this SQL statement will be used to split on html tag

SQL statement

CONCAT(SUBSTRING_INDEX(SUBSTRING_INDEX([large_html_string], ">", [tag_position]), ">", -1), ">") as tag

The trick now is to combine the number generator and the html string splittig. So the [tag_position] is filled with a number from the number generator.
This is done with a CROSS JOIN.

Query

SELECT 
 *
FROM ( 

  SELECT 
    CONCAT(SUBSTRING_INDEX(SUBSTRING_INDEX(data, ">", generator.number), ">", -1), ">") as tag 
  FROM (

    SELECT '<p><span><b>C10373 - FIAT GROUP AUTOMOBILES/RAMO DI AZIENDA DI KUEHNE + NAGEL</b></span>
    <p>la somma pari a € 400+IVA per l’attività</p>
    <p>TELE+ A 20.000 LIRE AL MESE </p>
    <li>a mano o via fax al numero +39.00.0.0.0.00.</li>
    <p>Il punteggio base sarà incrementato di un <strong>+ </strong>al ricorrere di ciascuna delle seguenti condizioni:</p>
    <li><a href="/aaa/gare/CIGZB81E5568D+RDO1560277+Obblighi+contattuali-signed.pdf" title="">Obblighi contrattuali</a></li> 
    <li><a href="/aaa/gare/CIGZB81E5568D+RDO1560277+Obblighi+contattuali-signed.pdf" title="">Obblighi contrattuali</a></li> 
    <li><a href="/aaa/gare/CIGZB81E5568D+RDO_1560277Lettera_Invito.pdf" title="">Lettera di invito</a></li>
    <li><a href="/aaa/gare/CIGZB81E5568D+RDO1560277+Obblighi+contattuali-signed.pdf" title="">Obblighi contrattuali</a></li> 
    <li><a href="/aaa/gare/CIGZB81E5568D+RDO1560277+disciplinare+di+gara-signed.pdf" title="">Disciplinare di gara</a></li>
    <li><a href="/aaa/gare/CIGZB81E5568D+determina+di+aggiudicazione+58+2017.pdf" title="">Determina di aggiudicazione</a></li>' AS data

   ) AS record 
   CROSS JOIN (
     SELECT 
       (@number := @number + 1) AS number
     FROM (    
       SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9 UNION SELECT 10
     ) AS record_1
     CROSS JOIN (
       SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9 UNION SELECT 10
     ) AS record_2
     CROSS JOIN (
       SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9 UNION SELECT 10
     ) AS record_3
     CROSS JOIN (
       SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9 UNION SELECT 10
     ) AS record_4
     CROSS JOIN ( SELECT @number := 0 ) AS init_user_param
   ) 
    AS generator
 ) 
 AS tags
WHERE 
 tags.tag != '>'

see demo http://www.sqlfiddle.com/#!9/de2ed/32

Now we have the tags as separated records it really eazy to replace only with in records that contain "href".

SQL statement

 (
    CASE 
      WHEN 
        LOCATE("href", tags.tag) > 0
      THEN 
        REPLACE(tags.tag, "+", " ") 
      ELSE 
        tags.tag
    END
 ) AS tag

see demo http://www.sqlfiddle.com/#!9/de2ed/38

Now that we know how to replace now we are going to merge the records back to one string. We can use GROUP_CONCAT for that.

Query

    SET SESSION group_concat_max_len = @@max_allowed_packet;

    SELECT 
     GROUP_CONCAT(
        CASE 
          WHEN 
            LOCATE("href", tags.tag) > 0
          THEN 
            REPLACE(tags.tag, "+", " ") 
          ELSE 
            tags.tag
        END
       SEPARATOR ""
     ) AS html

    FROM ( 

      SELECT 
        CONCAT(SUBSTRING_INDEX(SUBSTRING_INDEX(data, ">", generator.number), ">", -1), ">") as tag 
      FROM (

        SELECT '<p><span><b>C10373 - FIAT GROUP AUTOMOBILES/RAMO DI AZIENDA DI KUEHNE + NAGEL</b></span>
        <p>la somma pari a € 400+IVA per l’attività</p>
        <p>TELE+ A 20.000 LIRE AL MESE </p>
        <li>a mano o via fax al numero +39.00.0.0.0.00.</li>
        <p>Il punteggio base sarà incrementato di un <strong>+ </strong>al ricorrere di ciascuna delle seguenti condizioni:</p>
        <li><a href="/aaa/gare/CIGZB81E5568D+RDO1560277+Obblighi+contattuali-signed.pdf" title="">Obblighi contrattuali</a></li> 
        <li><a href="/aaa/gare/CIGZB81E5568D+RDO1560277+Obblighi+contattuali-signed.pdf" title="">Obblighi contrattuali</a></li> 
        <li><a href="/aaa/gare/CIGZB81E5568D+RDO_1560277Lettera_Invito.pdf" title="">Lettera di invito</a></li>
        <li><a href="/aaa/gare/CIGZB81E5568D+RDO1560277+Obblighi+contattuali-signed.pdf" title="">Obblighi contrattuali</a></li> 
        <li><a href="/aaa/gare/CIGZB81E5568D+RDO1560277+disciplinare+di+gara-signed.pdf" title="">Disciplinare di gara</a></li>
        <li><a href="/aaa/gare/CIGZB81E5568D+determina+di+aggiudicazione+58+2017.pdf" title="">Determina di aggiudicazione</a></li>' AS data

       ) AS record 
       CROSS JOIN (
         SELECT 
           (@number := @number + 1) AS number
         FROM (    
           SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9 UNION SELECT 10
         ) AS record_1
         CROSS JOIN (
           SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9 UNION SELECT 10
         ) AS record_2
         CROSS JOIN (
           SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9 UNION SELECT 10
         ) AS record_3
         CROSS JOIN (
           SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9 UNION SELECT 10
         ) AS record_4
         CROSS JOIN ( SELECT @number := 0 ) AS init_user_param
       ) 
        AS generator
     ) 
     AS tags
    WHERE 
     tags.tag != '>'

see demo for the complete query

http://www.sqlfiddle.com/#!9/de2ed/46

Your solution was perfect! Thank you very much, u are a genius ^^ — Fabio Ferrari, Apr 04 '18 at 14:26
@FabioFerrari When reviewing the solution i notice a little error within ( http://www.sqlfiddle.com/#!9/de2ed/40 ) The `GROUP_CONCAT` was missing a `SEPARATOR ""`... so the replaced html contains (,) every where the GROUP_CONCAT has merged the records. This query version is the one to use http://www.sqlfiddle.com/#!9/de2ed/41 — Raymond Nijland, Apr 04 '18 at 20:04
Yeah, I thought about the SEPARATOR for myself, but i also noticed that the returning string is not complete: the procedure arrives at " — Fabio Ferrari, Apr 06 '18 at 14:16
Yes that's because GROUP_CONCAT has a limit you can forgot this very easy @FabioFerrari .. You can increase this limit with `SET SESSION group_concat_max_len = @@max_allowed_packet;` before executing the query see http://www.sqlfiddle.com/#!9/de2ed/46 — Raymond Nijland, Apr 06 '18 at 14:32

MySQL REPLACE : How replace all occurrences of a char in every distinct substring delimited by the same head and tail

1 Answers1

Linked