如何在SQL中使用該功能進行字符串操作？

STUFF函數用於刪除字符串中指定位置和長度的字符並插入新字符，1. 例如STUFF('123-456-7890', 5, 3, 'XXX')返回'123-XXX-7890'；2. 通過設置長度為0可實現插入，如STUFF('Hello World', 6, 0, 'Beautiful ')返回'Hello Beautiful World'；3. 使用空字符串替換可刪除字符，如STUFF('abcdef', 2, 3, '')返回'af'；4. 可嵌套使用進行複雜格式化，如為信用卡號加空格返回'1234 5678 9012 3456'，該函數基於1索引，參數為NULL或索引無效時返回NULL，適用於需按位置精確操作字符串的場景。

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;

Result: 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;

Result: 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;

Result: 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;

Result: 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 surgical 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.

以上是如何在SQL中使用該功能進行字符串操作？的詳細內容。更多資訊請關注PHP中文網其他相關文章！