How to replace strings in SQL Server?

In SQL Server, you can use the REPLACE function to replace strings.

The syntax of the REPLACE function is as follows:

REPLACE (string_expression, search_string, replacement_string)

Parameter description:

1. string_expression: the string expression to be replaced.
2. search_string: the substring to be replaced.
3. replacement_string: the new string used for replacement.

Here is an example:

SELECT REPLACE('Hello, World!', 'Hello', 'Hi')

-- 输出结果为：Hi, World!

In the above example, replace the word “Hello” with “Hi” in the original string and output the result.

It is important to note that the REPLACE function is case-sensitive. If you want to perform a case-insensitive replacement, you can use the COLLATE keyword to specify a case-insensitive collation rule, for example:

SELECT REPLACE('Hello, World!', 'hello', 'Hi') COLLATE Latin1_General_CI_AI

-- 输出结果为：Hi, World!

In the example above, COLLATE Latin1_General_CI_AI is used to specify a case-insensitive sorting rule, achieving a case-insensitive replacement.