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

适用对象: 是SQL Server 是Azure SQL 数据库 是Azure Synapse Analytics (SQL DW) 是并行数据仓库 APPLIES TO: yesSQL Server yesAzure SQL Database yesAzure Synapse Analytics (SQL DW) 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 为负或为零,则返回空字符串 。If start is negative or zero, a null string is returned. 如果 start 的长度大于第一个 character_expression,则返回空字符串 。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 为负,则返回空字符串 。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 为零,则插入在 start 位置发生,并且不会删除任何字符。If length is zero, insertion occurs at start location and no characters are deleted. length 的类型可以是 bigint 。length can be of type bigint.

replaceWith_expression replaceWith_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 开始替换 length 个字符的 character_expression 。This expression replaces length characters of character_expression beginning at start. 如果 replaceWith_expression 为 NULL,则在不插入任何内容的情况下删除字符 。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

如果开始位置或长度值是负数,或者开始位置大于第一个字符串的长度,则返回 Null 字符串。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.

补充字符(代理项对)Supplementary Characters (Surrogate Pairs)

使用 SC 排序规则时,character_expression 和 replaceWith_expression 都可以包含代理项对 。When using SC collations, both character_expression and replaceWith_expression can include surrogate pairs. length 参数将 character_expression 中的每个代理项计为一个字符 。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)