Stuff VS Replace function in SQL Server

Stuff VS Replace function

Stuff Function

This function is used to replace string from the given start position, passed as 2nd argument with string passed as last argument. In Stuff function, 3rd argument defines the number of characters which are going to be replaced.

Syntax:-

STUFF ( character_expression , start , length , replaceWith_expression )

For example:-

Select Stuff (‘Software’, 3, 3, ‘abc’)

This query will return the string “Soabcare”. In this example, Stuff function replaces the string “Software” onwards the 3rd position(‘ftw’) with ‘abc’.

Replace Function 

Replace function is used to replace all occurrence of a specified with the string passed as last argument.

Syntax :-

REPLACE ( string_expression , string_pattern , string_replacement )

For example:-

Select Replace (‘Abcabcabc’, ‘bc’, ‘xy’)

This query will return the string Axyaxyaxy. In this example, Replace function replaces the occurrence of each ‘bc‘ string with ‘xy‘.

DMCA.com

Discover more from Technology with Vivek Johari

Subscribe to get the latest posts sent to your email.

6 comments

  4. Hi Vivek,

    I have a MS SQL query below where i am using stuff in co-related subquery instead of a simple sub-query with my minimum knowledge on this. Need your help to use in simple subquery to improve the performance. Right now, for every row the co-related sub query executes which will degrade the performance. Thanks in advance.
    SELECT SSNum
    , Segment
    , lic.license
    , Stuff(
    (
    Select ', ' + R1.RepNum
    From Caesar.Representative As R1
    Where R1.status = 'A' AND R1.Producer in ('DUPRR', 'DUPBR', 'CBC', 'OTH')
    AND R1.SSNum = r.SSNum Order By R1.RepNum
    For Xml Path(''), type
    ).value('.', 'nvarchar(max)'), 1, 2, '') As Other_Rep_Numbers
    ,Stuff(
    (
    Select ', ' + RST.state
    From Caesar.States As RST
    Where RST.RepNum = r.RepNum
    Order By RST.state
    For Xml Path(''), type
    ).value('.', 'nvarchar(max)'), 1, 2, '') As State_Registration
    FROM Caesar.Representative r
    inner join Caesar.Producer p on (r.Producer = p.ProducerId)

    left join (SELECT distinct a.RepNum, license=( STUFF((SELECT replace(';Series '+ CAST(license AS VARCHAR(20)),'Series Insurance','Insurance')
    FROM (select RepNum, 'Insurance' License
    from Caesar.InsuranceLicense where ExpiredDate is null or ExpiredDate >= GETDATE()
    group by RepNum
    union
    select RepNum, License from Caesar.NASD where ApproveDate is not null) b
    where a.RepNum=b.RepNum
    FOR XML PATH('')),1,1,'') )
    from (select RepNum, 'Insurance' License
    from Caesar.InsuranceLicense where ExpiredDate is null or ExpiredDate >= GETDATE()
    group by RepNum
    union
    select RepNum, License from Caesar.NASD where ApproveDate is not null) a
    ) lic
    on r.RepNum = lic.RepNum
    where Crd is not null
    and not (r.Status = 'T' and r.EffectiveDate < CAST(DATEADD(month,-1,GETDATE() ) as date))
    and r.Producer not in ('DUPRR', 'DUPBR', 'CBC', 'OTH')

    Reply

Leave a Reply