Back to Library

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

Error Handling:

  • Error: Returns NULL if any parameter is NULL

  • Recommendation: Validate position and length inputs

Supported Databases:

DBMS
Function / Syntax
Example
Behavior with NULL
MySQL

INSERT() 

INSERT(str,pos,len,newstr) 

Returns NULL

PostgreSQL

OVERLAY()

OVERLAY(str PLACING new FROM pos FOR len)

Returns NULL

SQL Server

STUFF() 

STUFF(str,pos,len,newstr)

Returns NULL

SQLite

REPLACE()

Custom Implementation

Returns NULL

BigQuery

REPLACE()

Custom Implementation

Returns NULL

Snowflake

REPLACE()

Custom Implementation

Returns NULL

Athena

REPLACE()

Custom Implementation

Returns NULL

Related Functions:

REVERSE()

Returns a string value in a reversed order (characters displayed in reverse order).

Returns a string value in a reversed order (characters displayed in reverse order).

Returns a string value in a reversed order (characters displayed in reverse order).

Returns a string value in a reversed order (characters displayed in reverse order).

STUFF()

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

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

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

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

PATINDEX()

Returns the starting position of the first occurrence of a pattern in a string. Uses pattern matching with wildcards.

Returns the starting position of the first occurrence of a pattern in a string. Uses pattern matching with wildcards.

Returns the starting position of the first occurrence of a pattern in a string. Uses pattern matching with wildcards.

Returns the starting position of the first occurrence of a pattern in a string. Uses pattern matching with wildcards.

RIGHT()

Extracts a specified number of characters from the end (right side) of a string.

Extracts a specified number of characters from the end (right side) of a string.

Extracts a specified number of characters from the end (right side) of a string.

Extracts a specified number of characters from the end (right side) of a string.

CHARINDEX()

Returns the starting position of a substring within a string. Position is 1-based (first character is position 1).

Returns the starting position of a substring within a string. Position is 1-based (first character is position 1).

Returns the starting position of a substring within a string. Position is 1-based (first character is position 1).

Returns the starting position of a substring within a string. Position is 1-based (first character is position 1).

LEFT()

Extracts a specified number of characters from the beginning (left side) of a string.

Extracts a specified number of characters from the beginning (left side) of a string.

Extracts a specified number of characters from the beginning (left side) of a string.

Extracts a specified number of characters from the beginning (left side) of a string.

LENGTH()/LEN()

Returns the number of characters in a string. Note: Function name varies by DBMS (LENGTH in most, LEN in SQL Server).

Returns the number of characters in a string. Note: Function name varies by DBMS (LENGTH in most, LEN in SQL Server).

Returns the number of characters in a string. Note: Function name varies by DBMS (LENGTH in most, LEN in SQL Server).

Returns the number of characters in a string. Note: Function name varies by DBMS (LENGTH in most, LEN in SQL Server).

TRIM()

Removes leading and/or trailing spaces (or specified characters) from a string.

Removes leading and/or trailing spaces (or specified characters) from a string.

Removes leading and/or trailing spaces (or specified characters) from a string.

Removes leading and/or trailing spaces (or specified characters) from a string.

LOWER()

Converts all characters in a string to lowercase letters.

Converts all characters in a string to lowercase letters.

Converts all characters in a string to lowercase letters.

Converts all characters in a string to lowercase letters.

UPPER()

Converts all characters in a string to uppercase letters.

Converts all characters in a string to uppercase letters.

Converts all characters in a string to uppercase letters.

Converts all characters in a string to uppercase letters.

One place for all your queries,
directly on your SQL editor