String Functions

REPLACE Vs. STUFF

REPLACE

  • To Replace all occurrences of the given pattern in a string.
  • It Executes till it replaces all occurrences of the given pattern in a string.
  • Syntax –
REPLACE ( String_Expression , String_Pattern , String_Replacement )
  • Example –
DECLARE @String_Expression VARCHAR(50)='Input:-)String:-)ForExample';
DECLARE @String_Pattern VARCHAR(10)=':-)';
DECLARE @String_Replacement VARCHAR(1)='';

SELECT @String_Expression AS INPUT_STRING,
REPLACE
 (
  @String_Expression,
  @String_Pattern,
  @String_Replacement
 ) AS REPLACE_STRING;

STUFF

  • To Replace the part of the string with some other string.
  • It Executes only one time.
  • Syntax –
STUFF (String_Expression, Start_Position, Length, String_Replacement)
  • Example –
DECLARE @String_Expression VARCHAR(50)='Input:-)String:-)ForExample';
DECLARE @String_Pattern VARCHAR(10)=':-)';
DECLARE @String_Replacement VARCHAR(1)='';

SELECT @String_Expression AS INPUT_STRING,
STUFF
 (
  @String_Expression, 
  CHARINDEX(@String_Pattern, @String_Expression), 
  LEN(@String_Pattern), 
  @String_Replacement
 ) AS STUFF_STRING;

REPLACE Vs STUFF – Output Example

Advertisement

2 thoughts on “REPLACE Vs. STUFF

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s