2

I'm trying to replace multiple lines using the mariadb regex_replace function.

CREATE TABLE `test` (
  `entry` text NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `test` (`entry`) VALUES ('LINE1\r\nLINE2\r\nLINE3\r\nLINE4\r\nLINE5'); 

This creates one entry which the following content:

LINE1
LINE2
LINE3
LINE4
LINE5

Let's say I want to replace LINE2 through LINE4 with ''. This should be the result:

LINE1
LINE5

I've tried so many different regexes, but I'm out of ideas, as none of these work (no lines are removed).

select regexp_replace(entry, 'LINE2.*LINE4', '') from test 
select regexp_replace(entry, 'LINE2[.\r\n]*LINE4', '') from test 
select regexp_replace(entry, 'LINE2[.\R]*LINE4', '') from test 
select regexp_replace(entry, '(*CRLF)LINE2.*LINE4', '') from test 
select regexp_replace(entry, '(*ANYCRLF)LINE2.*LINE4', '') from test 

I've also tried selecting using RLIKE, but it also does not work (no entries returned):

select entry from test where entry RLIKE 'LINE1.*LINE4' 
select entry from test where entry RLIKE '(*ANYCRLF)LINE1.*LINE5'

It should be possible, so I don't really know why this isn't working.

I'm using latest mariadb on debian (10.3.22).

To make sure this has nothing to do with the switch to PCRE2 in 10.5.1., I also check the historical mariadb pcre docs, but the syntax did not change.

2
  • 1
    can you add how the end result should look like. your usuage is quite confusing. Aslo you should use lore ipsum text lines maybe it could clear also up somethings.
    – nbk
    May 1, 2020 at 0:47
  • @nbk I've added the end result.
    – Zulakis
    May 1, 2020 at 12:16

3 Answers 3

2

This can be solved with the dotall-modifier (?s):

 select regexp_replace(entry, '(?s)LINE2.*LINE4\r\n', '') from test

More modifiers can be found here: https://mariadb.com/kb/en/pcre/#option-setting

1
  • That looks good. You can also do this variant: SET default_regex_flags='DOTALL'; SELECT regexp_replace(entry, 'LINE2.*LINE4\r\n', '') FROM test;
    – dbdemon
    May 1, 2020 at 17:13
0

regexp_replace doesn't work for that purpose.

I tried almost everyting here https://mariadb.com/kb/en/pcre/

and i test the logical regular expression

(LINE1)(.*)(\r\nLINE5)

at https://regex101.com/

But Mariadb didn't accept it, no error simply couldn't find a match, neither with 103 mot 10.4

But for your purpose this REPLACE is enough

select REPLACE(entry, 'LINE2\r\nLINE3\r\nLINE4\r\n', '') from test 

see https://dbfiddle.uk/?rdbms=mariadb_10.3&fiddle=add220e8df6a31d35717a8392d51ae34

0

This works:

SELECT regexp_replace(entry, 'LINE2((.*)(\\R))*LINE4(.*)(\\R)', '') FROM test;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.