Why SQL Server converts '' to zero

  • 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.

  • 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

  • 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!":-D
    (So many miracle inventions provided by MS to us...)

    How to post your question to get the best and quick help[/url]

  • 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]

    "I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

    -- Itzik Ben-Gan 2001

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply