How STUFF works!

I hope this helps people who want more explanation about this initially quite odd function

The whole of the RIGHT string will be INJECTED in to the LEFT string at the START position.

The LENGTH parameter indicates the number of characters TO OVERWRITE (delete before insertion) from the FIRST string starting at the character indicated by the START parameter.

(The START and LENGTH parameters take no interest in the RIGHT string)

IMPORTANT: The start position must be a character position in the FIRST string and as 0 is not a valid character position anything with a START of 0 will result in NULL to indicate failure.

NOTE ALSO: There is no way to append to the first string for the same reason. In the example the string length is 6 so 7 is not a valid character position in the left string.

This can be demonstrated well with the following

SELECT STUFF('123456', 0,3,'XXXXXX')

-- NULL - What were you expecting? Looking up character 0 in the left string returned nothing so SQL does not know what to do with the statement

SELECT STUFF('123456', 1,3,'XXXXXX')

-- First three chars deleted, then injection then remaining part of the left string "XXXXXX456"

SELECT STUFF('123456', 2,3,'XXXXXX')

-- Returns the left string upto the start position then deletes next three chars, new string inserted , then remainder of left string "1XXXXXX56"

SELECT STUFF('123456', 3,3,'XXXXXX')

-- Returns the left string upto the start position then deletes next three chars, new string inserted , then remainder of left string "12XXXXXX6"

SELECT STUFF('123456', 4,3,'XXXXXX') -- "123XXXXXX"

SELECT STUFF('123456', 5,3,'XXXXXX') -- "1234XXXXXX"

SELECT STUFF('123456', 6,3,'XXXXXX') -- "12345XXXXXX"

SELECT STUFF('123456', 7,3,'XXXXXX') -- Were you expecting "123456XXXXXX"? - Cannot insert at position 7 because left string does not have position 7

Note also that the current length of the string is the important factor and not the buffer size first declared, hence

declare @a varchar(8) = '12345678'

SET @a = '123456'

SELECT STUFF(@a, 7,3,'XXXXXX')

still returns NULL (because the string size is now 6 - not 8 chars)

So what happens if we put a NULL into the string?

SELECT STUFF('123456', 1,3,NULL) -- "456" - SQL has found the first character, removed it and two next to it (3 in total) and inserted nothing into the string (because NULL is nothing)

SELECT STUFF('123456', 2,3,NULL) -- "156" - SQL has found the second character, removed it and two next to it (3 in total) and inserted nothing into the string

SELECT STUFF('123456', 3,3,NULL) --

SELECT STUFF('123456', 4,3,NULL) -- "123" - SQL has found the fourth character, removed it and two next to it (3 in total) which is actually all of the left string and then inserted nothing into the string

SELECT STUFF('123456', 5,3,NULL) --

SELECT STUFF('123456', 6,3,NULL) --

So for this expression where @STR is 'ServeR' which is a 6 character string and @stx is null

SELECT STUFF(@str, 1,6,@stx) AS 'S-1'

SQL has found the first character 'S' and deleted it and the next 5 characters (6 in total) and then inserted nothing in to the string, leaving it as an empty string.

Remeber we cannot insert NULL into the string - inserting null just does nothing to the string that was there before (which is now empty, i.e. zero length string)

and for

SELECT STUFF(@str, 0,5,@stx) AS 'S-2'

the contents of @STR is irrelavant as it will not have a 0 position. S is the first char at position 1. The contect of @stx is also irrelavent as we are not going to return anything anyway.

All ofthese return NULL just because start is 0 ...

SELECT STUFF('123456', 0,5,NULL)

SELECT STUFF(NULL, 0,5,NULL)

SELECT STUFF('123456', 0,5,'XYZ')

Dave

David Bridge technology Limited

http://www.davidbridgetechnology.com