September 3, 2015 at 12:37 am
Good question. Thanks for sharing
September 3, 2015 at 12:56 am
This was removed by the editor as SPAM
September 3, 2015 at 4:06 am
Clever question. Thanks for sharing Naveen.
September 3, 2015 at 6:12 am
The question illustrates an important point. It pays to learn how the functions really work and not have to rely on BOL for everything. This is probably obvious to most people. Given some of the posts lately, its not obvious to everyone.
Nice question. Thanks.
September 3, 2015 at 7:26 am
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.
I would have thought a null string is '' and a null value shows as NULL
In this example it should have returned a null string but it returns NULL.
What is the difference (if any) between a "null string" and a "null value?"
September 3, 2015 at 7:32 am
The question is valid. The BOL reference is valid. The comment about understanding how a function works is valid. But the reason the result of the query is NULL is because @TrueValue is NULL so the function can't STUFF an unknown that has no known starting position with a value. This question doesn't really exhibit the behavior of returning a null string as explained in the BOL explanation of the "start" argument.
September 3, 2015 at 8:11 am
David Harder (9/3/2015)
The question is valid. The BOL reference is valid. The comment about understanding how a function works is valid. But the reason the result of the query is NULL is because @TrueValue is NULL so the function can't STUFF an unknown that has no known starting position with a value. This question doesn't really exhibit the behavior of returning a null string as explained in the BOL explanation of the "start" argument.
It looks to me like it's not null before the STUFF function. It was set to a zero-length string on the prior line. The STUFF function is referencing a starting position of 1. Since that's larger than the length of the string, it returns a null value.
September 3, 2015 at 8:50 am
marcia.j.wilson you have true. Here is a simple test:
DECLARE @TrueValue VARCHAR(10);
SET @TrueValue = '';
-----------------------------------------------------------------
SELECT @TrueValue as TrueValue, LEN( @TrueValue) as LenTrueValue
September 3, 2015 at 8:55 am
Very interesting. And if you change the second line to SET @TrueValue = ' '; then you get 123 as the output.
Definitely worth knowing.
-----
[font="Arial"]Knowledge is of two kinds. We know a subject ourselves or we know where we can find information upon it. --Samuel Johnson[/font]
September 3, 2015 at 8:57 am
David Harder (9/3/2015)
The question is valid. The BOL reference is valid. The comment about understanding how a function works is valid. But the reason the result of the query is NULL is because @TrueValue is NULL so the function can't STUFF an unknown that has no known starting position with a value. This question doesn't really exhibit the behavior of returning a null string as explained in the BOL explanation of the "start" argument.
It looks to me like it's not null before the STUFF function. It was set to a zero-length string on the prior line. The STUFF function is referencing a starting position of 1. Since that's larger than the length of the string, it returns a null value.
So in the BOL article: "null string" = "null value" and what I was thinking of as a "null string" ('') is actually a "zero-length string?"
September 3, 2015 at 9:19 am
Naveen, a very good question. I played with this query and this is what I come up.
When you set @TrueValue = '' --- No space you get Null because there is no character that is there is no 1st position.
When you set @TrueValue = ' ' ---- There is one space character so it pick that place and stuff with value of '123'
When you set @TrueValue = NULL, you get NULL, again same as setting '' value, so you get NULL
:hehe:
September 3, 2015 at 9:37 am
Did anyone try this on SQL Server 2014? I compared the BOL quote in the explanation and the one for 2008R2 and it is different.
SQL Server 2014, SQL Server 2016 Preview:
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.
SQL Server 2008 R2:
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 length to delete is longer than the first string, it is deleted to the first character in the first string.
EDIT: never mind. The difference doesn't apply to the example.
September 3, 2015 at 9:55 am
kumar1pr (9/3/2015)
Naveen, a very good question. I played with this query and this is what I come up.When you set @TrueValue = '' --- No space you get Null because there is no character that is there is no 1st position.
When you set @TrueValue = ' ' ---- There is one space character so it pick that place and stuff with value of '123'
When you set @TrueValue = NULL, you get NULL, again same as setting '' value, so you get NULL
:hehe:
You are right
Thanks,
Naveen.
Every thought is a cause and every condition an effect
September 3, 2015 at 9:57 am
timwell (9/3/2015)
David Harder (9/3/2015)
The question is valid. The BOL reference is valid. The comment about understanding how a function works is valid. But the reason the result of the query is NULL is because @TrueValue is NULL so the function can't STUFF an unknown that has no known starting position with a value. This question doesn't really exhibit the behavior of returning a null string as explained in the BOL explanation of the "start" argument.
It looks to me like it's not null before the STUFF function. It was set to a zero-length string on the prior line. The STUFF function is referencing a starting position of 1. Since that's larger than the length of the string, it returns a null value.
So in the BOL article: "null string" = "null value" and what I was thinking of as a "null string" ('') is actually a "zero-length string?"
That is correct. In other words, zero length string means 'blank' value.
Thanks,
Naveen.
Every thought is a cause and every condition an effect
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply