March 16, 2016 at 7:40 pm
Lynn Pettis (8/6/2012)
JasonRowland (8/6/2012)
Oracle converts an empty string to null. If I could choose how SQL Server handled it, my 1st preference would be a type conversion error. 2nd would be the way Oracle does it, but to say '' = 0 is just not right.SQL Server Query
select cast('' as int)
-----------
0
Oracle Query
SELECT TO_NUMBER('') FROM dual;
----------
(null)
An empty string is not null, it is a known value (empty). Also, although Oracle currently treats the empty string as null, this behaviour may change in a future version of the product.
How oracle treats an empty string bit me a while back while working with Oracle. I found the reference regarding how Oracle treats an empty string while researching why it didn't work the way I was used to it (SQL Server). I prefer the way SQL Server handles it.
I strongly agree. NULL is not NOTHING. NOTHING is a known condition. NULL is an unknown condition. People frequently try to equate the two and THAT's just wrong. 😀
--Jeff Moden
Change is inevitable... Change for the better is not.
March 16, 2016 at 7:45 pm
Luis Cazares (8/7/2012)
JasonRowland (8/7/2012)
Luis Cazares (8/7/2012)
You might find this article interesting.http://msdn.microsoft.com/en-us/library/ms190309.aspx
So you won't have to wait for Celko to "do some digging". Here are the exact rules for implicit conversion in SQL Server. It's not a T-SQL error, it's just the way it's designed.
I understand the data type precedence hierarchy list, but that still doesn't explain why '' = 0.
Another point of interest is that this occurs for every numeric data type except decimal.
Then the part on implicit conversions in the following link might help.
http://msdn.microsoft.com/en-us/library/ms187928.aspx
Trying to explain '' = 0 could be easier if you see them both as empty not null values for both varchar and int. Have you tried an implicit conversion from 0 or '' to datetime?
Agreed. All of those are cases of NOTHING = NOTHING. Datetime is an exception to the concept of NOTHING because there is no NOTHING for a DATETIME... only UNKNOWN or "illegal value".
--Jeff Moden
Change is inevitable... Change for the better is not.
March 16, 2016 at 9:01 pm
Jeff Moden (3/16/2016)
Lynn Pettis (8/6/2012)
An empty string is not null, it is a known value (empty).I strongly agree. NULL is not NOTHING. NOTHING is a known condition. NULL is an unknown condition. People frequently try to equate the two and THAT's just wrong. 😀
I strongly disagree! 😀
We're talking here not just about an empty string, we're talking about an empty string representing a number.
There is no "NOTHING number". "Nothing" in terms of numbers can have only one meaning - it's missing, undefined.
We use to represent such a condition with NULL.
I had to specifically program my way around those conversions when importing data from a file.
You must have a way to distinguish between 0 Qty values imported from "... ,0, ..." and from "... ,, ...".
Once it's imported you have no way to tell the difference.
So, you need to import into a staging table with all varchar columns, and after that use NULLIF to correctly deal with missing numbers.
When you export data using bcp - what numeric value in a table will give you an empty string like this "... ,, ..." in a file?
Right, NULL. Nothing else.
So, to me, it would be logical if a reverse implicit conversion would work the same way.
Definitely not like this:
declare @MyVarChar varchar(12) = '';
declare @MyVarcharInt int;
declare @MyVarCharReverse varchar(12);
set @MyVarcharInt = @MyVarChar
set @MyVarCharReverse = @MyVarcharInt
select @MyVarChar MyVarchar, @MyVarcharInt MyVarcharInt, @MyVarCharReverse MyVarCharReverse
Results:
MyVarchar MyVarcharInt MyVarCharReverse
------------ ------------ ----------------
0 0
_____________
Code for TallyGenerator
March 16, 2016 at 9:10 pm
Sergiy (3/16/2016)
Jeff Moden (3/16/2016)
Lynn Pettis (8/6/2012)
An empty string is not null, it is a known value (empty).I strongly agree. NULL is not NOTHING. NOTHING is a known condition. NULL is an unknown condition. People frequently try to equate the two and THAT's just wrong. 😀
I strongly disagree! 😀
We're talking here not just about an empty string, we're talking about an empty string representing a number.
There is no "NOTHING number". "Nothing" in terms of numbers can have only one meaning - it's missing, undefined.
We use to represent such a condition with NULL.
I had to specifically program my way around those conversions when importing data from a file.
You must have a way to distinguish between 0 Qty values imported from "... ,0, ..." and from "... ,, ...".
Once it's imported you have no way to tell the difference.
So, you need to import into a staging table with all varchar columns, and after that use NULLIF to correctly deal with missing numbers.
When you export data using bcp - what numeric value in a table will give you an empty string like this "... ,, ..." in a file?
Right, NULL. Nothing else.
So, to me, it would be logical if a reverse implicit conversion would work the same way.
Definitely not like this:
declare @MyVarChar varchar(12) = '';declare @MyVarcharInt int;
declare @MyVarCharReverse varchar(12);
set @MyVarcharInt = @MyVarChar
set @MyVarCharReverse = @MyVarcharInt
select @MyVarChar MyVarchar, @MyVarcharInt MyVarcharInt, @MyVarCharReverse MyVarCharReverse
Results:
MyVarchar MyVarcharInt MyVarCharReverse
------------ ------------ ----------------
0 0
Ah! Yeah... I see your point there for empty strings. Unless someone defines what they mean for a given column, they have the same amount of information as a NULL ... UNKNOWN. Thanks, Sergiy.
--Jeff Moden
Change is inevitable... Change for the better is not.
March 17, 2016 at 1:52 pm
Jeff Moden (3/16/2016)
Sergiy (3/16/2016)
Jeff Moden (3/16/2016)
Lynn Pettis (8/6/2012)
An empty string is not null, it is a known value (empty).I strongly agree. NULL is not NOTHING. NOTHING is a known condition. NULL is an unknown condition. People frequently try to equate the two and THAT's just wrong. 😀
I strongly disagree! 😀
We're talking here not just about an empty string, we're talking about an empty string representing a number.
There is no "NOTHING number". "Nothing" in terms of numbers can have only one meaning - it's missing, undefined.
We use to represent such a condition with NULL.
I had to specifically program my way around those conversions when importing data from a file.
You must have a way to distinguish between 0 Qty values imported from "... ,0, ..." and from "... ,, ...".
Once it's imported you have no way to tell the difference.
So, you need to import into a staging table with all varchar columns, and after that use NULLIF to correctly deal with missing numbers.
When you export data using bcp - what numeric value in a table will give you an empty string like this "... ,, ..." in a file?
Right, NULL. Nothing else.
So, to me, it would be logical if a reverse implicit conversion would work the same way.
Definitely not like this:
declare @MyVarChar varchar(12) = '';declare @MyVarcharInt int;
declare @MyVarCharReverse varchar(12);
set @MyVarcharInt = @MyVarChar
set @MyVarCharReverse = @MyVarcharInt
select @MyVarChar MyVarchar, @MyVarcharInt MyVarcharInt, @MyVarCharReverse MyVarCharReverse
Results:
MyVarchar MyVarcharInt MyVarCharReverse
------------ ------------ ----------------
0 0
Ah! Yeah... I see your point there for empty strings. Unless someone defines what they mean for a given column, they have the same amount of information as a NULL ... UNKNOWN. Thanks, Sergiy.
I don't see that. A middle name of '' should mean the person has no middle name (and that such fact is known); a middle name of NULL means it's unknown. Those are not the same thing.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
March 17, 2016 at 2:22 pm
ScottPletcher (3/17/2016)
Jeff Moden (3/16/2016)
Sergiy (3/16/2016)
Jeff Moden (3/16/2016)
Lynn Pettis (8/6/2012)
An empty string is not null, it is a known value (empty).I strongly agree. NULL is not NOTHING. NOTHING is a known condition. NULL is an unknown condition. People frequently try to equate the two and THAT's just wrong. 😀
I strongly disagree! 😀
We're talking here not just about an empty string, we're talking about an empty string representing a number.
There is no "NOTHING number". "Nothing" in terms of numbers can have only one meaning - it's missing, undefined.
We use to represent such a condition with NULL.
I had to specifically program my way around those conversions when importing data from a file.
You must have a way to distinguish between 0 Qty values imported from "... ,0, ..." and from "... ,, ...".
Once it's imported you have no way to tell the difference.
So, you need to import into a staging table with all varchar columns, and after that use NULLIF to correctly deal with missing numbers.
When you export data using bcp - what numeric value in a table will give you an empty string like this "... ,, ..." in a file?
Right, NULL. Nothing else.
So, to me, it would be logical if a reverse implicit conversion would work the same way.
Definitely not like this:
declare @MyVarChar varchar(12) = '';declare @MyVarcharInt int;
declare @MyVarCharReverse varchar(12);
set @MyVarcharInt = @MyVarChar
set @MyVarCharReverse = @MyVarcharInt
select @MyVarChar MyVarchar, @MyVarcharInt MyVarcharInt, @MyVarCharReverse MyVarCharReverse
Results:
MyVarchar MyVarcharInt MyVarCharReverse
------------ ------------ ----------------
0 0
Ah! Yeah... I see your point there for empty strings. Unless someone defines what they mean for a given column, they have the same amount of information as a NULL ... UNKNOWN. Thanks, Sergiy.
I don't see that. A middle name of '' should mean the person has no middle name (and that such fact is known); a middle name of NULL means it's unknown. Those are not the same thing.
I guess you missed the part where it says that the empty strings come from a file.
There's no NULL in a csv. In some cases, an empty value should be an empty string and in others it should be a NULL value. Someone should define what does that empty value should represent to store the correct value in the database.
March 17, 2016 at 8:22 pm
ScottPletcher (3/17/2016)
I don't see that. A middle name of '' should mean the person has no middle name (and that such fact is known); a middle name of NULL means it's unknown. Those are not the same thing.
You missed the part "string representing a number".
Quantity of '' means - what number?
_____________
Code for TallyGenerator
March 18, 2016 at 6:08 pm
Luis Cazares (3/17/2016)
ScottPletcher (3/17/2016)
Jeff Moden (3/16/2016)
Sergiy (3/16/2016)
Jeff Moden (3/16/2016)
Lynn Pettis (8/6/2012)
An empty string is not null, it is a known value (empty).I strongly agree. NULL is not NOTHING. NOTHING is a known condition. NULL is an unknown condition. People frequently try to equate the two and THAT's just wrong. 😀
I strongly disagree! 😀
We're talking here not just about an empty string, we're talking about an empty string representing a number.
There is no "NOTHING number". "Nothing" in terms of numbers can have only one meaning - it's missing, undefined.
We use to represent such a condition with NULL.
I had to specifically program my way around those conversions when importing data from a file.
You must have a way to distinguish between 0 Qty values imported from "... ,0, ..." and from "... ,, ...".
Once it's imported you have no way to tell the difference.
So, you need to import into a staging table with all varchar columns, and after that use NULLIF to correctly deal with missing numbers.
When you export data using bcp - what numeric value in a table will give you an empty string like this "... ,, ..." in a file?
Right, NULL. Nothing else.
So, to me, it would be logical if a reverse implicit conversion would work the same way.
Definitely not like this:
declare @MyVarChar varchar(12) = '';declare @MyVarcharInt int;
declare @MyVarCharReverse varchar(12);
set @MyVarcharInt = @MyVarChar
set @MyVarCharReverse = @MyVarcharInt
select @MyVarChar MyVarchar, @MyVarcharInt MyVarcharInt, @MyVarCharReverse MyVarCharReverse
Results:
MyVarchar MyVarcharInt MyVarCharReverse
------------ ------------ ----------------
0 0
Ah! Yeah... I see your point there for empty strings. Unless someone defines what they mean for a given column, they have the same amount of information as a NULL ... UNKNOWN. Thanks, Sergiy.
I don't see that. A middle name of '' should mean the person has no middle name (and that such fact is known); a middle name of NULL means it's unknown. Those are not the same thing.
I guess you missed the part where it says that the empty strings come from a file.
There's no NULL in a csv. In some cases, an empty value should be an empty string and in others it should be a NULL value. Someone should define what does that empty value should represent to store the correct value in the database.
.
Well perhaps but that's the weakness within the transfer format. Just because CSV didn't provide a way to notate NULL vs '' (or rather you and your data provided didn't agree on a way to convey NULL) does NOT mean null=''. Frankly just means you picked a poor way to pass the data (or your upstream folks didn't bother to think through it and stuck you with the check)..
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
March 18, 2016 at 9:14 pm
Matt Miller (#4) (3/18/2016)
Just because CSV didn't provide a way to notate NULL vs '' (or rather you and your data provided didn't agree on a way to convey NULL) does NOT mean null=''.
But, CSV DOES provide a real way to pass embedded NULLs. That's what CHAR(0) is for.
But, all that nowithstanding, I think that any form of CSV or TSV is pretty bad and was meant for humans to be able to read the output rather than geared for data transfer. If you look at CHAR(0) thru CHAR(4) and CHAR(28) thru CHAR(31) (just for starters), some very nice and very old and very effective methods of text data transmission will reappear for some and be something new for others.
DECLARE @SomeString VARCHAR(8000);
SELECT @SomeString = 'This ' + CHAR(0) + ' is a NULL.';
SELECT @SomeString; --Cut off by the software that builds a grid but not text results.
PRINT @SomeString; --Same as text results.
SELECT *, AsciiValueOfFirstCharacter = ASCII(LEFT(Item,1))
FROM dbo.DelimitedSplit8k(@SomeString,SPACE(1))
;
Unfortunately, nothing in SSMS recognizes CHAR(0) as an indicator for NULL. At best, it returns an unprintable character which may or may not look like a space to some or a zero length character on the screen. Humans have a fit with such things as do many spreadsheets which, of course, I built for humans. If humans would get over the need for easy readability of volumes of data they have no chance of reading completely, we could transmit text based and text based columnar data a whole lot faster. Throw in a good ol' Escape (CHAR(27)) here and there and you could even transmit some awesome computer understood short hand for text attributes such as bold, italic, color, etc, and even vector graphics with very little going across the pipe when compared to any of the pipe-clogging markup languages.
Heh... maybe I should call up one of the old standards, document it, and call it "JMOD" for short. 😀
--Jeff Moden
Change is inevitable... Change for the better is not.
March 20, 2016 at 9:17 pm
Jeff Moden (3/18/2016)
Heh... maybe I should call up one of the old standards, document it, and call it "JMOD" for short. 😀
Plagiarist!!!
:w00t:
As for CHAR(0) - well, it's actually a character, not quite an empty string.
We can slightly modify your example to see it for ourselves:
DECLARE @SomeString VARCHAR(8000);
SELECT @SomeString = 'This ,' + CHAR(0) + ', is a NULL.';
SELECT @SomeString; --Cut off by the software that builds a grid but not text results.
PRINT @SomeString; --Same as text results.
SELECT *, AsciiValueOfFirstCharacter = ASCII(LEFT(Item,1))
FROM dbo.DelimitedSplit8k(@SomeString,SPACE(1))
And it's much closer to zero numeric value than an empty string.
A null character, therefore, has a numeric value of 0, but it has a special meaning when interpreted as text.
http://www.webopedia.com/TERM/N/null_character.html
Once again, the quote reminds us: same characters have different meanings when interpreted as text or a number.
Empty string interpreted as a number can have only one meaning - NULL.
At least I cannot think of any other option.
On a side note.
The modified script gives another observation:
SQL Server matches CHAR(0) to SPACE(1).
In some cases. 🙂
SELECT CHARINDEX (SPACE(1), char(0) + ',' + SPACE(1))
WHERE space(1) = CHAR(0)
3
(1 row(s) affected)
Apparently, CHAR(0) <> SPACE(1) for CHARINDEX, when they are equal for "=".
_____________
Code for TallyGenerator
March 22, 2016 at 8:23 pm
Jeff Moden (3/18/2016)
Matt Miller (#4) (3/18/2016)
Just because CSV didn't provide a way to notate NULL vs '' (or rather you and your data provided didn't agree on a way to convey NULL) does NOT mean null=''.But, CSV DOES provide a real way to pass embedded NULLs. That's what CHAR(0) is for.
But, all that nowithstanding, I think that any form of CSV or TSV is pretty bad and was meant for humans to be able to read the output rather than geared for data transfer. If you look at CHAR(0) thru CHAR(4) and CHAR(28) thru CHAR(31) (just for starters), some very nice and very old and very effective methods of text data transmission will reappear for some and be something new for others.
DECLARE @SomeString VARCHAR(8000);
SELECT @SomeString = 'This ' + CHAR(0) + ' is a NULL.';
SELECT @SomeString; --Cut off by the software that builds a grid but not text results.
PRINT @SomeString; --Same as text results.
SELECT *, AsciiValueOfFirstCharacter = ASCII(LEFT(Item,1))
FROM dbo.DelimitedSplit8k(@SomeString,SPACE(1))
;
Unfortunately, nothing in SSMS recognizes CHAR(0) as an indicator for NULL. At best, it returns an unprintable character which may or may not look like a space to some or a zero length character on the screen. Humans have a fit with such things as do many spreadsheets which, of course, I built for humans. If humans would get over the need for easy readability of volumes of data they have no chance of reading completely, we could transmit text based and text based columnar data a whole lot faster. Throw in a good ol' Escape (CHAR(27)) here and there and you could even transmit some awesome computer understood short hand for text attributes such as bold, italic, color, etc, and even vector graphics with very little going across the pipe when compared to any of the pipe-clogging markup languages.
Heh... maybe I should call up one of the old standards, document it, and call it "JMOD" for short. 😀
I still remember using |~| as the delimiter in many cases. Still looking for a single case where it occurs naturally (other than posts like this one describing it :-))
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
Viewing 11 posts - 16 through 25 (of 25 total)
You must be logged in to reply to this topic. Login to reply