Mysql REPLACE or REGEXP_REPLACE , search for one specific expression-CodePudding

I am tyring to solve a problem, concerning mysql REPLACE or REGEXP_REPLACE function.

I stored a lot of text in my db and now I am trying to replace some strings.

For example:

"The parapraph 5.3 contains a lot of words, but 3.5.3.1 has only two words"

5.3 should be replaced with 4.2 and 3.5.3.1 with 2.1 , but I´m always running into the problem, that I´m not only replacing 5.3 at the beginning, but also the 5.3 in 3.5.3.1 .

Does anyone know the exact expression, concerning mysql, for this? When I´m using a codeeditor I get the right result by using \s5.3\s , but that seems not to work that way in mysql.

Thx a lot.

CodePudding user response：

You can do any replacement in the order you want by including spaces in your REPLACE statement:

SET @string = "The parapraph 5.3 contains a lot of words, but 3.5.3.1 has only two words";
SELECT
    REPLACE(@string, ' 5.3 ', ' 4.2 '),
    REPLACE(@string, ' 3.5.3.1 ', ' 2.1 ');

You can nest both REPLACE to get the result you need:

SET @string = "The parapraph 5.3 contains a lot of words, but 3.5.3.1 has only two words";
SELECT
    REPLACE(REPLACE(@string, ' 3.5.3.1 ', ' 2.1 '), ' 5.3 ', ' 4.2 ');

SET @string = "The parapraph 5.3 contains a lot of words, but 3.5.3.1 has only two words";
SELECT
    REPLACE(REPLACE(@string, ' 5.3 ', ' 4.2 '), ' 3.5.3.1 ', ' 2.1 ');

Refer to this fiddle: http://sqlfiddle.com/#!9/9eecb/284883

CodePudding user response：

You need to replace the larger expression first. This does not need regex.

create table test (str varchar(100));
insert into test values ("The parapraph 5.3 contains a lot of words, but 3.5.3.1 has only two words");

SELECT REPLACE(REPLACE(str, '3.5.3.1', '2.1'),'5.3','4.2')
FROM test;
| REPLACE(REPLACE(str, '3.5.3.1', '2.1'),'5.3','4.2')                   |
| :-------------------------------------------------------------------- |
| The parapraph 4.2 contains a lot of words, but 2.1 has only two words |

db<>fiddle here

CodePudding user response：

WITH RECURSIVE
cte1 AS ( SELECT pattern,
                 replacement, 
                 ROW_NUMBER() OVER () rn
          FROM data ),
cte2 AS ( SELECT test.id, 
                 REGEXP_REPLACE(test.txt, 
                                CONCAT('([\\s|^])', cte1.pattern, '([^\\d\.])'),
                                CONCAT('$1\\', cte1.replacement, '$2')
                               ) txt, 
                 cte1.rn
          FROM test
          JOIN cte1 ON cte1.rn = 1
        UNION ALL
          SELECT cte2.id, 
                 REGEXP_REPLACE(cte2.txt, 
                                CONCAT('([\\s|^])', cte1.pattern, '([^\\d\.])'),
                                CONCAT('$1\\', cte1.replacement, '$2')
                               ), 
                 cte1.rn
          FROM cte1
          JOIN cte2 ON cte1.rn = cte2.rn   1 )
SELECT cte2.id, cte2.txt
FROM cte2
WHERE rn = ( SELECT MAX(rn)
             FROM cte1 );

https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=e4560c104e35663e0c36bf475ef66f43

Pay attention to pattern and replacement format.