mariadb multiline regexp_replace

Question

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.

Answer 1

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

Answer 2

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

Answer 3

This works:

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