Skip Navigation Links
Home
Articles
SQL Server 2012
SQL Server 2014
SQL Server 2016
FAQ
Forums
Practice Test
Bookstore
Tip of the Day : How to Determine if a Table has a Primary Key
SQL Server Helper
Home > > Tip of the Day
SQL Server Helper - Tip of the Day

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

Back to Tip of the Day List Next Tip