MySQL – 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 −
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);
To match with specific pattern and replace with another string using MySQL Query through Node.js program, we need to execute the ”SELECT” statement using the query() function of the mysql2 library as follows −
sql = "SELECT REGEXP_REPLACE(''Welcome To Tutorialspoint!'', ''Welcome'', ''Welcom'')"; con.query(sql);
To match with specific pattern and replace with another string using MySQL Query through Java program, we need to execute the ”SELECT” statement using the JDBC function executeUpdate() as follows −
String sql = "SELECT REGEXP_REPLACE(''Welcome To Tutorialspoint!'', ''Welcome'', ''Welcom'')"; statement.executeQuery(sql);
To match with specific pattern and replace with another string using MySQL Query through Python program, we need to execute the ”SELECT” statement using the execute() function of the MySQL Connector/Python as follows −
sql = "SELECT REGEXP_REPLACE(''Welcome To Tutorialspoint!'', ''Welcome'', ''Welcom'')" cursorObj.execute(sql)
Example
Following are the programs −
$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!
var mysql = require(''mysql2''); var con = mysql.createConnection({ host:"localhost", user:"root", password:"password" }); //Connecting to MySQL con.connect(function(err) { if (err) throw err; //console.log("Connected successfully...!"); //console.log("--------------------------"); sql = "USE TUTORIALS"; con.query(sql); sql = "SELECT REGEXP_REPLACE(''Welcome To Tutorialspoint!'', ''Welcome'', ''Welcom'') AS RESULT"; console.log("Select query executed successfully..!"); console.log("Table records: "); con.query(sql); con.query(sql, function(err, result){ if (err) throw err; console.log(result); }); });
Output
The output obtained is as shown below −
Select query executed successfully..! Table records: [ { RESULT: ''Welcome To Tutorialspoint!'' } ]
import java.sql.Connection; import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.Statement; public class regexp_replace { public static void main(String[] args) { String url = "jdbc:mysql://localhost:3306/TUTORIALS"; String user = "root"; String password = "password"; ResultSet rs; try { Class.forName("com.mysql.cj.jdbc.Driver"); Connection con = DriverManager.getConnection(url, user, password); Statement st = con.createStatement(); //System.out.println("Database connected successfully...!"); String sql = "SELECT REGEXP_REPLACE(''Welcome To Tutorialspoint!'', ''Welcome'', ''Welcom'') AS RESULT"; rs = st.executeQuery(sql); while(rs.next()) { String result = rs.getString("RESULT"); System.out.println("Result: " + result); } }catch(Exception e) { e.printStackTrace(); } } }
Output
The output obtained is as shown below −
Result: Welcom To Tutorialspoint!
import mysql.connector # Establishing the connection connection = mysql.connector.connect( host=''localhost'', user=''root'', password=''password'', database=''tut'' ) # Creating a cursor object cursorObj = connection.cursor() regexp_replace_query = f"SELECT REGEXP_REPLACE(''Welcome To Tutorialspoint!'', ''Welcome'', ''Welcom'') AS RESULT;" cursorObj.execute(regexp_replace_query) # Fetching all the results results = cursorObj.fetchall() # Display the result print("Result of REGEXP_REPLACE() Function:") for row in results: result = row[0] print(f"The modified string is: ''{result}''") cursorObj.close() connection.close()
Output
The output obtained is as shown below −
Result of REGEXP_REPLACE() Function: The modified string is: ''Welcom To Tutorialspoint!''
”;