SQL: Regexp_replace but only the first time a value appears in record

Question

I am coding an plugin for harbiforum. I have post content in the message column.

I want to search and replace pattern with X but only the first time pattern appears in the record as it may appear more than once.

The below query would obviously replace all instances of pattern with X

UPDATE xf_post SET message = REGEXP_REPLACE (message, 'pattern', 'X');

It's very easy with this command on oracle db.

UPDATE xf_post SET message = REGEXP_REPLACE (message , 'pattern', 'X', 1, 1);

But the server has mariadb 😟 I don't know how to do it.

Answer 1

An example of the REGEXP_REPLACE emulation with 1st occurence replacement only.

SELECT txt,
       CONCAT(LEFT(txt, LOCATE(REGEXP_SUBSTR(txt,'pattern'), txt) - 1),
              REGEXP_REPLACE(REGEXP_SUBSTR(txt,'pattern'), 'pattern', 'replacement'),
              SUBSTRING(txt FROM LOCATE(REGEXP_SUBSTR(txt,'pattern'), txt) + LENGTH(REGEXP_SUBSTR(txt,'pattern')))) output
FROM test

fiddle with the explanations