MySQL REPLACE() function
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
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:
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>
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
- Weekly Trends
- Java Basic Programming Exercises
- SQL Subqueries
- Adventureworks Database Exercises
- C# Sharp Basic Exercises
- SQL COUNT() with distinct
- JavaScript String Exercises
- JavaScript HTML Form Validation
- Java Collection Exercises
- SQL COUNT() function
- SQL Inner Join
- JavaScript functions Exercises
- Python Tutorial
- Python Array Exercises
- SQL Cross Join
- C# Sharp Array Exercises