Mysql REPLACE() function ~ Tech Blog

Mysql 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.

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



Copy

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



Copy

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">
<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>



Copy

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)

Tech Blog

Tuesday, 10 July 2018