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 12»»

0 is equal to zero length string. Can someone explain how this can be? Expand / Collapse
Author
Message
Posted Saturday, August 04, 2012 9:27 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: 2 days ago @ 2:08 PM
Points: 89, Visits: 794
--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 04, 2012 11:46 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: 2 days ago @ 2:08 PM
Points: 89, Visits: 794
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 05, 2012 8:01 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 10:25 AM
Points: 5,472, Visits: 23,549
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.

Ron

Please help us, help you -before posting a question please read

Before posting a performance problem please read
Post #1340273
Posted Sunday, August 05, 2012 6:51 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Tuesday, January 15, 2013 11:11 AM
Points: 1,945, Visits: 2,782
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

Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL
Post #1340321
Posted Monday, August 06, 2012 12:45 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Thursday, April 17, 2014 7:37 AM
Points: 860, Visits: 2,323
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
Post #1340378
Posted Monday, August 06, 2012 3:48 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 3:44 AM
Points: 4,830, Visits: 11,192
The implicit conversion suggestion is strengthened by the following:

declare @MyVarChar varchar(12) = '';

declare @MyVarcharInt int

set @MyVarcharInt = @MyVarChar

select @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.
Post #1340469
Posted Monday, August 06, 2012 5:57 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: 2 days ago @ 2:08 PM
Points: 89, Visits: 794
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 06, 2012 6:07 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 7:12 PM
Points: 22,518, Visits: 30,269
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.



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1340954
Posted Tuesday, August 07, 2012 7:32 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, September 03, 2013 8:24 AM
Points: 1,240, Visits: 5,421
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
Post #1341255
Posted Tuesday, August 07, 2012 7:52 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 5:51 PM
Points: 2,768, Visits: 5,933
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.



Luis C.
I am a great believer in luck, and I find the harder I work the more I have of it. Stephen Leacock

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1341275
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse