How to use replace in mysql

In mysql, when the replace function is used in conjunction with the SELECT statement, it can be used to perform string replacement operations. It also supports multiple strings to be replaced at the same time. The syntax is "SELECT REPLACE (column name of the database table) , the string that needs to be replaced, the string that needs to be replaced)".

The operating environment of this tutorial: windows10 system, mysql8.0.22 version, Dell G3 computer.

How to use replace in mysql

Syntax

replace(field,search,replace)

Instructions:

• field - column name of the database table

• search - the string to be replaced

• replace - the string to be replaced

Semantics: Replace Column name: All search strings appearing in field are replaced with replace strings.

mysql replace example description:

UPDATE tb1 SET f1=REPLACE(f1, 'abc', 'def');

Explanation: The value of abc in field f1 in table tb1 is updated to def. Generally used for fields where the value in a certain field is inappropriate and needs to be changed in batches. You can use update table set field=replace('field','a certain value of the field','the value that needs to be replaced');

REPLACE(str,from_str,to_str)

All occurrences of the string from_str in the string str are replaced by to_str, and then this string is returned.

The replacement function REPLACE(s, s1, s2) in MySQL uses string s2 to replace all string s1 in string s.

[Example] Use the REPLACE function to perform string replacement operations. The input SQL statement and execution process are as follows.

mysql> SELECT REPLACE('aaa.mysql.com','a','w');
+----------------------------------+
| REPLACE('aaa.mysql.com','a','w') |
+----------------------------------+
| www.mysql.com                    |
+----------------------------------+
1 row in set (0.00 sec)

As can be seen from the running results, use REPLACE('aaa.mysql.com', 'a', 'w') to replace the "a" character of the "aaa.mysql.com" string with "w" character, the result is "www.mysql.com".

Recommended learning: mysql video tutorial

The above is the detailed content of How to use replace in mysql. For more information, please follow other related articles on the PHP Chinese website!