''=0 ?!?!

  • This is something I ran into today and am curious if anyone knows anything about this....

    I was working on something earlier and executed T-SQL command. I noticed a typo in my code after the query had executed successfully. The offending code looked something like this...

    IF CHARINDEX('xxx','yy')=''

    PRINT 'xxx'

    Though logic would dictate otherwise, the above query runs successfully on 2008 & 2012. It appears that SQL Server treats double single quotes as a zero (0). I was curious so I ran these:

    SELECT ISNUMERIC(5);

    SELECT ISNUMERIC('');

    The first statement returned a 1, the second returned a 0. This is what I expected: 5 is numeric, '' is not. :satisfied:.

    Then I ran the following and it appears that ''=0...

    SELECT 7*''

    SELECT CAST('' AS int)

    SELECT REPLICATE(CAST('' AS int),5)

    After a bunch of Google searching I was not able to find anything about this... :blink:

    Is this a bug, is my server haunted? Anyone know anything about this?

    "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

  • SQL cannot compare two different data types, nor can it multiply by a string. Hence, any time you ask it to do that, it will do an implicit conversion. As you saw, casting '' to int results in 0 (it can't logically be anything else), so all you're seeing here is the implicit conversion of a string to an integer.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Thanks Gail.

    "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

  • If you think those "typos" that pass SQL Server's parser seem weird, try this one:

    SELECT 7+$


    My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]

    My thought question: Have you ever been told that your query runs too fast?

    My advice:
    INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
    The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

    Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
    Since random numbers are too important to be left to chance, let's generate some![/url]
    Learn to understand recursive CTEs by example.[/url]
    [url url=http://www.sqlservercentral.com/articles/St

  • dwain.c (12/17/2012)


    If you think those "typos" that pass SQL Server's parser seem weird, try this one:

    SELECT 7+$

    Thanks for the post dwain. That's really odd too; I would never think to try that if not for this forum.. This was actually the first time I ever posted a question on any forumn - I decided to do so after you suggested it in a different thread recently (I wish I had a more interesting question to post).

    I assumed that statement was casting 7 as money... I wanted to see for sure but couldn't think a way to do something like this:

    SELECT GetTheDatatypeOf(7+$) :unsure:

    Then I came up with:

    select 7+$ AS dt

    into tmp;

    SELECT DATA_TYPE FROM INFORMATION_SCHEMA.COLUMNS

    WHERE TABLE_NAME='tmp' AND COLUMN_NAME='dt';

    DROP TABLE tmp;

    Edit: Typo

    "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

  • Interesting too is that an empty string does not work for decimal values:

    SELECT 1.1+''

    But it again does work for dates:

    SELECT CAST('' as datetime)

    Ah the wonders of SQL -- "wonder why it does that?!"

    SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.

Viewing 6 posts - 1 through 5 (of 5 total)

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