T-SQL: The STUFF method, inserting characters to a varchar

There are many scenarios in which you might want to add characters to the middle of a string, for example formatting a date string from ddMMyyyy to dd/MM/yyyy (standard format in the UK).

There are also quite a few ways to do this, you can use substring and concatenation methods, or in JavaScript you could use a split, splice & join.

In SQL there is a method to do this, you might think that it would be called something nice, like splice, but no! It’s called STUFF! Granted, it does sound worse when you place an exclamation mark after it.

Anyway, this is how you would use the STUFF method using our example above.

DECLARE @strDate VARCHAR(8) = '16041980'

SELECT STUFF(STUFF(@strDate, 5, 0, '/'), 3, 0, '/') as [formattedDateString]

As you can see, we have used the STUFF method twice here, starting with the inner call, we add the ‘/’ character that is nearest the end of our string, then with the outer call, we add the second from last ‘/’ character.

It is important that we do it in this order, as we are modifying the length of the string with each call, & therefore the position of the characters after the one that we are adding.

The 5 and the 3 in the above snippet indicate the positions to insert the character, the 0’s indicate how many characters at those positions we would like to overwrite, the others are self explanatory, however if you would like it, further reference can be found here on MSDN.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s