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

Leave a Comment

NOTE - You can use these HTML tags and attributes:
<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

Trackbacks and Pingbacks: