Recent PostsRecent Posts Popular TopicsPopular Topics
 Home Search Members Calendar Who's On

 0 is equal to zero length string. Can someone explain how this can be? Rate Topic Display Mode Topic Options
Author
 Message
 Posted Saturday, August 4, 2012 9:27 PM
 SSC Journeyman Group: General Forum Members Last Login: Today @ 10:02 AM Points: 93, Visits: 1,089
 --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!';
Post #1340233
 Posted Saturday, August 4, 2012 11:46 PM
 SSC Journeyman Group: General Forum Members Last Login: Today @ 10:02 AM Points: 93, Visits: 1,089
 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.
Post #1340237
 Posted Sunday, August 5, 2012 8:01 AM
 SSCertifiable Group: General Forum Members Last Login: Sunday, January 4, 2015 7:55 AM Points: 5,333, Visits: 25,280
 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 ` If everything seems to be going well, you have obviously overlooked something. RonPlease help us, help you -before posting a question please read Before posting a performance problem please read
Post #1340273
 Posted Monday, August 6, 2012 12:45 AM
 Ten Centuries Group: General Forum Members Last Login: Monday, October 31, 2016 11:11 AM Points: 1,076, Visits: 3,229
 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'Elseprint '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
Post #1340378
 Posted Monday, August 6, 2012 3:48 AM
 SSCertifiable Group: General Forum Members Last Login: Today @ 11:55 AM Points: 7,967, Visits: 19,086
 The implicit conversion suggestion is strengthened by the following:`declare @MyVarChar varchar(12) = '';declare @MyVarcharInt intset @MyVarcharInt = @MyVarCharselect @MyVarChar MyVarchar, @MyVarcharInt MyVarcharInt` Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.When you ask a question (and please do ask a question: "My T-SQL does not work" just doesn't cut it), please provide enough information for us to understand its context.If your posting includes some T-SQL code, please surround the code with IFCode formatting tags. It helps readability a lot.
Post #1340469
 Posted Monday, August 6, 2012 5:57 PM
 SSC Journeyman Group: General Forum Members Last Login: Today @ 10:02 AM Points: 93, Visits: 1,089
 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)`
Post #1340953
 Posted Monday, August 6, 2012 6:07 PM
 SSC-Insane Group: General Forum Members Last Login: Today @ 11:12 AM Points: 23,522, Visits: 37,747
 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.
Post #1340954
 Posted Tuesday, August 7, 2012 7:32 AM
 SSCrazy Group: General Forum Members Last Login: Today @ 11:50 AM Points: 2,399, Visits: 9,473
 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 AllenBusiness Intelligence AnalystPhiladelphia, PAHow to post data/code on a forum to get the best help.Make sure that you include code in the appropriate IFCode tags, e.g. [code="sql"][/code]. You can find the IFCode tags on the left when you are writing a post.How to Post Performance Problems
Post #1341255
 Posted Tuesday, August 7, 2012 7:52 AM
 SSCrazy Eights Group: General Forum Members Last Login: Today @ 11:04 AM Points: 8,215, Visits: 17,774
 You might find this article interesting.http://msdn.microsoft.com/en-us/library/ms190309.aspxSo 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. Luis C.General Disclaimer:Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?How to post data/code on a forum to get the best help: Option 1 / Option 2
Post #1341275
 Posted Tuesday, August 7, 2012 8:32 AM
 SSChampion Group: General Forum Members Last Login: Monday, August 29, 2016 1:09 PM Points: 13,999, Visits: 9,728
 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.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, ETCProperty of The Thread"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Post #1341308

 Permissions