Updated May 16, 2023
Definition of MySQL REGEXP_REPLACE()
REGEXP_REPLACE() operator is used in the SELECT query to replace the matched sub-string. This operator searches for the regular expression, identifies it, replaces the pattern with the sub-string provided explicitly in the query, and returns the output with the updated sub-string. Users rarely use this function, but it significantly impacts when utilized. You can replace the sub-string as a whole, at a specified position, or within an array. In this article, we will discuss MySQL REGEXP_REPLACE() in detail, and also, we can discuss in detail the syntax and the use in the following portions.
Syntax
Simplest syntax for REGEXP_REPLACE() function is as follows:
REGEXP_REPLACE(exp, pat, repl);The program searches for the regular expression “pat” in the string “exp” and replaces it with the sub-string “a reply.”
When used in a SELECT query, the query can be as below:
SELECT REGEXP_REPLACE(exp, pat, repl);A further detailed syntax for REGEXP_REPLACE() is as follows:
REGEXP_REPLACE(exp, pat, repl [, pos[, occurrence[, match_type]]]);In this, the pos, pat, and repl are optional arguments. Pos is the position in the string where the search will be performed. The query can omit this, causing the search to start at the first character. The specific occurrence of the expression that should be replaced is specified by the ‘occurrence’ parameter. If omitted, it will result in the replacement of all occurrences of the expression. The ‘match_type’ parameter determines the method to be used for matching.
We can now look at the practical examples of the REGEXP_REPLACE() operator.
How does REGEXP_REPLACE() work in MySQL?
Consider the string below:
set@original ='I am robot. I can read, write and process.';
select @original;
The string has data as ‘I am robot. I can read, write, and process.’. This is our test string, where we will work on the different REPLACE() operations.
Query 1
SELECT @original, REGEXP_REPLACE(@original , 'robot', 'Human');Output:
The query aims to search the string for the sub-string ‘robot’, replace it with ‘Human’, and return the updated string. The ‘string’ will be searched using a regular expression. In this case, the variable ‘exp’ represents the string to be searched, ‘pat’ represents the pattern to be searched (sub-string ‘robot’), and ‘rep’ represents the replacing sub-string ‘Human’.
In the output, the sub-string ‘robot’ is replaced with ‘Human’, and the SELECT query returns the updated string as ‘I am Human’. I can read, write, and process.’
Query 2
Let’s now write the query to replace multiple occurrences of a sub-string with the same replacing expression.
SELECT @original, REGEXP_REPLACE(@original , 'I', 'i');The expected output is to replace all upper case ‘I’ to lower case ‘i’ in the string. Though we have mentioned this only once in our query, the upper case ‘I’ appears twice in the string.
Output:
The output demonstrates that the upper case ‘I’ is replaced with a lower case ‘i’.
Query 3
The replacing function will return a null value if the string’s sub-string (expression) is absent. The query to validate that scenario will be as follows:
SELECT @original, REGEXP_REPLACE(@original , 'and', 'also');Our string does not have the sub-string ‘also.’ So the output should not be affected by the replacement clause. Instead, it should be the same as the input.
Output:
We discussed the optional arguments of REPLACE() function. Let’s see how to use them in practical scenarios. Let’s consider the original string to be as below:
set @original ='Table Chair Light Table Switch Fan Table';Query 4
We can see the use of position argument.
SELECT @original, REGEXP_REPLACE(@original , 'Table', '*****', 2);The query is to return the string updated from the second position of the sub-string ‘Table’ replaced by ‘*****’.
Output:
We had the sub-string ‘Table’ three times in the original string. The query returned the first sub-string of ‘Table’ as is and replaced the second and third sub-strings as ‘*****’.
Query 5
The above query replaced all occurrences of the specified sub-strings from a particular position. Instead, let us see how to replace only one sub-string ‘Table’ event from the original string.
SELECT@original,REGEXP_REPLACE(@original , 'Table', '*****', 1, 2);The expected query result is the string with only the second occurrence of the sub-string ‘Table’ replaced by ‘*****’. The counting of sub-strings starts from the first position.
Output:
Among the three occurrences of the ‘Table’ sub-string, we can see that only the second one (when counted from the first one) is replaced.
The same query can give a different output if we change the position of the occurrence count.
SELECT@original,REGEXP_REPLACE(@original , 'Table', '*****', 2, 2);
The output will update the second occurrence of ‘Table’ from the second position. Or change the occurrence count as below:
SELECT@original,REGEXP_REPLACE(@original , 'Table', '*****', 1, 1);The output will update the first occurrence of ‘Table’ from the first position.
Finally, let’s explore the match_type argument. There are several characters in this argument. They are
- ‘c’ – this will enable a case-sensitive matching
- ‘i’ – this will enable a case-insensitive matching
- ‘m’ – this will identify where the line is terminated
- ‘ n’ – this will identify the line terminators ‘.’.
Query 6
SELECT@original 'Actual_String',
REGEXP_REPLACE(@original, 'table', '*****', 1, 2, 'c') 'Case_Sensitive_Result',
REGEXP_REPLACE(@original, 'table', '*****', 1, 2, 'i') 'Case_Insensitive_Result';The expected query result includes three cases where the pattern to be searched is ‘table’ in all lowercase characters.
- The original string with three occurrences of the sub-string ‘table’.
- To obtain a case-sensitive result, you should replace the second occurrence of the sub-string ‘table’ with ‘*****’. This will not replace the sub-string because the original string has a ‘Table’ sub-string with an upper case ‘T’.
- To achieve a case-insensitive result, the second occurrence of the sub-string ‘table’ should be replaced by ‘*****’. This string portion will update the sub-string ‘table’ with ‘*****’.
Output:
The output will have the case insensitive result field with ‘table’ replaced by ‘*****’.
Conclusion – MySQL REGEXP_REPLACE()
This chapter has discussed different options for using REGEXP_REPLACE() function. The function, as discussed, replaces the regular expression with the sub-string specified in the SELECT query.
Recommended Articles
We hope that this EDUCBA information on “MySQL REGEXP_REPLACE()” was beneficial to you. You can view EDUCBA’s recommended articles for more information.
