The REPLACE string function replaces all occurrences of the second specified string expression in the first string expression with a third expression. The syntax of the REPLACE string function is as follows:
REPLACE ( <string_expression_1>, <string_expression_2>, <string_expression_3> )
The first parameter of the REPLACE string function, <string_expression_1>, is the string expression to be searched, the second parameter, <string_expression_2>, is the string expression to try to find, and the third parameter, <string_expression_3>, is the replacement string expression. All three parameters can be of character or binary data. If any of the parameters is NULL, the REPLACE string function returns NULL.
Here are a few examples on the uses of the REPLACE string function:
Usage #1 : Count the Occurrence of a Character
DECLARE @Input VARCHAR(50) = 'SHE SELLS SEASHELLS AT THE SEASHORE'
DECLARE @Char CHAR(1) = 'E'
SELECT LEN(@Input) - LEN(REPLACE(@Input, @Char, '')) AS [CharCount]
CharCount
-----------
7
Usage #2 : Count the Occurrence of a String
DECLARE @Input VARCHAR(50) = 'I SCREAM, YOU SCREAM, WE ALL SCREAM FOR ICE CREAM'
DECLARE @String VARCHAR(10) = 'SCREAM'
SELECT (LEN(@Input) - LEN(REPLACE(@Input, @String, ''))) / LEN(@String) AS [StringCount]
StringCount
-------------
3
Usage #3 : Count the Number of Words Within a String
DECLARE @Input VARCHAR(50) = 'SHE SELLS SEASHELLS AT THE SEASHORE'
SELECT LEN(@Input) - LEN(REPLACE(@Input, ' ', '')) + 1 AS [WordCount]
WordCount
-----------
6
Usage #4 : Trim Leading Zeros
DECLARE @Input VARCHAR(20)
SET @Input = '0000120-3100'
SELECT REPLACE(LTRIM(REPLACE(@Input, '0', ' ')), ' ', '0')
Usage #5 : String Character Translation
DECLARE @Input VARCHAR(26) = 'ABCDEFGHIJKLMNOPQRSTUVWXYZ'
DECLARE @Output VARCHAR(26) = '!@#$%^&*()[]{}\|;:<>,./?`~'
DECLARE @String VARCHAR(50) = 'THE QUICK BROWN FOX JUMPS OVER THE LAZY DOG'
SELECT @String = REPLACE(@String, SUBSTRING(@Input, [Number], 1), SUBSTRING(@Output, [Number], 1))
FROM [master].[dbo].[spt_values]
WHERE [Type] = 'P' AND [Number] != 0 AND [Number] <= LEN(@Input)
SELECT @String AS [String]
String
----------------------------------------------
>*% ;,(#[ @:\/} ^\? ),{|< \.%: >*% ]!~` $\&