I’m sure you’ve all heard it, and probably even said it, many times: “SQL Server sucks at string manipulation”. And with good reason – it is true. But not quite as true as many seem to believe.

I notice that many people who complain about SQL Server lacking string manipulation are themselves unaware of the string functions that SQL Server does have. Most know LIKE, LEFT, RIGHT, and SUBSTRING. Some also know CHARINDEX, maybe even REPLACE. But how many of you know and use PATINDEX, REVERSE, QUOTENAME, and LTRIM, to name just a few?

The string function that appears to be the most overlooked has to be STUFF. And yet, this function can prove to be an invaluable tool. I’ll give a short description first, then show some examples of how STUFF can be used for string manipulation that would otherwise be much harder to achieve.

What STUFF does, basically, is cut a specified portion from a string and replace it with a new string. The replacement string can be shorter, longer, or exactly as long as the part that has been cut out. The STUFF function takes four arguments, none of which is optional. The first is of character data type and specifies the input string; the second and third are integers specifying the starting position and length of the substring to remove, and the fourth is the replacement string. Here are some examples to illustrate the use of STUFF:

DECLARE @Test varchar(15)

SET @Test = ‘Hugo Kornelis’

SELECT STUFF (@Test, 1, 4, ‘I am’),   — Basic usage example

       STUFF (@Test, 6, 0, ‘SQL ‘),   — Replaced string can be empty

       STUFF (@Test, 2, 10, ”)       — Replacement string can be empty

————— ——————- ——

I am Kornelis   Hugo SQL Kornelis   His

The first real world example of using STUFF is based on a newsgroup question. The question was how to replace a substring of a string. Here’s the simplified and non-working example given by the poster (based on the pubs sample database):

UPDATE authors

SET    SUBSTRING(phone,5,3) = ‘888’

WHERE  SUBSTRING(phone,5,3) = ‘826’;

One of the regulars in the group replied with this suggestion:

UPDATE authors

SET    phone = REPLACE(phone, ‘826’, ‘888’)

WHERE  SUBSTRING(phone,5,3) = ‘826’;

That looks clean and tidy, and runs fine on the data in pubs – but what if one of the authors in pubs has phone number ‘801 826-0826’? Indeed, it would be changed to ‘801 888-0888’ instead of ‘801 888-0826’, as requested.

Another way to do this that I often see recommended (though not in this particular thread) is to use LEFT and SUBSTRING to cut the string in pieces, then mend them together after making the change. Like this:

UPDATE authors

SET    phone = LEFT(phone, 4) + ‘888’ + SUBSTRING(phone, 8, LEN(phone) – 7)

WHERE  SUBSTRING(phone,5,3) = ‘826’;

That works – but ugh!, how ugly. This would be a lot easier with STUFF:

UPDATE authors

SET    phone = STUFF(phone, 5, 3, ‘888’)

WHERE  SUBSTRING(phone,5,3) = ‘826’;

Here’s another example. A common question in the newsgroups is how to find individual parts in a string. For instance, a bulk import results in a table of names in the form “Lastname, Firstname”. How to get individual Lastname and Firstname values from this?

For the first part of the string (Lastname in this case), this is easy. Use POSINDEX to find the starting position of the separator, use that to calculate the argument for the LEFT function.

The second part is harder. Answers typically given in the groups to these questions use techniques such as finding the starting position, calculating remaining length and feeding those as parameters to the SUBSTRING (version 1 below); using REVERSE to change the task to another task of finding the first part in a string (version 2 below) or using RIGHT instead of LEFT, with again the use of REVERSE to calculate length (version 3). However, with STUFF (version 4), this becomes much easier! See version 4, below:

CREATE TABLE BadData (FullName varchar(20) NOT NULL);

INSERT INTO BadData (FullName)

SELECT ‘Clinton, Bill’ UNION ALL

SELECT ‘Johnson, Lyndon’ UNION ALL

SELECT ‘Bush, George’;

— Version 1, using SUBSTRING

SELECT LEFT(FullName, CHARINDEX(‘, ‘, FullName) – 1) AS LastName,

       SUBSTRING(FullName,

                 CHARINDEX(‘, ‘, FullName) + 2,

                 LEN(FullName) – CHARINDEX(‘, ‘, FullName) – 1) AS FirstName

FROM   BadData

— Version 2, using REVERSE, LEFT, and REVERSE again

SELECT LEFT(FullName, CHARINDEX(‘, ‘, FullName) – 1) AS LastName,

       REVERSE(LEFT(REVERSE(FullName),

              CHARINDEX(‘ ,’, REVERSE(FullName)) – 1)) AS FirstName

FROM   BadData

— Version 3, using RIGHT and REVERSE

SELECT LEFT(FullName, CHARINDEX(‘, ‘, FullName) – 1) AS LastName,

       RIGHT(FullName, CHARINDEX(‘ ,’, REVERSE(FullName)) – 1) AS FirstName

FROM   BadData

— Version 4, using STUFF

SELECT LEFT(FullName, CHARINDEX(‘, ‘, FullName) – 1) AS LastName,

       STUFF(FullName, 1, CHARINDEX(‘, ‘, FullName) + 1, ”) AS FirstName

FROM   BadData

You might say that there’s not actually that much difference between versions 3 and 4 above, so why am I so enthusiastic about STUFF? To see that, let’s take this to the next level. What if the names in the imported data are of the form “Lastname, Firstname MiddleInitial”, with MiddleInitial being optional? Below, you’ll find the best I was able to do without STUFF, and than a version with STUFF – still not pretty, but not quite as awful as the first version, I’d say. If anyone is able to write a shorter version, I’d love to hear it!

CREATE TABLE BadData (FullName varchar(20) NOT NULL);

INSERT INTO BadData (FullName)

SELECT ‘Clinton, Bill’ UNION ALL

SELECT ‘Johnson, Lyndon, B.’ UNION ALL

SELECT ‘Bush, George, H.W.’;

— Version 1, without STUFF

SELECT FullName,

       LEFT(FullName, CHARINDEX(‘, ‘, FullName) – 1) AS LastName,

       SUBSTRING(FullName,

                 CHARINDEX(‘, ‘, FullName) + 2,

                 CHARINDEX(‘, ‘, FullName + ‘, ‘,

                           CHARINDEX(‘, ‘, FullName) + 2)

               – CHARINDEX(‘, ‘, FullName) – 2) AS FirstName,

       CASE WHEN FullName LIKE ‘%, %, %’

            THEN RIGHT(FullName, CHARINDEX(‘ ,’, REVERSE(FullName)) – 1)

            ELSE ” END AS MiddleInitial

FROM   BadData

— Version 2, with STUFF

SELECT FullName,

       LEFT(FullName, CHARINDEX(‘, ‘, FullName) – 1) AS LastName,

       STUFF(LEFT(FullName, CHARINDEX(‘, ‘, FullName + ‘, ‘,

                                      CHARINDEX(‘, ‘, FullName) + 2) – 1),

             1, CHARINDEX(‘, ‘, FullName) + 1, ”) AS FirstName,

       STUFF(FullName, 1,

             CHARINDEX(‘, ‘, FullName + ‘, ‘,

                       CHARINDEX(‘, ‘, FullName) + 2), ”) AS MiddleInitial

FROM   BadData

As you have seen, learning to use STUFF when appropriate can make hard tasks easy, complex queries simple, and extremely complicated queries somewhat less complicated. This string function is a tool that every SQL Server developer should know. So the next time someone complains how SQL Server is severely lacking adequate string manipulation tools, you know what to do – just tell’m to stuff it!