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