How to use replace function in sql

The REPLACE function in SQL is used to replace all given substrings with another substring in a text or string. Can be used for basic replacement, conditional replacement, and NULL value handling. The basic syntax is: REPLACE(string, old_substring, new_substring).

Usage of REPLACE function in SQL

The REPLACE function is used to replace all given values in a text or string. A given substring is another substring. It can update data in the database based on specified conditions.

Syntax

REPLACE(string, old_substring, new_substring)

Parameters

• string:Need to replace the substring text or string.
• old_substring:The substring to be replaced from string.
• new_substring:New substring that replaces old_substring.

Usage

1. Basic usage:Replace all occurrences of substrings in a text or string.

SELECT REPLACE('This is a sample text', 'sample', 'example');

Result: This is an example text

2. Conditional replacement:Replace substrings based on given conditions.

SELECT REPLACE('This is a sample text', 'sample', 'example', 1);

Result: This is an example text

where 1 specifies that only the first occurrence of the substring is replaced.

3. NULL value handling:The REPLACE function supports NULL values. If old_substring or new_substring is NULL, NULL is returned.

SELECT REPLACE('This is a sample text', NULL, 'example');

Result: NULL

Notes

##The REPLACE function is case-sensitive.
• If old_substring does not exist in string, no replacement is performed.
• The REPLACE function does not modify the original data. It returns a copy containing the replaced data.

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