How to use replace function in mysql

The REPLACE() function in MySQL is used to replace characters or substrings in a string. Its syntax is: REPLACE(string, search_string, replace_string). It supports replacing multiple substrings simultaneously, using regular expressions for replacement, and replacing null values. For example, the following example replaces "Apple" with "Banana" in the string: SELECT REPLACE('I love Apple', 'Apple', 'Banana');

Usage of REPLACE() function in MySQL

The REPLACE() function is used to replace characters or substrings in a string. The syntax is as follows:

<code class="sql">REPLACE(string, search_string, replace_string)</code>

Parameter description:

• string: The string that needs to replace characters.
• search_string: The substring to be replaced.
• replace_string: New substring used to replace search_string.

Usage example:

The following example replaces "Apple" in the string with "Banana":

<code class="sql">SELECT REPLACE('I love Apple', 'Apple', 'Banana');</code>

Output:

<code>I love Banana</code>

Other usage:

1. Replace multiple substrings

The REPLACE() function can replace multiple subcharacters at the same time string. For example, the following statement replaces "a" and "e" with "o" in the string:

<code class="sql">SELECT REPLACE('apple', 'a', 'o')
SELECT REPLACE('apple', 'e', 'o')</code>

Output:

<code>opple
oploe</code>

2. Use regular expressions

The REPLACE() function supports replacement using regular expressions. For example, the following statement replaces all numbers in the string with "*":

<code class="sql">SELECT REPLACE('12345', '[0-9]', '*')</code>

Output:

<code>*****</code>

3. Replace empty values

REPLACE () function can be used to replace null values. For example, the following statement replaces null values ​​in a string with "N/A":

<code class="sql">SELECT REPLACE(NULL, NULL, 'N/A')</code>

Output:

<code>N/A</code>

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