💎🤖SQL Reloaded: Copilot Stories (Part IX: Working with the Stuff Function)

One of the underused string functions in SQL Server is Stuff, which returns part of a character, binary, text, or image expression. This post evaluates the answers provided by Microsoft 365 Copilot in the use of the respective function.

Disclaimer: The below code was generated by Copilot via prompts and is used for demonstration purposes!

Prompt:
Give me 10 scenarios with examples in which the Stuff function can be used in SQL Server.

Output:

/* Example 1: Inserting a Substring */

-- Insert a substring into a specific position within a string
SELECT STUFF('Hello World', 6, 0, 'SQL ') AS Result; -- Result: 'Hello SQL World'

/* Example 2: Replacing a Substring */

-- Replace a part of a string with another substring.
SELECT STUFF('Hello World', 7, 5, 'SQL Server') AS Result; -- Result: 'Hello SQL Server'

/* Example 3: Removing Characters */

-- Remove a specific number of characters from a string
SELECT STUFF('Hello World', 6, 5, '') AS Result; -- Result: 'Hello'

/* Example 4: Masking Sensitive Data */

-- Mask a credit card number
SELECT STUFF('1234-5678-9012-3456', 6, 9, '****-****') AS MaskedCard; -- Result: '1234-****-****-3456'

/* Example 5: Formatting Phone Numbers */

-- insert formatting characters into a phone number.
SELECT STUFF(STUFF('1234567890', 4, 0, '-'), 8, 0, '-') AS FormattedPhone; -- Result: '123-456-7890'

/* Example 6: Creating Initials */

-- extract initials from a full name.
SELECT STUFF(STUFF('John Doe', 2, 3, '.'), 5, 3, '.') AS Initials; -- Result: 'J.D.'

/* Example 7: Dynamic SQL Generation */

-- modify parts of a dynamic SQL query.
DECLARE @sql NVARCHAR(MAX) = 'SELECT * FROM Table WHERE Column = Value';
SELECT STUFF(@sql, CHARINDEX('Value', @sql), LEN('Value'), 'NewValue') AS ModifiedSQL; -- Result: 'SELECT * FROM Table WHERE Column = NewValue'

/* Example 8: URL Manipulation */

-- Insert or replace parts of a URL.
SELECT STUFF('http://example.com/page', 8, 0, 'www.') AS ModifiedURL; -- Result: 'http://www.example.com/page'

/* Example 9: Handling CSV Data */

-- Modify parts of a CSV string
SELECT STUFF('1,2,3,4,5', 3, 1, '10') AS ModifiedCSV; -- Result: '1,10,3,4,5'

/* Example 10: Custom String Formatting */ 

-- Insert custom formatting into a string
SELECT STUFF('20250305', 5, 0, '-') AS FormattedDate; -- Result: '2025-0305'

The first 6 examples are typical usages of the Stuff function while the other might raise the eyebrows of some developers.

Example 1: The example is not entirely correct (counting the characters starts at 1).

Example 2: In exchange, this example is correct.

Example 3: The example is unfortunately incorrect (same issue like in the first example). The technique can be useful when only one contiguous region is to be replaced once. Otherwise, one should use the Replace function. 

Example 4: Starting with SQL Server 2016 one can use dynamic masking for similar purposes, which should provide more flexibility in many cases. 

Example 5: Formatting phone number can be more complex than this because there can be different formats, country codes or any other special formats.

Example 6: Names can be quite complex, each part having different size.

Example 7: The example might work in certain cases, though it's enough for a field to contain the keyword "value" for the whole logic to generate errors. 

Example 8: The example is valid, though web addresses can use also other transfer protocols (ftp, ftps, sftp, scp, etc.).

Example 9: A more reliable method is splitting the string in parts and replacing the value from the nth position then inserting a text when the length of the values is not known.

Example 10: Even if the technique is valid, the example isn't entirely valid even if it might be met in practice. See 20th example below for a better approach. 

Prompt:
Give me 10 more scenarios with examples in which the Stuff function can be used in SQL Server.

Output:

/* Example 11: Reversing a string */

-- Reverse a string by replacing characters in a specific order.
SELECT STUFF(STUFF(STUFF('abcde', 1, 1, 'e'), 2, 1, 'd'), 3, 1, 'c') AS ReversedString; -- Result: 'edcba'

/* Example 12: Generating unique identifiers */

-- Modify parts of a GUID to create a custom identifier.
SELECT STUFF('123e4567-e89b-12d3-a456-426614174000', 1, 8, 'abcdef12') AS CustomGUID;-- Result: 'abcdef12-e89b-12d3-a456-426614174000'

/* Example 13: Combining columns */

-- Combine multiple columns into a single formatted string.
SELECT STUFF(FirstName + ' ' + LastName, LEN(FirstName) + 1, 0, ', ') AS FullName
FROM Employees;-- Result: 'John, Doe'

/* Example 14: Trimming strings */

-- Remove leading or trailing characters from a string.
SELECT STUFF('   Hello World   ', 1, 3, '') AS TrimmedString;-- Result: 'Hello World   '

/* Example 15: Creating abbreviations */

-- Generate abbreviations from a full string.
SELECT STUFF('North Atlantic Treaty Organization', 6, 22, 'ATO') AS Abbreviation;-- Result: 'NATO'

/* Example 16: Modifying JSON Data */

-- Insert or replace parts of a JSON string 
SELECT STUFF('{"name":"John","age":30}', 9, 4, 'Doe') AS ModifiedJSON;-- Result: '{"name":"Doe","age":30}'

/* Example 17: Handling XML Data */

-- Modify parts of an XML string.
SELECT STUFF('<name>John</name>', 7, 4, 'Doe') AS ModifiedXML;-- Result: '<name>Doe</name>'

/* Example 18: Generating random strings */

-- Insert random characters into a string.
SELECT STUFF('abcdef', 3, 0, CHAR(65 + ABS(CHECKSUM(NEWID())) % 26)) AS RandomString;-- Result: 'abXcdef' (X is a random character)

/* Example 19: Creating custom error messages  */

-- Insert dynamic values into error messages.
DECLARE @ErrorMessage NVARCHAR(100) = 'Error: Value {0} is not valid.';
SELECT STUFF(@ErrorMessage, CHARINDEX('{0}', @ErrorMessage), 3, '123') AS CustomErrorMessage;-- Result: 'Error: Value 123 is not valid.'

/* Example 20: Formatting dates */

-- Insert separators into a date string.
SELECT STUFF(STUFF('20250305', 5, 0, '-'), 8, 0, '-') AS FormattedDate; -- Result: '2025-03-05'

Example 11: Copilot got the use partially wrong given that each character except the pivoting character needs to be replaced accordingly. So for 6 characters one needs to replace the Stuff function 5 times!

Example 12:  Implementing custom GUID is a process more complex than this as one needs to take care of not generating duplicates.

Example 13: This is an example on how to handle changes dynamically.

Example 14: One should use the Trim function whenever possible, respectively the combination LTrim and RTrim, if Trim is not available (it was introduced in SQL 2017).

Example 15: The example is incorrect. One should consider the length of the string in the formula. 

Example 16:  One must know the values in advance, otherwise the example doesn't hold. Moreover, the same issue like in the first example occurs.

Example 17:  The example in not dynamic.

Example 18:  It's an interesting technique for generating "random" characters given that unique values are generated across a dataset.

Example 19: One can write error messages with multiple placeholders, though the Replace function is simpler to use. 

Example 20: It's easier to cast the value as datetime and apply the required formatting accordingly. Not testing whether the value is a date can lead to curious results.

I met some of the usages exemplified above, though I used the Stuff function seldom (see a previous post), when no other functions were available. Frankly, Copilot could prove to be a useful tool for learning SQL or other programming language in similar ways.

Happy coding!

Previous Post <<||>> Next Post 

💎SQL Reloaded: Pulling the Strings of SQL Server VIII (Insertions, Deletions and Replacements)

Until now, the operations with strings resumed to concatenation and its reverse operation(s) - extracting a substring or splitting a string into substrings. It was just the warm up! There are several other important operations that involve the internal manipulation of strings – insertion, deletion and replacement of a substring in a given string, operations performed using the Replace and Stuff functions.

Replace function, as its name denotes, replaces all occurrences of a specified string value with another. Several scenarios in which the function is quite useful: the replacement of delimiters, special characters, correcting misspelled words or any other chunks of text. Here are some basic simple examples, following to consider the before mentioned applications in other posts:

-- examples with replace 
DECLARE @str varchar(30) 
SET @str = 'this is a test string' 
SELECT replace(@str, ' ', ',') Example1 
, replace(@str, ' ', ' ') Example2 
, replace(@str, ' ', '') Example3 
, replace(@str, 'is', 'as') Example4  

Output:

Example1	Example2	Example3	Example4
this,is,a,test,string	this is a test string	thisisateststring	thas as a test string

When there are good chances that the searched string won’t appear in the “searched” string, and especially when additional logic is depending on the replacement, logic that could be included in the same expression with the replacement, then maybe it makes sense to check first if the searched character is present:

-- replacement with check 
DECLARE @str varchar(30) 
DECLARE @search varchar(30) 
DECLARE @replacememt varchar(30) 
SET @str = 'this is a test string' 
SET @search = 'this string' 
SET @replacememt = 'other string' 
SELECT CASE            
    WHEN CharIndex(@search, @str)>0 THEN Replace(@str, @search, @replacememt)             
    ELSE @str        
END result 

Unfortunately the function doesn’t have the flexibility of the homonym functions provided by the languages from the family of VB (VBScript, VB.NET), which allow to do the replacement starting with a given position, and/or for a given number of occurrences. This type of behavior could be obtained with a simple trick – splitting the string into two other strings, performing the replacement on the second string, and then concatenating the first string and the result of the replacement:

-- replacement starting with a given position 
DECLARE @str varchar(30) 
DECLARE @search varchar(30) 
DECLARE @replacememt varchar(30) 
DECLARE @start int 
SET @str = 'this is a test string' 
SET @search = 's' 
SET @replacememt = 'x' 
SET @start = 7 
SELECT Left(@str, @start-1) FirstPart 
, RIGHT(@str, Len(@str)-@start+1) SecondPart 
, CASE        
    WHEN @start <= LEN(@str) THEN Left(@str, @start-1) + Replace(RIGHT(@str, Len(@str)-@start+1), @search, @replacememt)        
     ELSE @str  
END Replacement 

Output:

FirstPart	SecondPart	Replacement
this i	s a test string	this ix a text xtring

The logic can be encapsulated in a function together with additional validation logic.

Stuff function inserts a string into another string starting with a given position and deleting a specified number of characters. Even if seldom used, the function it’s quite powerful allowing to insert a string in another, to remove a part of a string or more general, to replace a single occurrence of a string with another string, as can be seen from the below examples:
 

-- Stuff-based examples 
DECLARE @str varchar(30) 
SET @str = 'this is a test string' 
SELECT STUFF(@str, 6, 2, 'was ') Example1 
, STUFF(@str, 1, 0, 'and ') Example2 
, STUFF(@str, 1, 0, 'that') Example3 
, STUFF(@str, LEN(@str) + 1, 0, '!') Example4 

Output:

Example1	Example2	Example3	Example4
this was a test string	and this is a test string	thatthis is a test string	NULL

If in the first example is done a replacement of a text from a fix position, in the next examples are attempted insert on a first, middle respectively end position. As can be seen, the last example doesn’t work as expected, this because the insert position can’t go over the length of the target string. Actually, if the insert needs to be done at the beginning, respectively the end of a string, a concatenation can be much easier to use. A such example is the padding of strings with leading or trailing characters, typically in order to arrive to a given length. SQL Server doesn’t provide such a function, however the function is quite easy to build.
 

-- left/right padding 

DECLARE @str varchar(30) 
DECLARE @length int  
DECLARE @padchar varchar(1) 
SET @str = '12345'  
SET @length = 10 
SET @padchar = '0' 
SELECT @str StringToPad  
, CASE  
     WHEN LEN(@str)<@length THEN Replicate(@padchar, @length-LEN(@str)) + @str       
     ELSE @str  
END LeftPadding  
, CASE  
     WHEN LEN(@str)<@length THEN @str + Replicate(@padchar, @length-LEN(@str))      
     ELSE @str  
END RightPadding 

 
Output:

StringToPad	LeftPadding	RightPadding
12345	0000012345	1234500000

Note:
The queries work also in SQL databases in Microsoft Fabric.

Happy Coding!