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 ««12345»»»

STUFF Function Expand / Collapse
Author
Message
Posted Tuesday, May 17, 2011 5:18 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 6:16 PM
Points: 7,738, Visits: 9,487
Toreador (5/17/2011)
Koen Verbeeck (5/16/2011)
And the explanation doesn't mention why inserting NULL in another string doesn't result in NULL, but in an empty space instead.


That's what got me.
Surely it's a bug?
select 'Vinay, ' + NULL + ', Amit'
returns NULL, so I'd have thought
select STUFF('Vinay, Vijay, Amit',8,5,NULL)
should do the same?

That's exactly my reaction. The BoL page quoted states that the last expression is inserted in the other, and I'm pretty sure that sticking a NULL in the middle of a string should produce NULL. If it doesn't, I would regard it as a bug unless there were a clear statement in BoL that this operation on NULL was unlike any other in SQL (if there is such a statement, I haven't found it). I wonder if it's affected by the CONCAT_NULL_YIELDS_NULL setting?


Tom
Post #1110076
Posted Tuesday, May 17, 2011 5:24 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, February 6, 2014 4:15 AM
Points: 1,242, Visits: 1,546
Toreador (5/17/2011)
Koen Verbeeck (5/16/2011)
And the explanation doesn't mention why inserting NULL in another string doesn't result in NULL, but in an empty space instead.


That's what got me.
Surely it's a bug?
select 'Vinay, ' + NULL + ', Amit'
returns NULL, so I'd have thought
select STUFF('Vinay, Vijay, Amit',8,5,NULL)
should do the same?



Hi,

Few days ago, when i was playing with some strings, i found this . That time i also shocked. I feel that we all should know this. that's why i sumbit this question. i think it help us.


Thx
Vinay






Thanks
Vinay Kumar
-----------------------------------------------------------------
Keep Learning - Keep Growing !!!
www.GrowWithSql.com

Post #1110082
Posted Tuesday, May 17, 2011 5:30 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Thursday, June 19, 2014 8:00 AM
Points: 363, Visits: 1,425
Wow... that didn't work quite the way I'd expected it to! Nice question!

(And I'm really glad I checked my answer in SQL before actually answering the question!)


-Ki
Post #1110091
Posted Tuesday, May 17, 2011 5:44 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 6:16 PM
Points: 7,738, Visits: 9,487
Danny Ocean (5/17/2011)
Hi,

Few days ago, when i was playing with some strings, i found this . That time i also shocked. I feel that we all should know this. that's why i sumbit this question. i think it help us.


Thx
Vinay

That's a very good reason for posting the question. Thanks, Vinay.


Tom
Post #1110100
Posted Tuesday, May 17, 2011 6:08 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Yesterday @ 12:17 PM
Points: 1,837, Visits: 2,180
Another ticky tack problem. Sure, I said 2, because the results were the same, just in a different order. It was the last of the two matching result sets. (No order by clause, so order doesn't matter. )

By the way, what setting in SQL Server Management Studio must I have set that required me to put semicolons at the end of each command before they would run without error?


Please don't go. The drones need you. They look up to you.
Connect to me on LinkedIn
Post #1110117
Posted Tuesday, May 17, 2011 6:14 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, September 25, 2014 10:52 AM
Points: 1,393, Visits: 481
Nice question!
Post #1110123
Posted Tuesday, May 17, 2011 6:18 AM
Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Monday, April 8, 2013 8:13 AM
Points: 700, Visits: 100
Damn it.
I answered correctly, but there was 2 same answers and I choose the wrong one

Or is there some difference between those 2 answers?

1. Vinay, Anup, Amit 2. Vinay, , Amit 3. NULL
1. Vinay, Anup, Amit 3. NULL 2. Vinay, , Amit



Post #1110126
Posted Tuesday, May 17, 2011 6:23 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 6:16 PM
Points: 7,738, Visits: 9,487
I've submitted a connect item suggesting this behavious be changed.

Tom
Post #1110127
Posted Tuesday, May 17, 2011 6:37 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, September 8, 2014 6:15 PM
Points: 2,278, Visits: 3,793
Tom.Thomson (5/17/2011)
I've submitted a connect item suggesting this behavious be changed.


Thank you Tom


Mohammed Moinudheen
Post #1110137
Posted Tuesday, May 17, 2011 7:00 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, September 2, 2014 10:39 AM
Points: 1,194, Visits: 787
Good Question and nice to know about the bug in this function.
Post #1110169
« Prev Topic | Next Topic »

Add to briefcase ««12345»»»

Permissions Expand / Collapse