none
MSSQL equal operator bug?

    Dotaz

  • Ineresting behavior of equal operator. Isn't this a bug?

     

    Result is 1:

    select 1 where 'aa' = 'aa             ' 
    select 1 where 'aa   ' = 'aa             '


    Empty result:

    select 1 where ' aa' = 'aa'

     

    My opinion is, that equl is only 'aa' = 'aa' and there is no any other option.

     

    3. března 2011 13:43

Odpovědi

  • This is not a bug.

    Databases use character strings stored in columns of fixed legth very often and almost all these columns contain trailing spaces because stored texts are not always of the maximum allowed length.

    To make the comparison of such strings easier the shorter string is padded with spaces needed to make it equal to the length of the longer string.

    This rule is independent on your opinion on MSSQL but some database engines (e.g. FoxPro) allow more flexibility in string comparisons and you may decide about the exact match or (as an opposite option) comparison in the length of the shorter string.

    If you would like to study more about this topic look at following examples: http://joelmansford.wordpress.com/2008/04/04/surprising-sql-string-comparison-with-untrimmed-string/

     

    4. března 2011 1:01