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‘.
Stuff works only in SQL Server or other database too??
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
hi johari excellent explanation….can you post the query on stuff with xmlpath
wow
Thanks Asad
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')