SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

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.


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'.

Vivek's SQL Notes

Vivek johari is currently a Analyst and have more that 5.5 yeras of experience in database. He has Master degree in Computer and also he is Microsoft certified Sql DBA (MCTS)& Microsoft certified SQl BI professional(MCTS). He is also Oracle certified profession(OCP)DBA in ORACLE 10g and ORACLE 9i.He has the experience of working in PL/SQL, T-SQL and SSIS/SSRS. His work basically involved designing and optimization of the Database.He has also published many database articles on his blog Technologies with Vivek Johari.


Leave a comment on the original post [feedproxy.google.com, opens in a new window]

Loading comments...