fbpx

Batch replacing of multiple exact name in a single query with mysql REGEXP

Batch replacing of multiple exact name in a single query with mysql REGEXP

MySQL allows the following regular expression metacharacters:
. match any character ? match zero or one
* match zero or more
+ match one or more
{n} match n times
{m,n} match m through n times
{n,} match n or more times
^ beginning of line
$ end of line
[[:<:]] match beginning of words
[[:>:]] match ending of words
[:class:] match a character class
i.e., [:alpha:] for letters
[:space:] for whitespace
[:punct:] for punctuation
[:upper:] for upper case letters
[abc] match one of enclosed chars
[^xyz] match any char not enclosed
| separates alternatives

Example of batch replacing of multiple exact name in a single query with mysql REGEXP
UPDATE
table_name
SET
column_name =
CASE
WHEN
column REGEXP '[[:<:]]WORD_TO_REPLACE[[:>:]]'
THEN
REPLACE(column,'WORD_TO_REPLACE','REPLACEMENT')
END
WHERE
column REGEXP '[[:<:]]WORD_TO_REPLACE[[:>:]]'

 

Share this post