Shulou(Shulou.com)05/31 Report--

This article mainly explains "how to replace some strings with mysql". Interested friends may wish to have a look at it. The method introduced in this paper is simple, fast and practical. Let's let the editor take you to learn how to replace part of a string with mysql.

Mysql replace part of the string method: 1, use REPLACE () function, syntax "REPLACE (string, find value, replace value)"; 2, use INSERT () function, syntax "INSERT (string, replacement start position, number of characters to be replaced, replacement value)".

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

Mysql replaces part of the string

1. Use the REPLACE () function

The REPLACE () function replaces all appearing substrings in the string with a new substring.

Note: this function performs case-sensitive substitution.

Grammar

The REPLACE (string, from_string, new_string) parameter describes the string required items. The original string from_string must be. The substring new_string requirement to replace. New replacement substring

[example] use REPLACE function for string replacement operation

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 you can see from the running result, using REPLACE ('aaa.mysql.com','a','w') to 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 location and inserts a certain number of characters.

Grammar

The INSERT (string, position, number, string2) parameter describes the string required items. The string position requirement to be modified. Insert the location of the string2 number required. The number of characters to replace string2 required. The string to insert the string

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

[example] use INSERT function for string replacement operation

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)

As can be seen from the results of the implementation:

The first function, INSERT ('Football',2,4,'Play'), replaces the string "Football" with a length of 4 from the second character with Play, resulting in "FPlayall"

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

If the replacement length of the third function INSERT ('Football',3,20,'Play') exceeds the length of the original string, all subsequent characters are intercepted from the third character and replaced with the specified character Play, resulting in "FoPlay".

At this point, I believe you have a deeper understanding of "how mysql replaces part of the string". You might as well do it in practice. Here is the website, more related content can enter the relevant channels to inquire, follow us, continue to learn!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.