STUFF() in SQL

Deletes a specified length of characters and inserts another string at a specified start position in a string.

Syntax

STUFF(original_string, start_position, length, insert_string)

Return type

VARCHAR (or equivalent string type in the respective DBMS)

STUFF() Function Example

SELECT STUFF('Hello World', 1, 5, 'Hi');
-- Output: 'Hi World'

What is STUFF() in SQL?

The STUFF() function in SQL is used to delete a specified number of characters from a string and insert another string at a given position. It is commonly used for modifying text dynamically, such as replacing sections of a string or inserting new content within existing text. STUFF() is primarily supported in SQL Server and follows a 1-based index system, meaning the first character starts at position 1. If the specified start position is invalid, or if the delete length exceeds the string length, the function may return NULL. It is often used in data cleansing, formatting outputs, and dynamically constructing strings.

Parameters:

original_string: The string to be modified
start_position: Position where to start deletion
length: Number of characters to delete
insert_string: String to insert at start_position

Example Use Cases:

-- Insert middle initial

SELECT STUFF(full_name, 6, 0, 'M. ') FROM employees;
-- Output:
full_name     name_with_middle
-----------   ----------------
James Smith   James M. Smith
Mary Jones    Mary M. Jones 
David Lee     David M. Lee
Sarah Chen    Sarah M. Chen
John Davis    John M. Davis

-- Mask credit card numbers

SELECT STUFF(card_number, 1, 12, 'XXXXXXXXXXXX') AS masked_card;

-- Output:
card_number         masked_card
-----------------  -----------------
4532987612345678   XXXXXXXXXXXX5678
5467321098765432   XXXXXXXXXXXX5432
6011876543219876   XXXXXXXXXXXX9876
3785123456789012   XXXXXXXXXXXX9012
4024007198765432   XXXXXXXXXXXX5432

-- Combine area code

SELECT STUFF(phone, 1, 0, '+1-') AS formatted_phone;
-- Output:
phone           formatted_phone
--------------  ----------------
555-123-4567    +1-555-123-4567
444-987-6543    +1-444-987-6543
777-654-3210    +1-777-654-3210
888-234-5678    +1-888-234-5678
999-876-5432    +1-999-876-5432

Notes:

Behavior:Deletes specified characters and then inserts a new string; if the length is 0, it just inserts without deletion, and the position uses 1-based indexing.
Performance Considerations: Efficient for single-pass string modifications
Version Info: Primarily SQL Server function
Deprecated/Recommended Alternatives: CONCAT with SUBSTRING for other DBMS