STUFF function [String]

Deletes a number of characters from one string and replaces them with another string.

STUFF ( string-expression1, start, length, string-expression2 )

string-expression1 The string to be modified by the STUFF function.

start The character position at which to begin deleting characters. The first character in the string is position 1.

length The number of characters to delete.

string-expression2 The string to be inserted. To delete a portion of a string using the STUFF function, use a replacement string of NULL.

The following statement returns the value “chocolate pie”:

SELECT STUFF( 'chocolate cake', 11, 4, 'pie' )
FROM iq_dummy

To delete a portion of a string using STUFF, use a replacement string of NULL. To insert a string using STUFF, use a length of zero.

The STUFF function will return a NULL result in the following situations: