Using REGEXP_REPLACE() in MySQL 8.0
MySQL 8.0 introduced the REGEXP_REPLACE() function, which allows you to search for text using a regular expression and replace the matched portion with a new string. Unlike REPLACE(), which works on fixed text, REGEXP_REPLACE() supports powerful ICU-based regex patterns.
Regex-based Matching: You can replace dynamic patterns using full ICU regex capabilities (e.g.,
\d, lookaheads, Unicode classes).Supports Capture Groups: Use parentheses
()to capture parts of the match and refer to them using\\1,\\2, etc.Full Unicode Support: ICU allows matching and replacing accented characters, symbols, and multilingual text.
Optional Match Parameters: You can specify position, occurrence number, and matching flags.
REGEXP_REPLACE() is extremely useful for cleaning data, normalizing formats, and transforming text using pattern-based rules.