SQL Server Performance

0 = ''

Discussion in 'General DBA Questions' started by MartinSmithh, Mar 4, 2007.

  1. MartinSmithh New Member

    I was somewhat surprised by the following behaviour.

    IF (0 = '')
    BEGIN
    PRINT '0 = '''''
    END
    ELSE
    BEGIN
    PRINT '0 <> '''''
    END

    Is there any server option or keyword that can be used to avoid 0 evaluating as equal to an empty string?
  2. Roji. P. Thomas New Member

  3. MartinSmithh New Member

    Not a very welcome feature IMO.

    I can see that you would want

    '0' to implicitly convert to 0 but not '' to 0
  4. FrankKalis Moderator

    http://www.sql-server-performance.com/fk_datetime2.asp<br /><br />I fully agree with you that SQL Server shouldn't quietly convert '' to 0, but rather throw an error, but that's the way it goes. And, frankly, in the first place I would ask myself, if comparing a number to an empty string makes logically much sense at all. [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br /><br />--<br />Frank Kalis<br />Moderator<br />Microsoft SQL Server MVP<br />Webmaster:<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a>
  5. MartinSmithh New Member

    Hi Frank,

    I agree that generally you wouldn't be doing this sort of compare.

    Where it came up was when I was passing a comma delimited list of values (relating to check boxes) to fnSplit http://www.sqlmag.com/articles/index.cfm?articleid=21071&) and joining onto another table which happened to have the identity column starting at zero.

    I was wondering why the join still returned results when no check boxes were selected and then realised that '' was being treated as zero.
  6. MartinSmithh New Member

    And my preferred behaviour wouldn't be for it to throw an error but just to return that '0' <> ''.
  7. FrankKalis Moderator

    You know, the really nasty thing about it is, that, to the best of my knowledge, this behaviour is not documented, and takes time to find out. Time that could have better be spend on something else. No matter, if it returns an error or just FALSE. This isn't a terribly thrilling investigative matter anyway. [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br /><br />--<br />Frank Kalis<br />Moderator<br />Microsoft SQL Server MVP<br />Webmaster:<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a>
  8. FrankKalis Moderator

    quote:Originally posted by MartinSmithh

    And my preferred behaviour wouldn't be for it to throw an error but just to return that '0' <> ''.
    Actually it does! As long as you pass two strings to be compared.


    IF ('0' = '')
    BEGIN
    PRINT '0 = '''''
    END
    ELSE
    BEGIN
    PRINT '0 <> '''''
    END

    0 <> ''

    yield the expected result. However, this is NOT, what you have written in your example.

    As soon as you pass something else but a string, you're subject to the data type precedence rules. These ARE documented in BOL and state, that SQL Server ALWAYS tries to convert to a data type of a higher precedence when there are two values of a different type to be compared. And because 0 is an INT number (while '0' is a string) with a higher precedence as a string, SQL Server tries to convert '' to an INT. This is the point, where I think it should throw the error and not quitely go on. The comparison between '0' and '' is a comparison between two strings, thus both types have the same precendence and no conversion takes place. The result of it is just what you would expect.


    --
    Frank Kalis
    Moderator
    Microsoft SQL Server MVP
    Webmaster:http://www.insidesql.de
  9. MartinSmithh New Member

    True.

    Running the following throws an error I guess it would be consistent to do that for '' also.

    IF (0 = 'Armadillo')
    BEGIN
    PRINT 'yes'
    END
    ELSE
    BEGIN
    PRINT 'no'
    END


    Server: Msg 245, Level 16, State 1, Line 1
    Syntax error converting the varchar value 'Armadillo' to a column of data type int.

  10. Adriaan New Member

    One of those areas where the syntax should be more strict, and where the engine should just throw the error like in MartinSmithh's post.
  11. mmarovic Active Member

    <blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><i>Originally posted by MartinSmithh</i><br /><br />Hi Frank,<br /><br />I agree that generally you wouldn't be doing this sort of compare.<br /><br />Where it came up was when I was passing a comma delimited list of values (relating to check boxes) to fnSplit <a target="_blank" href=http://www.sqlmag.com/articles/index.cfm?articleid=21071&>http://www.sqlmag.com/articles/index.cfm?articleid=21071&</a>) and joining onto another table which happened to have the identity column starting at zero.<br /><br />I was wondering why the join still returned results when no check boxes were selected and then realised that '' was being treated as zero.<br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote"><br />Do you know that using split function is not the most efficient way to solve the problem you have. It's time to advertise my own article [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<a target="_blank" href=http://www.sql-server-performance.com/mm_list_random_values.asp>http://www.sql-server-performance.com/mm_list_random_values.asp</a><br /><br />and also another larger and well known article:<a target="_blank" href=http://www.sommarskog.se/arrays-in-sql.html>http://www.sommarskog.se/arrays-in-sql.html</a><br />

Share This Page