Export (0) Print
Expand All

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 that can be implicitly converted to nvarchar or ntext.
start
Is an integer value, or an expression that can be implicitly converted to int, that specifies the location to begin deletion and insertion. If start 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, or an expression that can be implicitly converted to int, 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. If length is negative, a null string is returned.
Return Types

nvarchar or ntext

Examples

The following example deletes the second digit of the ProductID in the Products table in the Northwind database and replaces it with 000.

SELECT STUFF(ProductID, 2,1, '000')
FROM Products

Show:
© 2014 Microsoft