w3resource logo


MySQL replace function

MySQL REPLACE() function

rating MySQL replace() function has average rating 7 out of 10. Total 33 users rated.

<<PreviousNext>>

Description

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.

MySQL Version : 5.6

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');

Output

MySQL REPLACE()

Pictorial Presentation

MySQL REPLACE pictirial 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 UK.

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

Sample table : publisher

Output

MySQL REPLACE() EXAMPLE

PHP script

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />
<title>example-replace-function - php mysql examples | w3resource</title>
</head>
<body>
<?php
echo "<h2>A list of Publishers those who belong to USA. Right column shows the name of the publisher enclosed with single quotes : </h2>";
echo "<table border='1' style='border-collapse: collapse;border-color: silver;'>";
echo "<tr style='font-weight: bold;'>";
echo "<td width='250' align='center'>Publishers city</td><td width='250' align='center'>Publishers country</td>
<td width='250' align='center'>Value of Publishers country where string 'k' is replaced with 'sa' : </td>";
echo "</tr>";
include("../dbopen.php");
$result = mysql_query('SELECT pub_city,country,REPLACE(country,"K","SA") as output // output is an alias 
FROM publisher
WHERE country="UK"');
while($row=mysql_fetch_array($result))
{
echo "<tr>";
echo "<td align='center' width='200'>" . $row['pub_city'] . "</td>";
echo "<td align='center' width='200'>" . $row['country'] . "</td>";
echo "<td align='center' width='200'>" . $row['output'] . "</td>";
echo "</tr>";
}
echo "</table>";
?>
</body>
</html>

View the example in browser

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)


We have compiled a number of MySQL Questions and Answers. Check and assess you MySQL skill by visiting those questions and answers.

<<PreviousNext>>