Monthly Archives: May 2018

Stuff – The SQL Server Function

I want to dedicate a small post to STUFF. It’s a pretty handy little function in SQL Server which deletes a sequence of characters from a source string and then inserts another sequence of characters into the source string, starting at a specified position. The best way to show how it works is with examples.

Lets turn the word inimical into inimitable:

SELECT STUFF('inimical', 6, 3, 'table')

outputs: inimitable

STUFF is not 0-indexed. So, it goes to the 6th character in the string, deletes 3 characters and replaces that with the string in the last parameter. Now we have inimitable.

Add a colon into a time:

SELECT STUFF('1159', 3, 0, ':') 

outputs: 11:59

Mask a credit card number:

DECLARE @CreditCardNumber VARCHAR(20)
SET @CreditCardNumber = '9876-5432-0987-6543'

SELECT STUFF(@CreditCardNumber, 1, LEN(@CreditCardNumber) - 4, REPLICATE('X', LEN(@CreditCardNumber) - 4)) AS [Output]


So, STUFF is pretty cool. But there is something which I use it for quite regularly in my work. As it is quite involved, I’ll do a separate post to walk through it (this post is really just “setting the table” for that one).