SQL Server REPLACE() Function

In SQL Server, the REPLACE() function is used to replace specified characters or substrings in a string. It searches for the specified character or substring in the string and replaces it with another character or substring.

Syntax

The syntax for the REPLACE() function is:

REPLACE(string, find, replacement)

Where:

  • string: Required. The string to perform the replacement operation on.
  • find: Required. The substring to find and replace.
  • replacement: Required. The string to replace with.

Use cases

The REPLACE() function is very useful in the following scenarios:

  • Replacing characters or substrings in text strings.
  • Replacing spaces with other characters.
  • Modifying string values in query results.

Examples

Here are two examples of the REPLACE() function:

Example 1

Suppose we have a table containing order information, which includes a column named Product. We want to replace all occurrences of Printer in the product names of the orders with Scanner.

SELECT REPLACE(Product, 'Printer', 'Scanner') AS NewProduct
FROM Orders

This will return a column named NewProduct with all occurrences of Printer in the product names replaced with Scanner.

Example 2

Suppose we have a table containing personnel information, which includes a column named Name. We want to replace all spaces in the names with underscores.

SELECT REPLACE(Name, ' ', '_') AS NewName
FROM Personnel

This will return a column named NewName with all spaces in the names replaced with underscores.

Conclusion

The REPLACE() function is a very useful SQL Server function that allows us to find and replace specified characters or substrings in a string. It is useful in scenarios such as replacing characters or substrings in text strings, replacing spaces with other characters, and modifying string values in query results.