Export (0) Print
Expand All
ABS
ALL
AND
ANY
AVG
bit
COS
COT
DAY
EXP
GO
IN
LEN
LOG
MAX
MIN
NOT
OR
PI
SET
SIN
STR
SUM
TAN
USE
VAR
Expand Minimize

STUFF

SQL Server 2000

Deletes a specified length of characters and inserts another set of characters at a specified starting point.

Syntax

STUFF ( character_expression , start , length , character_expression )

Arguments

character_expression

Is an expression of character data. character_expression can be a constant, variable, or column of either character or binary data.

start

Is an integer value that specifies the location to begin deletion and insertion. If start or length is negative, a null string is returned. If start is longer than the first character_expression, a null string is returned.

length

Is an integer that specifies the number of characters to delete. If length is longer than the first character_expression, deletion occurs up to the last character in the last character_expression.

Return Types

Returns character data if character_expression is one of the supported character data types. Returns binary data if character_expression is one of the supported binary data types.

Remarks

String functions can be nested.

Examples

This 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

Data Types

String Functions

Was this page helpful?
(1500 characters remaining)
Thank you for your feedback
Show:
© 2014 Microsoft