MySQL REGEXP_REPLACE()

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. This function is rarely used but has a good impact when used. Sub-string can be replaced as a whole, at a specified position, or in an array. In this article, we will discuss MySQL REGEXP_REPLACE() in detail and also, we can discuss in detail about the syntax and the use in the following portions.

Syntax

Simplest syntax for REGEXP_REPLACE() function is as follows:

REGEXP_REPLACE(exp, pat, repl);

Here, exp is the string to be searched upon, pat is the regular expression searched for, and repl is the sub-string which will be replaced.

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, repl are optional arguments. Pos stands for the position in the string where the search is to be performed. This can be omitted in the query, which will lead the search to start at the first character. Occurrence specifies which occurrence of the expression is to be replaced. This argument can also be omitted and instead, all occurrences will be replaced. Match_type specifies how the matching is to be performed.

We can now take a detailed look at the practical examples of REGEXP_REPLACE() operator.

How REGEXP_REPLACE() works in MySQL?

Consider the string as below:

set@original ='I am robot. I can read, write and process.';
select @original;

The string is having 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 is expected to search the string to find the sub-string ‘robot’, replace it by sub-string ‘Human’ and then return the updated string. The regular expression is to be searched within the ‘string’. The expis the ‘string’, the pattern to be searched, pat, is the sub-string ‘robot’, and the replacing sub-string (rep) will be ‘Human;.

In the output, we can see, the sub-string ‘robot’ is replaced as ‘Human; and updated string that is returned by the SELECT query is ‘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 in our query, we have mentioned only once, upper case ‘I’ appears twice in the string.

Output:

We can see, in the output that both the upper case ‘I ‘are replaced with lower case ‘i’.

Query 3

The replacing function will return a null value if the sub-string (expression) is not present in the string. 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 with 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);

Query is to return the string updated as from the second position of sub-string ‘Table’ replaced by ‘*****’.

Output:

We had 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

In the above query, all occurrences of the specified sub-strings, from a particular position, were replaced. Instead, let us see how we can replace only one occurrence of sub-string ‘Table’ from the original string.

SELECT@original,REGEXP_REPLACE(@original , 'Table', '*****', 1, 2);

The query is expected to return the string with only the second occurrence of sub-string ‘Table’ replaced by ‘*****’. Here the sub-strings are to be counted from the first position.

Output:

We can see, among the three occurrences of ‘Table’ sub-string, only the second one (when counted from first one) is replaced.

The same query can give a different output if we change the position of occurrence count.

SELECT@original,REGEXP_REPLACE(@original , 'Table', '*****', 2, 2);

The Output will be updating 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 be updating 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 pattern to be searched in this query is ‘table’, with all lower case characters. The query is expected to return three cases:

1. The original string with three occurrences of the sub-string ‘table’.
2. A case sensitive result where the second occurrence of sub-string ‘table’ to be replaced by ‘*****’. This will not replace the sub-string, because the original string has ‘Table’ sub-string with an upper case ‘T’.
3. A case insensitive result where the second occurrence of sub-string ‘table’ to be replaced by ‘*****’. This portion of string 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()

In this chapter, we have discussed different options of using REGEXP_REPLACE() function. The function, as discussed replaces the regular expression with the sub-string specified in the SELECT query.

Recommended Articles

This is a guide to MySQL REGEXP_REPLACE(). Here we discuss MySQL REGEXP_REPLACE() along with appropriate syntax and respective examples. You may also have a look at the following articles to learn more –

1. MySQL Subquery
2. ANY in MySQL
3. MySQL Constraints
4. ROLLUP in MySQL