STUFF (Transact-SQL)STUFF (Transact-SQL)

適用於: 是SQL Server (從 2008 開始) 是Azure SQL Database 是Azure SQL 資料倉儲 是平行處理資料倉儲 APPLIES TO: yesSQL Server (starting with 2008) yesAzure SQL Database yesAzure SQL Data Warehouse yesParallel Data Warehouse

STUFF 函數會將字串插入另一個字串。The STUFF function inserts a string into another string. 它會在第一個字串的開始位置刪除指定長度的字元,然後將第二個字串插入第一個字串的開始位置。It deletes a specified length of characters in the first string at the start position and then inserts the second string into the first string at the start position.

主題連結圖示 Transact-SQL 語法慣例Topic link icon Transact-SQL Syntax Conventions

語法Syntax

STUFF ( character_expression , start , length , replaceWith_expression )  

引數Arguments

character_expressioncharacter_expression
這是字元資料的運算式Is an expression of character data. character_expression 可以是字元或二進位資料的常數、變數或資料行。character_expression can be a constant, variable, or column of either character or binary data.

startstart
這是一個開始刪除和插入之位置的整數值。Is an integer value that specifies the location to start deletion and insertion. 如果 start 是負數或零,則會傳回 Null 字串。If start is negative or zero, a null string is returned. 如果 start 長度超出第一個 character_expression,則會傳回 Null 字串。If start is longer than the first character_expression, a null string is returned. start 可以是 bigint 類型。start can be of type bigint.

lengthlength
這是一個整數,指定要刪除的字元數。Is an integer that specifies the number of characters to delete. 如果 length 是負數,則會傳回 Null 字串。If length is negative, a null string is returned. 如果 length 長度超出第一個 character_expression,則刪除動作就會進行到最後一個 character_expression的最後一個字元。If length is longer than the first character_expression, deletion occurs up to the last character in the last character_expression. 如果 length 為零,則會在字串中的第一個字元之前進行插入。If length is zero, insertion occurs before the first character in the string. length 可以是 bigint 類型。length can be of type bigint.

replaceWith_expressionreplaceWith_expression
這是字元資料的運算式Is an expression of character data. character_expression 可以是字元或二進位資料的常數、變數或資料行。character_expression can be a constant, variable, or column of either character or binary data. 這個運算式會從 start 開始取代 character_expressionlength 字元。This expression replaces length characters of character_expression beginning at start. 提供 NULL 當做 replaceWith_expression,移除字元且不插入任何內容。Providing NULL as the replaceWith_expression, removes characters without inserting anything.

傳回類型Return Types

如果 character_expression 是其中一個支援的字元資料類型,就會傳回字元資料。Returns character data if character_expression is one of the supported character data types. 如果 character_expression 是其中一個支援的二進位資料類型,就會傳回二進位資料。Returns binary data if character_expression is one of the supported binary data types.

RemarksRemarks

如果開始位置或長度是負的,或如果開始位置大於第一個字串的長度,則會傳回空的字串。If the start position or the length is negative, or if the starting position is larger than length of the first string, a null string is returned. 如果開始位置是 0,則會傳回 null 值。If the start position is 0, a null value is returned. 如果刪除的長度大於第一個字串,則會刪除到剩下第一個字串中的第一個字元。If the length to delete is longer than the first string, it is deleted to the first character in the first string.

如果產生的值大於傳回類型所支援的最大值,便會引發錯誤。An error is raised if the resulting value is larger than the maximum supported by the return type.

補充字元 (Surrogate 字組)Supplementary Characters (Surrogate Pairs)

當使用 SC 定序時,character_expressionreplaceWith_expression 都可以包含代理字組。When using SC collations, both character_expression and replaceWith_expression can include surrogate pairs. 長度參數會將 character_expression 中的每個 Surrogate 計算為單一字元。The length parameter counts each surrogate in character_expression as a single character.

範例Examples

下列範例會傳回從第一個字串 (abcdef) 中,從位置 2 (b) 開始,刪除三個字元所建立的字元字串,且會在刪除點插入第二個字串。The following example returns a character string created by deleting three characters from the first string, abcdef, starting at position 2, at b, and inserting the second string at the deletion point.

SELECT STUFF('abcdef', 2, 3, 'ijklmn');  
GO  

以下為結果集:Here is the result set.

---------   
aijklmnef   
  
(1 row(s) affected)  

另請參閱See Also

CONCAT (Transact-SQL)CONCAT (Transact-SQL)
CONCAT_WS (Transact-SQL)CONCAT_WS (Transact-SQL)
FORMATMESSAGE (Transact-SQL)FORMATMESSAGE (Transact-SQL)
QUOTENAME (Transact-SQL)QUOTENAME (Transact-SQL)
REPLACE (Transact-SQL)REPLACE (Transact-SQL)
REVERSE (Transact-SQL)REVERSE (Transact-SQL)
STRING_AGG (Transact-SQL)STRING_AGG (Transact-SQL)
STRING_ESCAPE (Transact-SQL)STRING_ESCAPE (Transact-SQL)
TRANSLATE (Transact-SQL)TRANSLATE (Transact-SQL)
資料類型 (Transact-SQL) Data Types (Transact-SQL)
字串函數 (Transact-SQL)String Functions (Transact-SQL)