MySQL REPLACE:如何替换由相同的头和尾分隔的每个不同子字符串中所有出现的char [英] MySQL REPLACE : How replace all occurrences of a char in every distinct substring delimited by the same head and tail

查看：84
本文介绍了MySQL REPLACE:如何替换由相同的头和尾分隔的每个不同子字符串中所有出现的char的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

我有这个字符串:

<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>

那是我记录的html内容的一部分.

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

现在，我的问题是我只尝试替换href标记中的'+'，所以我在MySQL中创建了以下脚本:

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)
                        ),
                    '+',
                    ' ')
                );

其中@xxx是所讨论的字符串. 头是<a href，头是</a>，而且永远不变.

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>

我必须替换href标记中的所有"+"，而不能替换其他位置的"+"，例如€400 + IVA"或"al numero +39.00.0.0.0.00".

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 ^^ .

推荐答案

只有在纯MySQL中，才可能不使用完整的HTML解析器或将REGEX_REPLACE函数导入MySQL的UDF.

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.

诀窍是使用数字生成器并使用嵌套的SUBSTRING_INDEX来拆分html标签上的大字符串
此数字生成器将生成1到10.000之间的数字
因此，解析器"将最多支持10.000个标签，如果您需要更多标签，则需要添加更多

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]

查询 

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

此SQL语句将用于分割html标记

this SQL statement will be used to split on html tag

SQL语句 

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

现在的技巧是将数字生成器和html字符串splittig结合在一起.因此[tag_position]中填充了数字生成器中的一个数字.
这是通过CROSS JOIN完成的.

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.

查询 

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 != '>'

请参阅演示 http://www.sqlfiddle.com/#!9/de2ed /32

现在，我们将标签作为单独的记录，它真的很容易只替换为包含"href"的记录.

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

SQL语句

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

请参阅演示 http://www.sqlfiddle.com/#!9/de2ed /38

现在我们知道如何替换了，我们将合并记录到一个字符串中.我们可以使用GROUP_CONCAT.

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.

查询 

    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

这篇关于MySQL REPLACE:如何替换由相同的头和尾分隔的每个不同子字符串中所有出现的char的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！

查看全文
相关文章
数据库最新文章
热门教程
热门工具
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆