CODEWITHSUNDEEP
mysqlregexreplacemariadb
shivank01
shivank01

Reputation: 1065

replacing regular expression not working in mysql and mariadb

I want to replace the string in mysql using regular expression. For this, I am using REGEXP_REPLACE but it is not giving me the desired result.

I am trying to replace &breakUp=Mumbai;city,Puma;brand& to &breakUp=Mumbai;city,Puma;brand,Delhi;State& but regexp replace not giving me the desired result.

I am using the following sql query:

SELECT REGEXP_REPLACE('&breakUp=Mumbai;city,Puma;brand&','&breakUp=([\w;,]*)&','&breakUp=$1,Delhi;State&');

But it is giving me the following result:

&breakUp=Mumbai;city,Puma;brand&

The same regular expressions are working fine in other places except sql. How to resolve this in mysql and mariadb?

Upvotes: 0

Views: 744

Answers (1)

Sebastian Brosch
Sebastian Brosch

Reputation: 43574

The \w is not valid. You can use [[:alnum:]] or [[:alpha:]] instead of \w:

Solution on MySQL:

SELECT REGEXP_REPLACE('&breakUp=Mumbai;city,Puma;brand&','&breakUp=([[[:alnum:]];,]*)&','&breakUp=$1,Delhi;State&');

demo on dbfiddle.uk

Solution on MariaDB:

SELECT REGEXP_REPLACE('&breakUp=Mumbai;city,Puma;brand&','&breakUp=([[:alnum:];,]*)&','&breakUp=\\1,Delhi;State&');

demo on dbfiddle.uk

Upvotes: 2

Related Questions