SUBSTITUTE
Updated: 30 April 2009
Use SUBSTITUTE to substitute new_text for old_text in a text string.
Syntax
SELECT [wctString].[wct].[SUBSTITUTE] (
<@Text, nvarchar(max),>
,<@Old_text, nvarchar(max),>
,<@New_text, nvarchar(max),>
,<@Instance_num, int,>)
Arguments
@Text
is the text value to be evaluated. The @Text argument can be of data types that are implicitly convertible to nvarchar or ntext.
@Old_Text
is the text value to be removed. The @Old_text argument can be of data types that are implicitly convertible to nvarchar or ntext.
@New_Text
is the text value to be used in the substitution for @Old_text. The @New_text argument can be of data types that are implicitly convertible to nvarchar or ntext.
@Instance_num
specifies which occurrence of @Old_text you want to replace with @New_text. The @Instance_num argument can be of data types that are implicitly convertible to int.
Return Types
nvarchar(max)
Remarks
· If @Instance_num is NULL, then all instances of @Old_text are replaced with @New_text.
Examples
select wct.substitute('April 19, 1919','19','20',2)
This produces the following result
--------------
April 19, 2019
(1 row(s) affected)
select wct.substitute('April 19, 1919','19','20',1)
--------------
April 20, 1919
(1 row(s) affected)
select wct.substitute('April 19, 1919','19','20',NULL)
--------------
April 20, 2020
(1 row(s) affected)