The REGEXP_REPLACE SQL Function in MySQL and MariaDB - Replace regular expression |
|
| | REGEXP_REPLACE | Syntax: | REGEXP_REPLACE(String, Pattern, Replace by [, Position] [, Occurrence] [, Match type]) | | Return value: | CHAR | | Function type: | Regular Expressions | |
| | The REGEXP_REPLACE() function returns a "String" with all occurrences of the regular expression "Pattern" replaced with the string "Replace with".
If no occurrences are found, the "String" is returned unchanged.
The string "Replace with" can have back references to the subexpressions of the form \N where N is a number from 1 to 9. Only works with MariaDB.
The function respects the case-sensitive rules of the effective sorting.
If the value of "String", "Pattern", or "Replace with" is NULL, the REGEXP_REPLACE() function returns NULL.
In MySQL, REGEXP_INSTR() accepts the following optional arguments: These options are not available in MariaDB.
"Position": The position in "String" at which to start the search. The default is 1 if not specified.
"Occurrence": Which occurrence of a match to replace. If not specified or 0, all occurrences are replaced.
"Match type": A string specifying how the match is performed:
| Type | Description |
| c | Case sensitive. |
| i | Case-insensitive match. |
| m | Multiline mode. Line terminators within the string are recognized. The default behavior is to match line terminators only at the beginning and end of "String". |
| n | A point "." is interpreted as a line terminator: Default. |
| u | Unix-only line endings. Only the newline character is recognized as a newline by the ".", ",", "^" and "$" match operators. |
If characters are specified within "Match type" that result in conflicting options, the character on the right takes precedence. | Examples of the REGEXP_REPLACE() function on MariaDB 10.3 |
1
2
3
4
5
6
7
8
9
10
11
12
13
|
| SELECT REGEXP_REPLACE('test-abc-abc-abc', 'Test', 'xxxx');
SELECT REGEXP_REPLACE(cast('test-abc-abc-abc' as char character set utf8mb4), cast('Test' as char character set utf8mb4), 'xxxx');
SELECT REGEXP_REPLACE('test-abc-ABC-aBc', '(?i)abc', 'xxx');
SELECT REGEXP_REPLACE('test-abc-ABC-aBc', '(?-i)abc', 'xxx');
SELECT REGEXP_REPLACE('Test Abc','^(.*) (.*)$','\2 = \1');
SELECT REGEXP_REPLACE(null, 'abc', 'xxx');
SELECT REGEXP_REPLACE('test-aa-abc', null, 'xxx');
SELECT REGEXP_REPLACE('test-aa-abc', 'abc', null);
|
|
| REGEXP_REPLACE('test-abc-abc-abc', 'Test', 'xxxx') |
|---|
|
|---|
| test-abc-abc-abc |
|
|
| REGEXP_REPLACE(cast('test-abc-abc-abc' as char character set utf8mb4), cast('Test' as char character set utf8mb4), 'xxxx') |
|---|
|
|---|
| xxxx-abc-abc-abc |
|
|
| REGEXP_REPLACE('test-abc-ABC-aBc', '(?i)abc', 'xxx') |
|---|
|
|---|
| test-xxx-xxx-xxx |
|
|
| REGEXP_REPLACE('test-abc-ABC-aBc', '(?-i)abc', 'xxx') |
|---|
|
|---|
| test-xxx-ABC-aBc |
|
|
| REGEXP_REPLACE('Test Abc','^(.*) (.*)$','\2 = \1') |
|---|
|
|---|
| Abc = Test |
|
|
| REGEXP_REPLACE(null, 'abc', 'xxx') |
|---|
|
|---|
| NULL |
|
|
| REGEXP_REPLACE('test-aa-abc', null, 'xxx') |
|---|
|
|---|
| NULL |
|
|
| REGEXP_REPLACE('test-aa-abc', 'abc', null) |
|---|
|
|---|
| NULL |
|
|
| Examples of the REGEXP_REPLACE() function on MySQL 8.0 |
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
|
| SELECT REGEXP_REPLACE('test-abc-abc-abc', 'Test', 'xxxx');
SELECT REGEXP_REPLACE(cast('test-abc-abc-abc' as char character set utf8mb4), cast('Test' as char character set utf8mb4), cast('xxxx' as char character set utf8mb4));
SELECT REGEXP_REPLACE('test-abc-ABC-aBc', '(?i)abc', 'xxx', 7);
SELECT REGEXP_REPLACE('test-abc-ABC-aBc', '(?-i)abc', 'xxx', 1, 2);
SELECT REGEXP_REPLACE('test-abc-ABC-aBc', 'abc', 'xxx', 1, 3, 'i');
SELECT REGEXP_REPLACE('test-abc-ABC-aBc', 'abc', 'xxx', 1, 0, 'i');
SELECT REGEXP_REPLACE('Test Abc','^(.*) (.*)$','\2 = \1');
SELECT REGEXP_REPLACE(null, 'abc', 'xxx');
SELECT REGEXP_REPLACE('test-aa-abc', null, 'xxx');
SELECT REGEXP_REPLACE('test-aa-abc', 'abc', null);
|
|
| REGEXP_REPLACE('test-abc-abc-abc', 'Test', 'xxxx') |
|---|
|
|---|
| test-abc-abc-abc |
|
|
| REGEXP_REPLACE(cast('test-abc-abc-abc' as char character set utf8mb4), cast('Test' as char character set utf8mb4), cast('xxxx' as char character set utf8mb4)) |
|---|
|
|---|
| xxxx-abc-abc-abc |
|
|
| REGEXP_REPLACE('test-abc-ABC-aBc', '(?i)abc', 'xxx', 7) |
|---|
|
|---|
| test-abc-xxx-xxx |
|
|
| REGEXP_REPLACE('test-abc-ABC-aBc', '(?-i)abc', 'xxx', 1, 2) |
|---|
|
|---|
| test-abc-ABC-aBc |
|
|
| REGEXP_REPLACE('test-abc-ABC-aBc', 'abc', 'xxx', 1, 3, 'i') |
|---|
|
|---|
| test-abc-ABC-xxx |
|
|
| REGEXP_REPLACE('test-abc-ABC-aBc', 'abc', 'xxx', 1, 0, 'i') |
|---|
|
|---|
| test-xxx-xxx-xxx |
|
|
| REGEXP_REPLACE('Test Abc','^(.*) (.*)$','\2 = \1') |
|---|
|
|---|
| 2 = 1 |
|
|
| REGEXP_REPLACE(null, 'abc', 'xxx') |
|---|
|
|---|
| NULL |
|
|
| REGEXP_REPLACE('test-aa-abc', null, 'xxx') |
|---|
|
|---|
| NULL |
|
|
| REGEXP_REPLACE('test-aa-abc', 'abc', null) |
|---|
|
|---|
| NULL |
|
|
| | The examples were created with the MyWAY SQL manager: | How to use the REGEXP_REPLACE() function in MySQL and MariaDB databases | | In MySQL and MariaDB, the REGEXP_REPLACE() function is used to find occurrences of a pattern within a string using regular expressions to replace. It can thus look for patterns in a string and replace them with a specified replacement string, and can be useful for tasks such as data cleaning, text manipulation, and data transformation. | | Further MySQL und MariaDB SQL Regular Expressions | | REGEXP | String [NOT] REGEXP Pattern | | More about REGEXP Function |
| | REGEXP_INSTR | REGEXP_INSTR(String, Pattern [, Position] [, Occurrence] [, Return option] [, Match type]) | | More about REGEXP_INSTR Function |
| | REGEXP_LIKE | REGEXP_LIKE(String, Pattern [, Match type]) | | More about REGEXP_LIKE Function |
| | REGEXP_SUBSTR | REGEXP_SUBSTR(String, Pattern [, Position] [, Occurrence] [, Match type]) | | More about REGEXP_SUBSTR Function |
| | RLIKE | String [NOT] RLIKE Pattern | | More about RLIKE Function |
|
|
| | | More information about the REGEXP_REPLACE SQL function: and |
|
|
|
|
