T-SQL Tutorial

T-SQL REPLACE function


The SQL Server REPLACE function is a built-in string manipulation function that allows you to replace a sequence of characters in a string with another set of characters. It is used to replace all occurrences of a substring in a specified string with a new substring.


Syntax

The syntax for the REPLACE function is as follows:

REPLACE (string_expression, search_string, replacement_string)

Where:
string_expression: The string that you want to search and replace characters in.
search_string: The substring that you want to replace in the string_expression.
replacement_string: The string that you want to replace the search_string with.


Example

Here's an example of how to use the REPLACE function in SQL Server:

SELECT REPLACE('Hello, world!', 'world', 'SQL Server')

This query will return the string "Hello, SQL Server!" as the output, because the REPLACE function replaces the substring "world" in the original string "Hello, world!" with "SQL Server".

You can also use the REPLACE function in combination with other SQL functions to manipulate strings in more complex ways. For example, you can use the REPLACE function with the CONCAT function to replace and concatenate strings:

SELECT CONCAT('The ', REPLACE('quick brown fox', 'brown', 'lazy'), ' jumps over the lazy dog.')

This query will return the string "The quick lazy fox jumps over the lazy dog.", because the REPLACE function replaces the substring "brown" in the original string "quick brown fox" with "lazy", and then the CONCAT function combines the new string with other strings.

Overall, the SQL Server REPLACE function is a powerful tool for manipulating strings in SQL Server, and it can be used in many different ways to perform complex string transformations.

See also: T-SQL Functions -> Substring | Patindex | Right | Rtrim | Upper