How to use the STUFF function for string manipulation in SQL?

STUFF function is used to delete characters with specified positions and lengths in a string and insert new characters, 1. For example, STUFF('123-456-7890', 5, 3, 'XXX') returns '123-XXX-7890'; 2. Insert can be achieved by setting the length to 0, such as STUFF('Hello World', 6, 0, 'Beautiful ') returns 'Hello Beautiful World'; 3. Replace empty strings to delete characters, such as STUFF('abcdef', 2, 3, '') returns 'af'; 4. Can be nested for complex formatting, such as adding spaces to credit card numbers to return '1234 5678 9012 3456', this function is based on 1 index, and returns NULL when the parameter is NULL or the index is invalid. It is suitable for scenarios where strings need to be operated accurately by position.

The STUFF function in SQL is a powerful string manipulation tool that allows you to delete a specified number of characters from a string and then insert another set of characters at a given position . It's particularly useful when you need precise control over modifying parts of a string.

Syntax of STUFF

 STUFF(input_string, start, length, replacement_string)

• input_string : The original string you want to modify.
• start : The position (1-based index) where deletion begins.
• length : The number of characters to delete from the start position.
• replacement_string : The string to insert at the start position after deletion.

⚠️ If start or length is NULL, STUFF returns NULL. Also, positions are 1-indexed.

1. Basic Example: Replacing Characters

Suppose you have a phone number and want to mask part of it:

 SELECT STUFF('123-456-7890', 5, 3, 'XXX') AS ModifiedNumber;

Results: 123-XXX-7890

• Starts at position 5 ( '4' in '123-456-7890' )
• Deletes 3 characters ( '456' )
• Inserts 'XXX' in their place

This is commonly used for data anonymization or formatting.

2. Inserting Text Without Deleting

You can use STUFF to insert text by setting length to 0:

 SELECT STUFF('Hello World', 6, 0, 'Beautiful ') AS InsertedText;

Results: Hello Beautiful World

• Starts at position 6 (just before 'W' )
• Deletes 0 characters
• Inserts 'Beautiful '

This is a clever way to insert text at a specific point without removing anything.

3. Removing Characters (Delete Without Inserting)

To delete part of a string without inserting anything, use an empty string as the replacement:

 SELECT STUFF('abcdef', 2, 3, '') AS RemovedChars;

Results: af

• Starts at position 2 ( 'b' )
• Deletes 3 characters ( 'bcd' )
• Inserts nothing

Useful for cleaning up unwanted substrings when you know their position.

4. Practical Use Case: Formatting Credit Card Numbers

Imagine you want to format a 16-digit card number with spaces every 4 digits:

 DECLARE @CardNumber VARCHAR(16) = '1234567890123456';

SELECT 
    STUFF(STUFF(STUFF(@CardNumber, 5, 0, ' '), 10, 0, ' '), 15, 0, ' ') AS FormattedCard;

Results: 1234 5678 9012 3456

• Each STUFF inserts a space at positions 5, 10, and 15
• Since length = 0 , no characters are deleted

This shows how STUFF can be nested for complex formatting.

Key Notes and Tips

• Position is 1-based , not 0-based like many programming languages.
• If start is greater than the length of the string, STUFF returns NULL.
• If start is 0, it also returns NULL — always use valid positions.
• If length exceeds available characters from start , it removes up to the end.
• STUFF works only on string types ( VARCHAR , NVARCHAR , CHAR , etc.)

Common Pitfalls

• ❌ Using 0-based indexing: STUFF('abc', 0, 1, 'x') → returns NULL
• ❌ Invalid lengths: STUFF('abc', 2, -1, 'x') → invalid, length must be ≥ 0
• ❌ Forgetting that replacement happens after deletion, not before

In short, STUFF gives you scientific control over string modification — delete and insert in one step. While REPLACE is good for known substrings, STUFF shines when you know the position and length of the part you want to change.

Basically, if you need to splice strings by position, STUFF is your go-to function in SQL Server.

The above is the detailed content of How to use the STUFF function for string manipulation in SQL?. For more information, please follow other related articles on the PHP Chinese website!