MySQL REPLACE() functie
Laatste update op 26 februari 2020 08:08:23 (UTC/GMT +8 uur)
REPLACE() functie
MySQL REPLACE() vervangt alle occurrences van een substring binnen een string.
Syntax:
REPLACE(str, find_string, replace_with)
Argumenten
| Naam | Beschrijving |
|---|---|
| str | Een tekenreeks. |
| find_string | Een string die een of meer keer voorkomt in de string str. |
| replace_with | Een tekenreeks die elke keer dat hij find_string binnen str vindt, zal vervangen. |
Syntax Diagram:
MySQL Versie: 5.6
Video presentatie:
Uw browser ondersteunt geen HTML5-video.
Voorbeeld van de MySQL REPLACE()-functie
De volgende MySQL-instructie vervangt elke keer dat het ‘ur’ vindt binnen de ‘w3resource’ door ‘r’.
Code :
SELECT REPLACE('w3resource','ur','r');Voorbeelduitvoer:
mysql> SELECT REPLACE('w3resource','ur','r');+--------------------------------+| REPLACE('w3resource','ur','r') |+--------------------------------+| w3resorce | +--------------------------------+1 row in set (0.02 sec)
Pictorische presentatie
Voorbeeld van MySQL REPLACE()-functie met where-clausule
Het volgende MySQL statement vervangt alle occurrences van ‘K’ door ‘SA’ binnen de kolom country van de tabel publisher voor die rijen waarin de kolomwaarde van land UK is.
Code:
SELECT pub_city,country,REPLACE(country,'K','SA') FROM publisher WHERE country='UK';Voorbeeld tabel: publisher
Voorbeeld uitvoer:
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 publisherWHERE country="UK"') as $row) {echo "<tr>";echo "<td>" . $row . "</td>"; echo "<td>" . $row . "</td>";echo "<td>" . $row . "</td>";echo "</tr>"; }?></tbody></table></div></div></div></body></html>Bekijk het voorbeeld 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: Gegevens zoeken en vervangen
We hebben een tabel genaamd test met de volgende records :
mysql> SELECT * FROM test;+-----------+| test_char |+-----------+| Abcd || Wxyz || Scott || Robin |+-----------+4 rows in set (0.00 sec)
Om ‘Scott’ te vinden en te vervangen door ‘Sidhu’ kunt u het volgende MySQL statement gebruiken :
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: 0mysql> SELECT * FROM test;+-----------+| test_char |+-----------+| Abcd || Wxyz || Sidhu || Robin |+-----------+4 rows in set (0.00 sec)
Alle String Functies
Vorige: REPEAT
Volgende: HERHALEN