Users: 15,000+ | Page Views: 50,000+ | PPS: 2.5
TechDevOps.com
Explore Tools
{Experts in Microsoft SQL Server Operations, Performance, HA/DR, Data Migrations & Cloud Computing}



T-SQL Stuff() vs Replace()
by BF (Senior MSSQL Operations - Enterprise, Cloud, Strategy)
2016-06-28









STUFF (Transact-SQL) - can be used to insert/stuff a string into another string. It deletes a specified length of characters in the
first string at the start position, then it inserts the second string into the first string at the start position.


Example #1: Replace domain name part with another one (gmail with test)

DECLARE @email VARCHAR(200) = 'JimmyF@gmail.ca'
SELECT Stuff(@email, Charindex('@', @email)+1, (Charindex('.', @email)-Charindex('@', @email))-1, 'test')

Result:
JimmyF@test.ca


Example #2: Mask a Credit Card Number

DECLARE @CCNumber VARCHAR(20)
SET @CCNumber = '111111111111111'
SELECT STUFF(@CCNumber, 1, LEN(@CCNumber) - 4, REPLICATE('X', LEN(@CCNumber) - 4))

Result:
XXXXXXXXXXX1111




REPLACE (Transact-SQL) - can be used to replaces all occurrences of a specified string value with another string value.


Example #1: Replace all occurrences of SQL with another value

DECLARE @var1 VARCHAR(25) = 'SQL 2016'
SELECT REPLACE(@var1,'SQL','Microsoft SQL Server')

Result:
Microsoft SQL Server 2016


Example #2: Replace all occurrences of SQL with another value

DECLARE @var1 VARCHAR(100) = 'SQL 2012, SQL 2014, SQL 2016'
SELECT REPLACE(@var1,'SQL','Microsoft SQL Server')

Result:
Microsoft SQL Server 2012, Microsoft SQL Server 2014, Microsoft SQL Server 2016