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:
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.
|Fahimur Rahman Khan
||Minhajur Rahman Khan
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.