Login     Register

        Contact Us     Search

XLeratorDB/strings Online Documentation

SQL Server SUBSTITUTE function


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)



Copyright 2008-2013 WestClinTech LLC         Privacy Policy        Terms of Service