REGEXP_REPLACE_ONE
Descriptionโ
The REGEXP_REPLACE_ONE
function is a powerful tool designed to perform regular expression matching on a given string. It allows you to find and replace the first occurrence of a specific pattern within the string.
When working with text data, you often need to manipulate strings based on certain rules. Regular expressions provide a flexible and efficient way to define these rules. This function takes a string (str
), a regular expression pattern (pattern
), and a replacement string (repl
). It then searches for the first part of the str
that matches the pattern
and substitutes it with the repl
.
It should be noted that when handling character set matching, Utf-8 standard character classes should be used. This ensures that functions can correctly identify and process various characters from different languages.
If the pattern
is not allowed regexp regular,throw error;
Support character match classes : https://github.com/google/re2/wiki/Syntax
Syntaxโ
REGEXP_REPLACE_ONE(<str>, <pattern>, <repl>)
Parametersโ
Parameter | Description |
---|---|
<str> | This parameter is of type string. It represents the string on which the regular expression matching will be performed. This is the target string that you want to modify. |
<pattern> | This parameter is also of type string. It is a regular expression pattern. The function will search for the first occurrence of this pattern within the |
<repl> | This is a string parameter as well. It contains the string that will replace the first part of |
Return Valueโ
The function returns the result string after the replacement operation. The return type is Varchar. If no part of the
Exampleโ
Replace the first space with a hyphen
Explanation: In this example, the input string
mysql> SELECT regexp_replace_one('a b c', ' ', '-');
+-----------------------------------+
| regexp_replace_one('a b c', ' ', '-') |
+-----------------------------------+
| a-b c |
+-----------------------------------+
Replace the first matched group.Here, the input string
mysql> SELECT regexp_replace_one('a b b', '(b)', '<\\1>');
+----------------------------------------+
| regexp_replace_one('a b b', '(b)', '<\1>') |
+----------------------------------------+
| a <b> b |
+----------------------------------------+
Replace the first Chinese character.The input string
mysql> select regexp_replace_one('่ฟๆฏไธๆฎตไธญๆ This is a passage in English 1234567', '\\p{Han}', '123');
+------------------------------------------------------------------------------------------------+
| regexp_replace_one('่ฟๆฏไธๆฎตไธญๆ This is a passage in English 1234567', '\p{Han}', '123') |
+------------------------------------------------------------------------------------------------+
| 123ๆฏไธๆฎตไธญๆThis is a passage in English 1234567 |
+------------------------------------------------------------------------------------------------+
Insert data into a table and perform replacementFirst, a table named test_table_for_regexp_replace_one is created with four columns: id (an integer), text_data (a string where the replacement will be performed), pattern (the regular expression pattern for matching), and repl (the replacement string).Then, ten rows of data are inserted into the table, each containing different values for the four columns.Finally, a SELECT statement is used to query the table. For each row, the REGEXP_REPLACE_ONE function is applied to the text_data column using the corresponding pattern and repl values. The result of the replacement is aliased as replaced_result. The rows are ordered by the id column.
CREATE TABLE test_table_for_regexp_replace_one (
id INT,
text_data VARCHAR(500),
pattern VARCHAR(100),
repl VARCHAR(100)
) PROPERTIES ("replication_num"="1");
INSERT INTO test_table_for_regexp_replace_one VALUES
(1, 'Hello World', ' ', '-'),
(2, 'apple123', '[0-9]', 'X'),
(3, 'aabbcc', '(aa)', 'AA'),
(4, '123-456-7890', '[0-9][0-9][0-9]', 'XXX'),
(5, 'test,data', ',', ';'),
(6, 'a1b2c3', '[a-z][0-9]', 'X'),
(7, 'book keeper', 'oo', 'OO'),
(8, 'ababab', '(ab)', 'AB'),
(9, 'aabbcc', '(bb)', 'BB'),
(10, 'apple,banana', '[aeiou]', 'X');
SELECT id, regexp_replace_one(text_data, pattern, repl) as replaced_result FROM test_table_for_regexp_replace_one ORDER BY id;
+------+-----------------+
| id | replaced_result |
+------+-----------------+
| 1 | Hello-World |
| 2 | appleX23 |
| 3 | AAbbcc |
| 4 | XXX-456-7890 |
| 5 | test;data |
| 6 | Xb2c3 |
| 7 | BOOk keeper |
| 8 | ABabab |
| 9 | aaBBcc |
| 10 | Xpple,banana |
+------+-----------------+
Emoji one replace case
SELECT regexp_replace_one('๐๐๐', '๐|๐', '[SMILE]');
+------------------------------------------------------------+
| regexp_replace_one('๐๐๐', '๐|๐', '[SMILE]') |
+------------------------------------------------------------+
| [SMILE]๐๐ |
+------------------------------------------------------------+
'str' is NULL,return NULL
mysql> SELECT REGEXP_REPLACE_ONE(NULL, ' ', '-');
+------------------------------------+
| REGEXP_REPLACE_ONE(NULL, ' ', '-') |
+------------------------------------+
| NULL |
+------------------------------------+
'pattern' is NULL,return NULL
mysql> SELECT REGEXP_REPLACE_ONE('Hello World', NULL, '-');
+----------------------------------------------+
| REGEXP_REPLACE_ONE('Hello World', NULL, '-') |
+----------------------------------------------+
| NULL |
+----------------------------------------------+
'repl' is NULL return NULL
mysql> SELECT REGEXP_REPLACE_ONE('Hello World', ' ', NULL);
+----------------------------------------------+
| REGEXP_REPLACE_ONE('Hello World', ' ', NULL) |
+----------------------------------------------+
| NULL |
+----------------------------------------------+
All parameters are NULL,return NULL
mysql> SELECT REGEXP_REPLACE_ONE(NULL, NULL, NULL);
+--------------------------------------+
| REGEXP_REPLACE_ONE(NULL, NULL, NULL) |
+--------------------------------------+
| NULL |
+--------------------------------------+
If the pattern
is not allowed regexp regular,throw error
SELECT regexp_replace_one('a b b', '(b', '<\\1>');
ERROR 1105 (HY000): errCode = 2, detailMessage = (10.16.10.2)[INVALID_ARGUMENT]Could not compile regexp pattern: (b
Error: missing ): (b