August 4, 2012 at 9:27 pm
--I know this has to do with an implicit conversion of the varchar to int,
--but why does the zero length string get converted to zero?
DECLARE @MyINT int = 0;
declare @MyVarChar varchar(12) = '';
IF @MyINT = @MyVarChar
PRINT 'TRUE! 0 equals ''''';
ELSE
PRINT 'FALSE!';
August 4, 2012 at 11:46 pm
Ok, I think I understand why this is. There is an implicit conversion from varchar to int and you might think that when converting '' to int it would be null. But null isn't an int, it isn't anything. So, it converts it to 0. I would rather get a type conversion error though.
August 5, 2012 at 8:01 am
JasonRowland (8/4/2012)
Ok, I think I understand why this is. There is an implicit conversion from varchar to int and you might think that when converting '' to int it would be null. But null isn't an int, it isn't anything. So, it converts it to 0. I would rather get a type conversion error though.
In your sample as you stated it is a zero length string, and NOT null, if it were null your result would be:
DECLARE @MyINT int = 0;
declare @MyVarChar varchar(12) = NULL;
IF @MyINT = @MyVarChar
PRINT 'TRUE! 0 equals ''''';
ELSE
PRINT 'FALSE!';
Result:
FALSE
August 6, 2012 at 12:45 am
I think you might find that SQL server is doing an implicit cast to an Int, I've see this at times when comparing numeric strings and Integers,
If you take the simple example,
If 1='A'
Pring 'True'
Else
print 'False'
Then Joe is correct SQL Server will give you a conversion error "Conversion failed when converting the varchar value 'A' to data type int."
That leads me to beleive that SQL server is doing an implict conversion under the bonnet of an Aplhanumeric field types if one of the parameters is a numeric data type, as it needs to be able to match like for like.
Unfortunately a blank string (even one just full of spaces), of any type will convert to 0, just do Print convert(int, '') (or CAST('' as Int) and SQL Server will return 0 every time.
I can understand the logic of the background convert, but dont understand strings just contianing spaces or empty string get cast to 0, as if you do PRINT ASCII('') it returns 32, indicating that there is a space in the string,
I can only conclude that this is a piece of legacy functionality in the code base, does anyone know if any other databases do this type of converstion?
_________________________________________________________________________
SSC Guide to Posting and Best Practices
August 6, 2012 at 3:48 am
The implicit conversion suggestion is strengthened by the following:
declare @MyVarChar varchar(12) = '';
declare @MyVarcharInt int
set @MyVarcharInt = @MyVarChar
select @MyVarChar MyVarchar, @MyVarcharInt MyVarcharInt
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
August 6, 2012 at 5:57 pm
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)
August 6, 2012 at 6:07 pm
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.
August 7, 2012 at 7:33 am
I think that we would all agree that the following strings should all produce the same results when converted to an integer:
' 1'
'01'
So, it makes sense to me, that if we remove the right-most character, they should also produce the same results when converted.
' '
'0'
We also know that trailing spaces are ignored in many cases, so the following are essentially equivalent
''
' '
So, by the law of transitivity, the result of converting all of these strings to integer should produce the same results.
''
' '
'0'
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
August 7, 2012 at 7:52 am
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.
August 7, 2012 at 8:32 am
CELKO (8/5/2012)
I would have to do some digging for exact rules, but this is a T-SQL dialect error. The conversion should fail with a message that the string cannot be CAST
Actually, I would say it's not an error. The ASCII code for a zero-length string is 0. Also called a "null character set" on ASCII tables.
If you want null character sets to be NULL when converted to integers, try NullIf() for that.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
August 7, 2012 at 9:35 am
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.
/*------------------------
select cast('' as float) as [float], cast('' as real) as [real], cast('' as money) as [money], cast('' as smallmoney) as [smallmoney], cast('' as bigint) as [bigint], cast('' as int) as [int], cast('' as smallint) as [smallint], cast('' as tinyint) as [tinyint]
------------------------*/
float real money smallmoney bigint int smallint tinyint
---------------------- ------------- --------------------- --------------------- -------------------- ----------- -------- -------
0 0 0.00 0.00 0 0 0 0
(1 row(s) affected)
Now try the same thing with decimal and you get what is in my opinion the correct response form SQL Server.
/*------------------------
select cast('' as decimal)
------------------------*/
---------------------------------------
Msg 8114, Level 16, State 5, Line 2
Error converting data type varchar to numeric.
August 7, 2012 at 10:00 am
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?
August 18, 2012 at 8:19 pm
CELKO (8/5/2012)
I would have to do some digging for exact rules, but this is a T-SQL dialect error. The conversion should fail with a message that the string cannot be CAST
Have you had a chance to do any digging?
August 21, 2012 at 10:00 am
GSquared (8/7/2012)
CELKO (8/5/2012)
I would have to do some digging for exact rules, but this is a T-SQL dialect error. The conversion should fail with a message that the string cannot be CASTActually, I would say it's not an error. The ASCII code for a zero-length string is 0. Also called a "null character set" on ASCII tables.
So CAST('A' AS INT) should equal 65? I'm not sure the ASCII codes have anything to do with it.
I tend to agree that it should throw a conversion error as a blank string is neither a number, nor is it NULL, in the same way as all manner of other strings that would throw an error, but it's a moot point since it's not going to change and generally follows the convention SQL Server uses elsewhere (e.g. DATE/DATETIME conversions).
March 16, 2016 at 7:35 pm
This inconsistency in type conversions, IMO is ridiculous. If you can convert to one numeric type with an empty string, you should be able to convert to other numeric types in the same way.
Seems like one Microsoft programmer wasn't talking to another one when they designed this.
<sarcasm> Now we just need an IfTypeIsThisTypeThenConvertThisAsThisOtherType statement to fix it please Microsoft. <\sarcasm>
Unfortunately I got around this with: CAST(IIF(ColumnName = '','0',ColumnName) as decimal(ps))
Viewing 15 posts - 1 through 15 (of 25 total)
You must be logged in to reply to this topic. Login to reply