DEV Community

Cover image for Working with the SQL STUFF Function in SQL Server
DbVisualizer
DbVisualizer

Posted on

Working with the SQL STUFF Function in SQL Server

#sqlserver

Manipulating strings in SQL Server often involves cleaning data or formatting results. The STUFF function offers a simple yet flexible way to do that — by removing characters and inserting a new string in their place.

STUFF is exclusive to Transact-SQL but commonly used by developers for tasks like merging text or masking sensitive data. Let’s break down how it works and see some practical examples.

Understanding the Syntax 

STUFF(original_string, start, length, replace_string)
Enter fullscreen mode Exit fullscreen mode
  • Removes length characters from original_string starting at start.
  • Inserts replace_string at that position.
  • Returns NULL if parameters are invalid or input is NULL.

Examples

Basic Replacement 

SELECT STUFF('Hello###World!', 6, 3, ', ');
-- Output: Hello, World!
Enter fullscreen mode Exit fullscreen mode

Deleting Characters 

SELECT STUFF('Hello###World!', 6, 3, NULL);
-- Output: HelloWorld!
Enter fullscreen mode Exit fullscreen mode

Add Text Without Deletion 

SELECT STUFF('Hello###World!', 6, 0, '@@@');
-- Output: Hello@@@###World!
Enter fullscreen mode Exit fullscreen mode

Real-World Use Cases

Combine Values 

SELECT STUFF(
  (SELECT ', ' + Surname FROM Employee FOR XML PATH('')),
  1, 2, ''
);
Enter fullscreen mode Exit fullscreen mode

Concatenates multiple rows and removes the leading comma.

Mask Emails 

SELECT Id,
       STUFF(Email, 2, CHARINDEX('@', Email) - 3, '*****') AS MaskedEmail
FROM Employee;
Enter fullscreen mode Exit fullscreen mode

Covers up email parts for privacy.

Best Practices

  • Validate parameters (start > 0, length >= 0).
  • Handle NULL values with COALESCE.
  • Keep performance in mind for large text fields.
  • Add comments when logic is complex.

FAQ

Is STUFF available in all databases?

No, it’s exclusive to SQL Server.

Can I use it on numbers?

Yes, numbers are treated as strings.

Why pair it with FOR XML PATH?

To clean up delimiters after joining text rows.

What about NULL handling?

A NULL input returns NULL; NULL replacement deletes characters only.

Conclusion

STUFF gives SQL Server users precise control over strings — from inserting and removing characters to formatting and data masking. It’s efficient, easy to read, and particularly powerful when used alongside XML or string aggregation queries.

Mastering it can make text manipulation much cleaner and faster. To explore additional examples, read SQL STUFF: Insert a String Into Another in SQL Server article.

Top comments (0)