MySQL REGEXP_REPLACE() function

160 Uncategorized Leave a Comment

Notice: Corrently, REGEXP_REPLACE() do not support \s.

Here is an example of using MySQL 8+ regexp_replace():

> select regexp_replace('<div>Hello <b>world</b></div>', '<b>.*?</b>', 'MySQL', 1, 0, 'in') as result;
+------------------------+
| result                 |
+------------------------+
| <div>Hello MySQL</div> |
+------------------------+

Above example correspond to this syntax:

REGEXP_REPLACE(expr, pat, repl[, pos[, occurrence[, match_type]]])
  • expr: is the string to be replaced
  • pat: is regex expression to search
  • repl: is what we want to replace with
  • pos: from position, default is 1
  • occurrence: replace times. default is 0, unlimited
  • match_type: regex flag.
    • c: Case-sensitive matching
    • i: Case-insensitive matching
    • m: Multiple-line mode. Recognize line terminators within the string. The default behavior is to match line terminators only at the start and end of the string expression.
    • n: The . character matches line terminators. The default is for . matching to stop at the end of a line.
    • u: Unix-only line endings. Only the newline character is recognized as a line ending by the ., ^, and $ match operators.

To replace a column value with REGEXP_REPLACE():

> update `name` = regexp_replace(`name`, '<b>.*?</b>', 'MySQL', 1, 0, 'in') where id = 10;

References:

  1. 12.8.2 Regular Expressions
  2. How to do a regular expression replace in MySQL?

Leave a Reply

Your email address will not be published.

Name *