Post reply

How does SQL work with blanks?

  • July 26, 2016 at 11:15 am

    #1892104

    Really odd question, unreal. Proves that it is not easy to build up a QotD.:angry:

  • July 26, 2016 at 11:35 am

    #1892111

    Corrected to add batch separators.

  • July 26, 2016 at 1:06 pm

    #1892138

    Interesting behavior. Thanks for the question!

  • July 26, 2016 at 7:13 pm

    #1892170

    My apologies. I didn't intend to give an ambiguous question in that way.

    I'll be more careful with my next one.

    Thanks for the feedback.

  • July 27, 2016 at 6:38 am

    #1892251

    Interesting one, thanks.

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • July 27, 2016 at 12:43 pm

    #1892370

    Thanks for the interesting question, Brett. It made sense to me. What's the matter with the rest of you?:-D

  • August 8, 2016 at 5:18 pm

    #1894444

    Good and interesting question. Here are some notes regarding the explanation of the answer, as copied here:

    Due to an odd behaviour that is not described at https://msdn.microsoft.com/en-us/library/ms175874.aspx, SQL Server choooses to allow a multiple character blank database name (ok), but then allows you to drop the same database using a different database name (single character). This is due to the string conversion.

    Notes:

    • The text of the link in the first sentence correctly takes you to the MSDN page for "Database Identifiers". However, the URL used for that link is actually https://msdn.microsoft.com/en-us/library/ms175997.aspx, which is the MSDN page for "COUNT".
    • The MSDN page for "Database Identifiers" shouldn't be expected to discuss this behavior as it is not specific to Identifiers.
    • I am not sure what exactly is meant by "string conversion" in the last sentence, but the reason that this behavior exists is due to SQL Server effectively ignoring trailing spaces for all string comparisons except the LIKE operator. As stated in the following KB article, INF: How SQL Server Compares Strings with Trailing Spaces :

      SQL Server follows the ANSI/ISO SQL-92 specification (Section 8.2, <Comparison Predicate>, General rules #3) on how to compare strings with spaces. The ANSI standard requires padding for the character strings used in comparisons so that their lengths match before comparing them. The padding directly affects the semantics of WHERE and HAVING clause predicates and other Transact-SQL string comparisons. For example, Transact-SQL considers the strings 'abc' and 'abc ' to be equivalent for most comparison operations.

      The only exception to this rule is the LIKE predicate. When the right side of a LIKE predicate expression features a value with a trailing space, SQL Server does not pad the two values to the same length before the comparison occurs. Because the purpose of the LIKE predicate, by definition, is to facilitate pattern searches rather than simple string equality tests, this does not violate the section of the ANSI SQL-92 specification mentioned earlier.

      While that KB article was written for SQL Server 2000, the behavior appears to not have changed, and I could not find any other documentation referring to this behavior.

    • In case anyone is wondering: No, Collation does not affect the handling of trailing spaces. Not even a binary Collation.

    Take care,

    Solomon..

    SQL# — https://SQLsharp.com/ ( SQLCLR library ofover 340 Functions and Procedures)
    Sql Quantum Lift — https://SqlQuantumLift.com/ ( company )
    Sql Quantum Leap — https://SqlQuantumLeap.com/ ( blog )
    Info sites — Collations     •     Module Signing     •     SQLCLR

Viewing 7 posts - 16 through 21 (of 21 total)

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