How to replace part of a string in mysql

Mysql method of replacing part of a string: 1. Use the REPLACE() function, the syntax "REPLACE (string, search value, replacement value)"; 2. Use the INSERT() function, the syntax "INSERT (character String, replacement starting position, number of characters to be replaced, replacement value)".

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

mysql replace part of the string

1. Use the REPLACE() function

REPLACE( ) function replaces all occurrences of a substring in a string with a new substring.

Note: This function performs case-sensitive substitutions.

Syntax

REPLACE(string, from_string, new_string)

Parameters	Description
string	Required. Original string
from_string	Required. The substring to replace
new_string	Required. New replacement substring

[Example] Use the REPLACE function to perform string replacement operations

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

It can be seen from the running results that using REPLACE('aaa.mysql.com','a','w') Replace the "a" character of the "aaa.mysql.com" string with the "w" character, the result is "www.mysql.com".

2. Use the INSERT() function

The INSERT() function inserts a string into the string at the specified position and inserts a certain number of characters.

Syntax

INSERT(string, position, number, string2)

Parameters	Description
string	Required. The string to be modified
#position	Required. Insert string2 at position
number	Required. Number of characters to replace
string2	Required. The string to insert the string

If position exceeds the string length, the return value is the original string. If the length of number is greater than the length of other strings, replacement starts from position position. If any parameter is NULL, the return value is NULL.

[Example] Use the INSERT function to perform string replacement operations

mysql> SELECT INSERT('Football',2,4,'Play') AS col1,
    -> INSERT('Football',-1,4,'Play') AS col2,
    -> INSERT('Football',3,20,'Play') AS col3;
+----------+----------+--------+
| col1     | col2     | col3   |
+----------+----------+--------+
| FPlayall | Football | FoPlay |
+----------+----------+--------+
1 row in set (0.04 sec)

It can be seen from the execution results:

• The first functionINSERT( 'Football', 2, 4, 'Play') Replace "Football" with a string of length 4 starting from the 2nd character with Play, the result is "FPlayall";

• The second function INSERT('Football',-1,4,'Play') The starting position -1 exceeds the length of the string and returns the original string directly;

• The third functionINSERT('Football', 3, 20, 'Play') If the replacement length exceeds the length of the original string, it will start from the 3rd character. Intercept all subsequent characters and replace them with the specified character Play. The result is "FoPlay".

[Related recommendations: mysql video tutorial]

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