STUFF (SQL Server Compact)

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
    An expression that can be implicitly converted to nvarcharor ntext.

  • start
    An integer value or an expression that can be implicitly converted to int, which 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
    An integer or an expression that can be implicitly converted to int, which 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 Value

nvarchar or ntext

Example

The following example deletes the second digit of the product ID in the Productstable and replaces it with the characters '000'.

SELECT STUFF([Product ID], 2,1, '000')
FROM Products