STUFF - 2

Question

Post reply

STUFF - 2

Ron McCullough

SSC Guru

Points: 63877
More actions
December 8, 2012 at 11:15 am

#267894

Comments posted to this topic are about the item STUFF - 2
If everything seems to be going well, you have obviously overlooked something.
Ron

Please help us, help you -before posting a question please read[/url]
Before posting a performance problem please read[/url]
December 10, 2012 at 12:21 am

This was removed by the editor as SPAM

Viewing 15 posts - 1 through 15 (of 32 total)

You must be logged in to reply to this topic. Login to reply

Lokesh Vij SSChampion Points: 10836 More actions · Answer 1

Thank you for another good question Ron!

NULL value will also be returned in the below two conditions:

-- When starting position is negative

SELECT STUFF(@str, -1,5,@stx)

-- When starting position is greater than the expression length => LEN(@str)

SELECT STUFF(@str, 7,5,@stx)

~ Lokesh Vij

Guidelines for quicker answers on T-SQL question[/url]
Guidelines for answers on Performance questions

Link to my Blog Post --> www.SQLPathy.com[/url]

Follow me @Twitter

paul s-306273 SSChampion Points: 10730 More actions · Answer 2

Guess you have to know your stuff to get today's QOTD right...

kalyani.k478 Default port Points: 1400 More actions · Answer 3

kalyani.k478

Default port

Points: 1400

December 10, 2012 at 2:00 am

#1566929

Simple question to start on monday:)

Koen Verbeeck SSC Guru Points: 259105 More actions · Answer 4

Nice question, thanks.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

rhythm.varshney Ten Centuries Points: 1138 More actions · Answer 5

Nice question.However it will fetch error in SQL 2005 not sure about upper version as we can not assign value while declaration.

sipas Hall of Fame Points: 3828 More actions · Answer 6

More STUFFing - it must be nearly Christmas. Thanks for the question.

(Bob Brown) SSCrazy Points: 2705 More actions · Answer 7

(Bob Brown)

SSCrazy

Points: 2705

December 10, 2012 at 4:21 am

#1566970

Thanks for the great Monday morning question.

Toreador SSChampion Points: 11365 More actions · Answer 8

The explanation is fine for the second example.

But there's no mention of why a NULL value for 'replace with' returns a non-null result.

This confused me when I first came across it, and confuses me still!

mtassin SSC-Insane Points: 23099 More actions · Answer 9

rhythm.varshney (12/10/2012)
Nice question.However it will fetch error in SQL 2005 not sure about upper version as we can not assign value while declaration.

2005 has been off of mainstream support for over a year, not accounting for it anymore seems pretty standard.

--Mark Tassin
MCITP - SQL Server DBA
Proud member of the Anti-RBAR alliance.
For help with Performance click this link[/url]
For tips on how to post your problems[/url]

Mike Is Here Hall of Fame Points: 3348 More actions · Answer 10

rhythm.varshney (12/10/2012)
Nice question.However it will fetch error in SQL 2005 not sure about upper version as we can not assign value while declaration.

+1

DavidBridgeTechnology.com SSChasing Mays Points: 649 More actions · Answer 11

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

David Bridge
David Bridge Technology Limited
www.davidbridgetechnology.com

sknox SSChampion Points: 12388 More actions · Answer 12

Toreador (12/10/2012)
The explanation is fine for the second example.
But there's no mention of why a NULL value for 'replace with' returns a non-null result.
This confused me when I first came across it, and confuses me still!

+1 -- and there's no mention in the official documentation of how STUFF treats NULL 'replace with' parameters either.

Toreador SSChampion Points: 11365 More actions · Answer 13

DavidBridgeTechnology.com (12/10/2012)
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)

NULL is not nothing!

If your explanation were correct then the result of

select '456' + NULL

would be '456'. And it isn't, it's NULL (which is what you'd expect).