SQL Server SUBSTITUTE Function

Updated 2023-11-10 16:12:54.883000

Description

Use the scalar function SUBSTITUTE to substitute new_text for old_text in a text string.

Syntax

SELECT [westclintech].[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 Type

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.

select wct.substitute('April 19, 1919', '19', '20', 1);

This produces the following result.

select wct.substitute('April 19, 1919', '19', '20', NULL);

This produces the following result.