Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««1234»»»

STUFF - 2 Expand / Collapse
Author
Message
Posted Monday, December 10, 2012 7:32 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Friday, September 12, 2014 8:38 AM
Points: 3,675, Visits: 72,434
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
For tips on how to post your problems
Post #1394616
Posted Monday, December 10, 2012 7:45 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, September 16, 2014 6:11 AM
Points: 1,393, Visits: 480
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
Post #1394628
Posted Monday, December 10, 2012 7:52 AM


Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, September 18, 2014 7:18 AM
Points: 58, Visits: 214
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
www.davidbridgetechnology.com




David Bridge
David Bridge Technology Limited
www.davidbridgetechnology.com
Post #1394632
Posted Monday, December 10, 2012 7:53 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 8:27 AM
Points: 1,327, Visits: 1,695
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.
Post #1394635
Posted Monday, December 10, 2012 8:00 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Friday, August 29, 2014 10:20 AM
Points: 1,740, Visits: 6,366
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).
Post #1394637
Posted Monday, December 10, 2012 8:26 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 8:27 AM
Points: 1,327, Visits: 1,695
Toreador (12/10/2012)
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).


Actually, nothing is perhaps the best characterization of NULL we have. But the mistake is that nothing is not the same thing as an empty string. An empty string has a type of string, a length of zero and data consisting of zero characters. It is a definitive statement of zero characters, just like the integer 0 is not nothing, but a definitive statement of the number zero.

NULL is truly nothing: no data, no type, no reason.

Consider carts of apples. Like a cart of apples, a string is a collection of things (characters). So when you have a cart of apples, and you add a cart with no apples (empty string) you end up with the same cart of apples as before. But if you have a cart of apples, and introduce a vacuum (nothing), weird things happen. So SQL Server doesn't generally allow combining or comparing things with nothing, to prevent upsetting the apple carts.

But STUFF is one of those odd cases: it was introduced primarily to make things easier on code monkeys. We wanted a way to "stuff" information into otherwise generic strings like error messages without worring about complex things like NULL elimination or type conversion. So it allows weird things to happen, unless you break its own weird rules like requiring the indexes to relate to actual positions in the string.
Post #1394650
Posted Monday, December 10, 2012 8:34 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 8:38 AM
Points: 7,700, Visits: 9,428
It's an interesting question,

But it also brings up a rather nasty fact about the language - a gaping whole in the whole idea that NULL is NULL, not somethink like 0 or an empty string or a mindnight at the end of December 31st 1899 according to what type null has. I agree with Toreador:
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!

Of course, I suspect he's exagerating a little when he says it confuses him still - it seems far more likely that it merely irritates him now, that he has a "bitten once I won't be caught again" attitude to it.

The explanation gives no hint as to why the second answer is what it is.

Of course people get it right, either because they've been messed up by this before (probably only those of us who have done much text-manipulation in SQL, which probably won't be many - too many people have been taught that anything like that is violating some professor or other's imaginary basic principles of modularity) or because they run the code to see what happens.

In fact a null as the last parameter of stuff is always treated as a zero length string, not as a null, whether it's being appended to an initial (proper) substring of the original string, being prepended to a final (not neccessarily proper) substring of the original string, being interposed between non-intersecting initial and final substrings of the original string, or just replacing the whole of the original string. I can't see the point of having this definition (and since this silly mess takes a lot of coding round, I could see a lot of point in treating NULL as NULL always instead). It appears to conflict quite heavily with CONCAT NULL = NULL which is true by default in SQL Server (ANSI null handling). It is nowhere (as far as I can tell, apart from occassional screams of pain on the web) documented. There doesn't seem to be any point to it (except, perhaps, to hand ammunition to the anti-null fanatic fundamentalists).


Tom
Post #1394656
Posted Monday, December 10, 2012 8:35 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Today @ 11:45 AM
Points: 530, Visits: 572

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


This is what threw me. I expected STUFF to act like the addition operator since there was no mention of the behavior on MSDN. They should probably add this quarkiness to the documentation.

Without testing, how could one know.


Aigle de Guerre!
Post #1394659
Posted Monday, December 10, 2012 11:21 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Today @ 12:16 PM
Points: 17,643, Visits: 15,497
Thanks Ron



Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp
Post #1394720
Posted Monday, December 10, 2012 1:12 PM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Monday, September 15, 2014 11:54 AM
Points: 4,107, Visits: 3,423
Ron's QotDs are always interesting. Thanks, Ron!
Post #1394765
« Prev Topic | Next Topic »

Add to briefcase ««1234»»»

Permissions Expand / Collapse