Monday, November 25, 2013

Stuff VS Replace function in SQL Server



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

6 comments:

  1. Stuff works only in SQL Server or other database too??

    ReplyDelete
    Replies
    1. Hi Nidhi,
      Thanks for your valuable comments.
      There is no such function (STUFF) in oracle and mysql.
      In Oracle, we use replace function for replacing string and in MYSQL, we use Insert() function which behave simillar to STUFF functions.
      For Insert function in MYSQL follow this link
      http://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_insert

      Delete
  2. hi johari excellent explanation....can you post the query on stuff with xmlpath

    ReplyDelete
  3. 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')

    ReplyDelete