Sql-server – Behavior of STUFF function for empty strings

sql serverstring manipulation

When working on answering another question (on SO), I came across some unexpected (to me) behavior with the STUFF function.

SELECT STUFF
(
    'a', -- character expression
    1,  -- start
    1,  -- length
    'z' -- replaceWith_expression
)

returns NULL, while I would expect it to return 'z'.

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.

Syntax

STUFF ( character_expression , start , length , replaceWith_expression )

Remarks

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. 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.

Per the documentation, there are a number of reasons why a NULL string would be returned:

  • If start is longer than the first character_expression
  • If start is negative or 0
  • If length is negative

Best Answer

After closely reading the documentation while posting this question, I realized it told me the answer. I'm still posting it, since I hope it might be helpful to others in a similar predicament.

Per the documentation:

If start is negative or zero, a null string is returned. If start is longer than the first character_expression, a null string is returned. start can be of type bigint.

This means that, effectively, you can't use STUFF with an empty string (since any argument to start will result in a NULL string).

One workaround is to use a non-empty string, and delete the content. For the above example, you could use STUFF('a', 1, 1, 'z') to delete the initial character.

Related Question