REPLACE String Function¶

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.

Sample Uses of the REPLACE String Function¶

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
----------------------------------------------
>*% ;,(#[ @:\/} ^\? ),{|< \.%: >*% ]!~` $\&