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

Why SQL Server converts '' to zero Expand / Collapse
Author
Message
Posted Thursday, July 25, 2013 7:50 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, July 25, 2013 1:18 PM
Points: 1, Visits: 10
I have a web developer who sent me a query he couldn't figure out. Select Name from dbo.Employee where EmployeeID = ''. EmployeeID is an integer. First, I told him he shouldn't use '' as an integer. But, I could not explain why SQL Server converted '' to a zero. Can anyone answer that for me?

My assumption is that the engine sees a blank value as a zero because the column being compared to is an integer type. Since the value is nothing (not a null), the engine converts it to a zero because mathmatically zero added to anything doesn't change the value.
Post #1477494
Posted Thursday, July 25, 2013 8:00 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 7:58 AM
Points: 5,369, Visits: 9,919
This indeed returns 0:
SELECT CAST('' AS int)

It's just the way it works. I suppose when the DBMS or the standards were being written, somebody decided that '' would convert to 0 rather than throwing an error.

John
Post #1477503
Posted Thursday, July 25, 2013 8:13 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, September 19, 2014 5:50 AM
Points: 2,856, Visits: 5,124
John Mitchell-245523 (7/25/2013)
This indeed returns 0:
SELECT CAST('' AS int)

It's just the way it works. I suppose when the DBMS or the standards were being written, somebody decided that '' would convert to 0 rather than throwing an error.

John


Yeah, empty string, when converted to any T-SQL numeric datatypes except DECIMAL and NUMERIC will end up as 0 (zero).


_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!"
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help
Post #1477516
Posted Thursday, July 25, 2013 1:15 PM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Today @ 6:32 AM
Points: 581, Visits: 2,712
I asked this same question a few months back and got some good replies; just thought I'd share this:
http://www.sqlservercentral.com/Forums/Topic1397471-391-1.aspx

Check this out:
SELECT CAST(REPLICATE(CAST('' AS int),10) AS char(10)) AS [10 0's]
SELECT 22+$ AS [$22.00]



-- Alan Burstein



Read this article for best practices on asking questions.
Need to split a string? Try this (Jeff Moden)
Need a pattern-based string spitter? Try this (Dwain Camps)
My blog
Post #1477697
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse