MySQL REPLACE() function

Last update on August 19 2022 21:50:41 (UTC/GMT +8 hours)

REPLACE() function

MySQL REPLACE() replaces all the occurrences of a substring within a string.

Syntax:

REPLACE(str, find_string, replace_with)

Arguments:

Name	Description
str	A string.
find_string	A string which is present one or more times within the string str.
replace_with	A string which will replace every time it finds find_string within str.

Syntax Diagram:

MySQL Version: 5.6

MySQL: Replace Presentation - w3resource

Video Presentation:

Example of MySQL REPLACE() function

The following MySQL statement replaces every time it finds ‘ur’ within the ‘w3resource’ by ‘r’.

Code:

SELECT REPLACE('w3resource','ur','r');

Sample Output:

mysql> SELECT REPLACE('w3resource','ur','r');
+--------------------------------+
| REPLACE('w3resource','ur','r') |
+--------------------------------+
| w3resorce                      | 
+--------------------------------+
1 row in set (0.02 sec)

Pictorial Presentation:

MySQL REPLACE pictorial presentation

Example of MySQL REPLACE() function with where clause

The following MySQL statement replaces all the occurrences of ‘K’ with 'SA' within the column country from the table publisher for those rows, in which the column value of country is the UK.

Code:

SELECT pub_city,country,
REPLACE(country,'K','SA') 
FROM publisher 
WHERE country='UK';

Sample table: publisher

Sample Output:

mysql> SELECT pub_city,country,
    -> REPLACE(country,'K','SA') 
    -> FROM publisher 
    -> WHERE country='UK';
+-----------+---------+---------------------------+
| pub_city  | country | REPLACE(country,'K','SA') |
+-----------+---------+---------------------------+
| London    | UK      | USA                       | 
| Cambridge | UK      | USA                       | 
+-----------+---------+---------------------------+
2 rows in set (0.05 sec)

PHP script:

<!doctype html>
<html lang="en">
<head>
<meta charset="utf-8">
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<title>example-replace-function - php mysql examples | w3resource</title>
<meta name="description" content="example-replace-function - php mysql examples | w3resource">
<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.5/css/bootstrap.min.css">
</head>
<body>
<div class="container">
<div class="row">
<div class="col-md-12">
<h2>A list of Publishers those who belong to USA. Right column shows the name of the publisher enclosed with single quotes:</h2>
<table class='table table-bordered'>
<tr>
<th>Publishers city</th><th>Publishers country</th><th>Value of Publishers country where string 'k' is replaced with 'sa':</th>
</tr>
<?php
$hostname="your_hostname";
$username="your_username";
$password="your_password";
$db = "your_dbname";
$dbh = new PDO("mysql:host=$hostname;dbname=$db", $username, $password);
foreach($dbh->query('SELECT pub_city,country,REPLACE(country,"K","SA") as output 
FROM publisher
WHERE country="UK"') as $row) {
echo "<tr>";
echo "<td>" . $row['pub_city'] . "</td>"; 
echo "<td>" . $row['country'] . "</td>";
echo "<td>" . $row['output'] . "</td>";
echo "</tr>"; 
}
?>
</tbody></table>
</div>
</div>
</div>
</body>
</html>

View the example in browser

JSP script:

<%@page contentType="text/html" pageEncoding="UTF-8"%>
<%@ page import="java.sql.*" %>
<%@ page import="java.io.*" %>
<!DOCTYPE html>
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<title>example-replace-function</title>
</head>
<body>
<%
try {
Class.forName("com.mysql.jdbc.Driver").newInstance();
String Host = "jdbc:mysql://localhost:3306/w3resour_bookinfo";
Connection connection = null;
Statement statement = null;
ResultSet rs = null;
connection = DriverManager.getConnection(Host, "root", "datasoft123");
statement = connection.createStatement();
String Data ="SELECT pub_city,country,REPLACE(country,'K','SA') as output FROM publisher WHERE country='UK'";
rs = statement.executeQuery(Data);
%>
<TABLE border="1">
<tr width="10" bgcolor="#9979">
<td>Publishers city</td>
<td>Publishers country</td>
<td>Value of Publishers country where string 'k' is replaced with 'sa'</td>
</tr>
<%
while (rs.next()) {
%>
<TR>
<TD><%=rs.getString("pub_city")%></TD>
<TD><%=rs.getString("country")%></TD>
<TD><%=rs.getString("output")%></TD>
</TR>
<%   }    %>
</table>
<%
rs.close();
statement.close();
connection.close();
} catch (Exception ex) {
out.println("Cant connect to database.");
}
%>
</body>
</html>

MySQL: Find and Replace Data

We have a table called test with following records :

mysql> SELECT * FROM test;

+-----------+
| test_char |
+-----------+
| Abcd      |
| Wxyz      |
| Scott     |
| Robin     |
+-----------+
4 rows in set (0.00 sec)

To find and replace 'Scott' with 'Sidhu' you can use the following MySQL statement :

mysql> UPDATE test set test_char = replace(test_char, 'Scott', 'Sidhu');
Query OK, 1 row affected (0.04 sec)
Rows matched: 4  Changed: 1  Warnings: 0

mysql> SELECT * FROM test;

+-----------+
| test_char |
+-----------+
| Abcd      |
| Wxyz      |
| Sidhu     |
| Robin     |
+-----------+
4 rows in set (0.00 sec)

Online Practice Editor:

All String Functions

Previous: REPEAT
Next: REVERSE