Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


0 is equal to zero length string. Can someone explain how this can be?


0 is equal to zero length string. Can someone explain how this can be?

Author
Message
JasonRowland
JasonRowland
SSC-Enthusiastic
SSC-Enthusiastic (103 reputation)SSC-Enthusiastic (103 reputation)SSC-Enthusiastic (103 reputation)SSC-Enthusiastic (103 reputation)SSC-Enthusiastic (103 reputation)SSC-Enthusiastic (103 reputation)SSC-Enthusiastic (103 reputation)SSC-Enthusiastic (103 reputation)

Group: General Forum Members
Points: 103 Visits: 1115
--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!';
JasonRowland
JasonRowland
SSC-Enthusiastic
SSC-Enthusiastic (103 reputation)SSC-Enthusiastic (103 reputation)SSC-Enthusiastic (103 reputation)SSC-Enthusiastic (103 reputation)SSC-Enthusiastic (103 reputation)SSC-Enthusiastic (103 reputation)SSC-Enthusiastic (103 reputation)SSC-Enthusiastic (103 reputation)

Group: General Forum Members
Points: 103 Visits: 1115
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.
bitbucket-25253
bitbucket-25253
SSCertifiable
SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)

Group: General Forum Members
Points: 5687 Visits: 25280
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
Jason-299789
Jason-299789
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1132 Visits: 3229
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
Phil Parkin
Phil Parkin
SSCrazy Eights
SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)

Group: General Forum Members
Points: 8300 Visits: 19447
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.

If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

Please surround any code or links you post with the appropriate IFCode formatting tags. It helps readability a lot.
JasonRowland
JasonRowland
SSC-Enthusiastic
SSC-Enthusiastic (103 reputation)SSC-Enthusiastic (103 reputation)SSC-Enthusiastic (103 reputation)SSC-Enthusiastic (103 reputation)SSC-Enthusiastic (103 reputation)SSC-Enthusiastic (103 reputation)SSC-Enthusiastic (103 reputation)SSC-Enthusiastic (103 reputation)

Group: General Forum Members
Points: 103 Visits: 1115
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)


Lynn Pettis
Lynn Pettis
SSC-Insane
SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)

Group: General Forum Members
Points: 24157 Visits: 37926
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.

Cool
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)
drew.allen
drew.allen
SSCrazy
SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)

Group: General Forum Members
Points: 2587 Visits: 9859
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
How to post data/code on a forum to get the best help.
How to Post Performance Problems
Make sure that you include code in the appropriate IFCode tags, e.g. [code=sql]<your code here>[/code]. You can find the IFCode tags under the INSERT options when you are writing a post.
Luis Cazares
Luis Cazares
SSCrazy Eights
SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)

Group: General Forum Members
Points: 8474 Visits: 18081
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.
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search