Ain’t Nothing Like Saying STUFF this & Moving On

Previously, we talked about the REPLACE and TRANSLATE functions and how similar, but not quite the same they are. Today, I want to talk about yet another function that does something similar but not quite the same.

Today, I want to discuss the STUFF function and how it can add or even replace parts of a string.

So the STUFF function, with its parameters, looks like this:

STUFF (character_expression , start , length , replaceWith_expression)

• The character_expression is the field or variable to change.
• Start and length show what position to start with and how big of a substring should be replaced.
• ReplaceWith_expression shows what to replace it with.

In an example that we can all walkthrough, we can do the following:

SELECT STUFF(Venue, 3, 0, 'The') 
FROM dbo.NirvanaPerformances

‘Venue’ would be the field we want to look into. ‘3’ would show that we want to look at the 3 character position of the Venue field. ‘0’ shows that we just want to insert some characters into this field. And finally, “The” just shows which characters we want to insert.

Now that is probably not the most useful thing ever. I can do close to the same thing with the following code:

SELECT LEFT(Venue, 2) + 'The' + 
   RIGHT(Venue, CASE WHEN LEN(Venue) > 2 THEN LEN(Venue) - 2 ELSE 0 END) 
FROM dbo.NirvanaPerformances

Yes, lots more typing. But it also doesn’t give you a NULL when the string you’re replacing is less than the 3 in the original STUFF function. (Yes, that’s a fun find if you don’t know it’s going to happen.)

Now, if you’re wondering why 3 is the magic number in the above paragraph – it’s because we’re starting at the 3rd position. Not because the length of the string we’re adding – “The” – is 3 characters long. Just wanted to be clear on that one…

Sometimes, we also want to actually replace phrases in our original string. We can do that by changing the “0” in our original STUFF function to any number greater than 0 – in this case “1”.

Notice that in row 1, it went from being “house party” to “hoThese party”. This is because the “u” in “house” is in the 3rd character position and is the only character to be replaced by the “The” phrase.

Word of warning when you’re replacing characters this way.

* If you were to attempt to update a character field so that the length of it is longer than the field length is – then you’ll get an error. For example, in the example below, the field length of Venue is only 75 characters. The Venue I want to replace already has a length of 65 characters.

Yes, we would get one of our favorite errors of all time – “String or binary data would be truncated.”

And with that my friends I end today’s discussion on the STUFF function. There are a few more things we can do with it, but I’ll talk about them next time! So until then – just STUFF it!