MySQL Tutorial

This SQL tutorial is structured for beginners to guide them from the foundational concepts to advanced data manipulation and querying techniques in SQL. By the end of this tutorial, you will have developed a robust understanding of SQL, equipping you with the knowledge to efficiently manage and analyze data across various database systems. This guide sets the stage for your growth into a skilled data professional, ready to tackle complex data challenges and contribute to the field of data analysis and database management.

REGEXP_REPLACE() Function
Regular expressions in MySQL are used in search operations to not only filter records but also replace the pattern occurrences in a string.
Consider a scenario where you noticed a spelling error among the huge sets of data present in a MySQL database. Now, you are supposed to correct all occurrences of these errors in this database without disturbing the other data. This is where regular expressions are extremely advantageous.
You can use regular expressions to find the accurate occurrences of the same error and replace it with the right characters. This is done using the regexp_replace() function.
MySQL REGEXP_REPLACE() Function
The MySQL regexp_replace() function is used to find and replace occurrences of a string that match specific patterns. If there's a match, it replaces the string with another. If there's no match, it returns the original string. If the string or pattern is NULL, it returns NULL. You can use a regular expression or a simple string as the pattern in this function.
Syntax
Following is the syntax of the MySQL regexp_replace() function −
REGEXP_REPLACE(expr, pattern, repl[, pos[, occurrence[, match_type]]])
﻿
Parameters
The regexp_replace() function takes following parameter values −
expr: The string in which search is performed
pattern: The pattern that is searched in the string
repl: The replacement string
This method also accepts following optional arguments −
pos − Starting position of the search
occurrence − Which occurrence of a match to replace. If omitted, the default is 0 so it replaces all occurrences.
match_type − A string that specifies how to perform matching.
Example
In the following query, we are performing a search operation on a simple string using the MySQL REGEXP_REPLACE() function −
SELECT REGEXP_REPLACE('Welcome To Tutorialspoint!', 'Welcome', 'Welll')
AS RESULT;
﻿
As we can observe the output below, the string 'Welcome' is found and replaced with 'Welll' −
RESULT
Welll To Tutorialspoint!
But if the pattern is not found in the string, the original string is displayed by the function. Look at the following query −
SELECT REGEXP_REPLACE('Welcome To Tutorialspoint!', 'H', 'Hi') AS RESULT;
﻿
On executing the given query, the output is displayed as follows −
RESULT
Welcome To Tutorialspoint!
Example
Let us also try to pass optional arguments to this function as case-insensitive matching(i). Here, the search starts from the 10th position in the given string; and as we are passing the occurrence value as 1, only the first occurrence of the letter 't' after 10th position will be replaced irrespective of its case −
SELECT REGEXP_REPLACE('Welcome To Tutorialspoint!', 't', 'x', 10, 1, 'i') AS RESULT;
﻿
Output
The output for the program above is produced as given below −
RESULT
Welcome To xutorialspoint!
Example
The following query replaces all the occurrences of the string "is" in the given text −
SELECT REGEXP_REPLACE('This is a sample string', 'is', '@@@@')
As Result;
﻿
Output
On executing the given query, the output is displayed as follows −
RESULT
Th@@@@ @@@@ a sample string
Example
The following query replaces only the first occurrence of the string "This" in the given text with "That" −
SELECT REGEXP_REPLACE('This is a test and This is another test', '^This', 'That')
As Result;
﻿
Output
The output for the query above is produced as given below −
RESULT
That is a test and This is another test
Example
Here, the below query replace the words 'wall' or 'floor' with the word 'bed' in the given string using the MySQL REGEXP_REPLACE() function −
SELECT REGEXP_REPLACE ('Humpty dumpty sat on a wall and slept on the floor', 'wall|floor', 'bed') As Result;
﻿
Output
On executing the given program, the output is displayed as follows −
RESULT
Humpty dumpty sat on a bed and slept on the bed
Example
The following query replaces the first occurrence of the string "eat" with the string "drink" in the provided input string.
In the query, the fourth parameter "1" specifies the position to start the search and the fifth parameter "1" is the number of replacements to be made. Therefore, only the first occurrence of "eat" is replaced with "drink".
SELECT REGEXP_REPLACE('eat sleep repeat and eat', 'eat', 'drink', 1, 1)
As Result;
﻿
Output
Following is the output −
RESULT
drink sleep repeat and eat
Example
If either of the first two arguments passed to this function is NULL, this function returns NULL. Here, we are passing NULL to the string parameter.
SELECT REGEXP_REPLACE(NULL, 'value', 'test') As Result;
﻿
Following is the output −
Result
NULL
If we pass NULL to the pattern parameter, it returns NULL as output.
SELECT REGEXP_REPLACE('Welcome to Tutorialspoint', NULL, 'sample')
As Result;
﻿
The output for the query above is produced as given below −
Result
NULL
If you pass empty string as the replacement string, this function returns NULL.
SELECT REGEXP_REPLACE('Welcome to Tutorialspoint', NULL, '')
As Result;
﻿
On executing the given query, the output is displayed as follows −
Result
NULL
Example
In another example, let us try to perform a search operation on a database table named CUSTOMERS using the REGEXP_REPLACE() function. First of all, let us create the table using the following query −
CREATE TABLE CUSTOMERS (
   ID INT AUTO_INCREMENT,
   NAME VARCHAR(20) NOT NULL,
   AGE INT NOT NULL,
   ADDRESS CHAR (25),
   SALARY DECIMAL (18, 2),
   PRIMARY KEY (ID)
);
﻿
The following query inserts 7 records into the above created table −
INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY) VALUES 
(1, 'Ramesh', 32, 'Ahmedabad', 2000.00 ),
(2, 'Khilan', 25, 'Delhi', 1500.00 ),
(3, 'Kaushik', 23, 'Kota', 2000.00 ),
(4, 'Chaitali', 25, 'Mumbai', 6500.00 ),
(5, 'Hardik', 27, 'Bhopal', 8500.00 ),
(6, 'Komal', 22, 'Hyderabad', 4500.00 ),
(7, 'Muffy', 24, 'Indore', 10000.00 );
﻿
Execute the following SELECT statement to display all the records of CUSTOMERS table −
Select * from CUSTOMERS;
﻿
Following is the CUSTOMERS table −
ID
NAME
AGE
ADDRESS
SALARY
1
Ramesh
32
Ahmedabad
2000.00
2
Khilan
25
Delhi
1500.00
3
Kaushik
23
Kota
2000.00
4
Chaitali
25
Mumbai
6500.00
5
Hardik
27
Bhopal
8500.00
6
Komal
22
Hyderabad
4500.00
7
Muffy
24
Indore
10000.00
The following query uses the REGEXP_REPLACE() function to update the NAME column in the person_tbl table. It looks for names that start with the letter 'A' and replaces that 'A' with 'An'.
SELECT REGEXP_REPLACE(NAME, '^A', 'An') AS Result FROM CUSTOMERS;
﻿
On executing the given query, the output is displayed as follows −
Result
Ramesh
Khilan
Kaushik
Chaitali
Hardik
Komal
Muffy
But if the pattern is not found in any record of the table, the original values of the table are displayed by the function. Look at the following query −
SELECT REGEXP_REPLACE(ADDRESS, '^Z', 'P') AS RESULT FROM CUSTOMERS;
﻿
There is no record in ADDRESS column that starts with letter 'Z'. So, it returned the original records as output −
Result
Ahmedabad
Delhi
Kota
Mumbai
Bhopal
Hyderabad
Indore
The following query is using the REGEXP_REPLACE function to replace the second occurrence of the letter 'r' with 'R' in the ADDRESS column of the CUSTOMERS table −
SELECT REGEXP_REPLACE(ADDRESS, 'r', 'R', 2, 0, 'c') 
AS RESULT FROM CUSTOMERS;
﻿
As we can see in the output, the records 'Hyderabad' and 'Indore' has letter 'r' in it. And they are replaced by 'R' −
Result
Ahmedabad
Delhi
Kota
Mumbai
Bhopal
HydeRabad
IndoRe
REGEXP_REPLACE() Funcion Using a Client Program
We can also perform the MySQL REGEXP_REPLACE function using the client programs to find and replace occurrences of a string that match specific patterns.
Syntax
Following are the syntaxes of this operation in various programming languages −
PHPNodeJSJavaPython
To match with specific pattern and replace with another string using MySQL Query through PHP program, we need to execute the 'SELECT' statement using the mysqli function query() as follows −
$sql = "SELECT REGEXP_REPLACE('Welcome To Tutorialspoint!', 'Welcome', 'Welcom')";
$mysqli->query($sql);
﻿
Example
Following are the programs −
PHPNodeJSJavaPython
$dbhost = 'localhost';
$dbuser = 'root';
$dbpass = 'password';
$db = 'TUTORIALS';
$mysqli = new mysqli($dbhost, $dbuser, $dbpass, $db);
if ($mysqli->connect_errno) {
    printf("Connect failed: %s", $mysqli->connect_error);
    exit();
}
//printf('Connected successfully.');
$sql = "SELECT REGEXP_REPLACE('Welcome To Tutorialspoint!', 'Welcome', 'Welcom') AS RESULT";
if($result = $mysqli->query($sql)){
    while($row = mysqli_fetch_array($result)){
        printf("Result: %s", $row['RESULT']);
    }
}
if($mysqli->error){
    printf("Error message: ", $mysqli->error);
}
$mysqli->close();
﻿
Output
The output obtained is as shown below −
Result: Welcom To Tutorialspoint!
﻿
﻿
﻿
﻿
﻿
﻿
﻿

ID	NAME	AGE	ADDRESS	SALARY
1	Ramesh	32	Ahmedabad	2000.00
2	Khilan	25	Delhi	1500.00
3	Kaushik	23	Kota	2000.00
4	Chaitali	25	Mumbai	6500.00
5	Hardik	27	Bhopal	8500.00
6	Komal	22	Hyderabad	4500.00
7	Muffy	24	Indore	10000.00

MySQL Basics

MySQL Databases

MySQL Users

MySQL Tables

MySQL Queries

MySQL Views

MySQL Indexes

MySQL Operators and Clauses

MySQL Joins

MySQL Keys

MySQL Triggers

MySQL Data Types

MySQL Regular Expressions

MySQL Fulltext Search

MySQL Functions & Operators

MySQL Misc Concepts

MySQL Useful Resources

MySQL Tutorial

REGEXP_REPLACE() Function

MySQL REGEXP_REPLACE() Function

Syntax

Parameters

Example

Example

Output

Example

Output

Example

Output

Example

Output

Example

Output

Example

Example

REGEXP_REPLACE() Funcion Using a Client Program

Syntax

Example

Output