Comparing Stuff and Replace Command

Good Morning All,

I found a cool neat function in SQL Server that is pretty helpful for replacing string within text.  This function is called the Stuff (Transact-SQL).

This function is simpilar to the Replace (Transact-SQL) function but they are different.  In the replace function you replace one string with another in any place in the whole text whereas the stuff function only replaces the string if the text is a certain value based on position within the text.  Well look at an example where some of the data got insert wrong with '#' in the teams name and we want to remove the first occurrence within the text.

What will I cover in this blog?

• Setting up a table
• Show how the Replace function works
• Show how the Stuff function works

First let's set up a simple table.

--Remove table if its there
IF EXISTS(SELECT * FROM tempdb.dbo.sysobjects WHERE ID = OBJECT_ID(N'tempdb..#Teams') ) DROP TABLE #Teams

--Create new table
CREATE TABLE #Teams(TeamID INT IDENTITY(10000000,1),Team VARCHAR(20),Founded DATE,City VARCHAR(30),State CHAR(2))

--Insert Team 
INSERT INTO  #Teams(Team,Founded,City,State) 
    SELECT '#Milwaukee Brewers#','1970','Milwaukee','WI'
    UNION ALL
    SELECT '#Minnesota Twins#','1894','Minneapolis','MN'
    UNION ALL
    SELECT 'Chicago Cubs','1870','Chicago','IL'
    UNION ALL
    SELECT 'Detroit Tigers','1894','Detroit','MI'


Seconly, let's see how the replace command works.

SELECT Team, REPLACE(Team,'#','')AS RepaceFunction FROM #Teams

As you can see the replace command both '#' in the Milwaukee Brewers and Minneasota Twins entry.

Thirdly, let's look at the stuff command.  With the use of the case statement we are able to update the correct rows.

SELECT team,
       CASE
           WHEN  SUBSTRING(Team,1,1)='#' THEN STUFF(Team,1,1,' ')
           ELSE team
       END AS StuffFunction 
from #Teams

As you can see in this screen shot that it only removed the first '#' within the Milwaukee Brewers and Minneasota Twins string.

Bonus time!  Let's review both commands in one statement.

SELECT team,
       REPLACE(Team,'#','')AS RepaceFunction,
       CASE
           WHEN  SUBSTRING(Team,1,1)='#' THEN STUFF(Team,1,1,' ')
           ELSE team
       END AS StuffFunction 
from #Teams


Now you can see how powerful this might be in updating data in your database.  This is only one of many ways to use either the replace or stuff functions.

Have a great day everyone,

Robert