MS SQL Server REPLACE() Function

Sometimes you may need to replace all occurrences of a specific string with another string value in MS SQL Server. There is a function called REPLACE in MS SQL Server by which you can do this. REPLACE function replaces all occurrences of a specified string value with another string value.

Let’s see the function syntax:

REPLACE(string_expression,string_pattern,string_replacement);

In this syntax:

• In this function there are only one parameter.
• Parameter string_expression is the string expression to be searched. string_expression can be of a character or binary data type.
• Parameter string_pattern is the substring to be found. string_pattern can be of a character or binary data type. string_pattern cannot be an empty string (”), and must not exceed the maximum number of bytes that fits on a page.
• Parameter string_replacement is the replacement string. string_replacement can be of a character or binary data type.
• Function returns nvarchar if one of the input arguments is of the nvarchar data type; otherwise, REPLACE returns varchar. Returns NULL if any one of the arguments is NULL. If string_expression is not of type varchar(max) or nvarchar(max), REPLACE truncates the return value at 8,000 bytes. To return values greater than 8,000 bytes, string_expression must be explicitly cast to a large-value data type.
• Function performs comparisons based on the collation of the input. To perform a comparison in a specified collation, you can use COLLATE to apply an explicit collation to the input.
• Function returns the final string value after replacement.

The following example uses REVERSE function to reverse of a specific string.

DECLARE @name varchar(30) = 'Minhajur Rahman Khan';
SELECT REPLACE(@name,'Minhaj','Fahim') AS first_example,
REPLACE(@name COLLATE Latin1_General_BIN,'MINHAJ','Fahim') AS second_example; 

Here is the result set.

I have an another post MS SQL Server String Related Functions where you will learn about different string related functions of MS SQL Server.

In this tutorial, I have shown you how to replace a specific string with another string value in MS SQL Server. Hope you have enjoyed the tutorial. If you want to get updated, like my facebook page http://www.facebook.com/freetechtrainer and stay connected.