Post reply

Does '+' operator collation sensitive or not?

  • February 2, 2012 at 3:06 am

    #252293

    I have posted bug (from my point of view) on connect.microsoft. I reproduce it's text here:

    ---------------------------------------------------------------------------------------

    The article "Collation Precedence", section "Collation Sensitive and Collation Insensitive", states:

    "The string concatenation operator is collation insensitive".

    I believe this is wrong, the correct version:

    "The string concatenation operator is collation sensitive".

    The proof:

    CREATE TABLE T1 (

    Col1 varchar(10) collate greek_ci_as,

    Col2 varchar(20) collate latin1_general_cs_as,

    Col3 varchar(10)

    )

    GO

    SELECT * FROM T1 WHERE Len(Col1 COLLATE SQL_Czech_Cp1250_CI_AS+Col2+Col3 COLLATE SQL_Czech_Cp1250_CI_AS)>0

    SELECT * FROM T1 WHERE Len(Col1+Col2 COLLATE SQL_Czech_Cp1250_CI_AS+Col3 COLLATE SQL_Czech_Cp1250_CI_AS)>0

    SELECT * FROM T1 WHERE Len(Col1+Col2+Col3 COLLATE SQL_Czech_Cp1250_CI_AS)>0

    If the above-mentioned state correct, all 3 concatenations must be calculated with "Explicit X" label and return the same result. In really first and second works, and third end with message:

    "Implicit conversion of varchar value to varchar cannot be performed because the collation of the value is unresolved due to a collation conflict."

    Obviously, the root of the error is in expression Col1+Col2, which get label 'No-collation'. Again, if the state correct, operator '+' must allow operands and result with label like this one. But, as we can see, it doesn't. So, logical deduction: operator '+' is NOT collation insensitive.

    ---------------------------------------------------------------------------------------

    But I want to know you opinions. What do you think? May be I just don't quite understand what IS collation [in]sensitive??

    Thanks!

  • February 2, 2012 at 7:36 am

    #1441830

    try below solu

    http://forums.aspfree.com/microsoft-sql-server-14/collation-conflict-20773.html

  • February 2, 2012 at 9:22 am

    #1441908

    johnitech.itech (2/2/2012)

    try below solu

    http://forums.aspfree.com/microsoft-sql-server-14/collation-conflict-20773.html

    Your link just not relevant to my question, sorry.

  • February 2, 2012 at 11:20 pm

    #1442206

    :hehe:

    sorry see below link instead man

    http://stackoverflow.com/questions/3296428/changing-sql-server-collation-to-case-insensitive-from-case-sensitive

  • February 3, 2012 at 12:20 am

    #1442217

    johnitech.itech (2/2/2012)

    :hehe:

    sorry see below link instead man

    http://stackoverflow.com/questions/3296428/changing-sql-server-collation-to-case-insensitive-from-case-sensitive

    That one is even more irrelevant.

    Typing in some keywords in Google and posting every result here on the forum isn't helping anybody.

    Please do some actual research and try to understand what the original poster (OP) is asking before you even attempt to reply.

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

  • February 5, 2012 at 3:07 am

    #1442803

    First:

    Operators and functions are either collation sensitive or insensitive.

    Collation sensitive

    This means that specifying a No-collation operand is a compile-time error. The expression result cannot be No-collation.

    Collation insensitive

    This means that the operands and result can be No-collation.

    Operators and Collation

    The comparison operators, and the MAX, MIN, BETWEEN, LIKE, and IN operators, are collation sensitive. The string used by the operators is assigned the collation label of the operand that has the higher precedence. The UNION operator is also collation sensitive, and all string operands and the final result is assigned the collation of the operand with the highest precedence. The collation precedence of the UNION operands and result are evaluated column by column.

    The assignment operator is collation insensitive and the right expression is cast to the left collation.

    The string concatenation operator is collation insensitive, the two string operands and the result are assigned the collation label of the operand with the highest collation precedence. The UNION ALL and CASE operators are collation insensitive, and all string operands and the final results are assigned the collation label of the operand with the highest precedence. The collation precedence of the UNION ALL operands and result are evaluated column by column.

    :w00t:

    http://msdn.microsoft.com/en-us/library/ms179886.aspx

    second : its mysql ,But i think the same with MSSQL

    http://www.databasejournal.com/features/mysql/article.php/3904221/How-Operator-Precedence-Affects-MySQL---SELECT-Queries.htm

  • February 5, 2012 at 8:03 am

    #1442828

    johnitech.itech (2/5/2012)

    First:

    Operators and functions are either collation sensitive or insensitive.

    Collation sensitive

    This means that specifying a No-collation operand is a compile-time error. The expression result cannot be No-collation.

    Collation insensitive

    This means that the operands and result can be No-collation.

    Operators and Collation

    The comparison operators, and the MAX, MIN, BETWEEN, LIKE, and IN operators, are collation sensitive. The string used by the operators is assigned the collation label of the operand that has the higher precedence. The UNION operator is also collation sensitive, and all string operands and the final result is assigned the collation of the operand with the highest precedence. The collation precedence of the UNION operands and result are evaluated column by column.

    The assignment operator is collation insensitive and the right expression is cast to the left collation.

    The string concatenation operator is collation insensitive, the two string operands and the result are assigned the collation label of the operand with the highest collation precedence. The UNION ALL and CASE operators are collation insensitive, and all string operands and the final results are assigned the collation label of the operand with the highest precedence. The collation precedence of the UNION ALL operands and result are evaluated column by column.

    :w00t:

    http://msdn.microsoft.com/en-us/library/ms179886.aspx

    All this chunk of text just copy-past from BOL. We know it, thanks.

    johnitech.itech (2/5/2012)

    second : its mysql ,But i think the same with MSSQL

    http://www.databasejournal.com/features/mysql/article.php/3904221/How-Operator-Precedence-Affects-MySQL---SELECT-Queries.htm

    And this, again, NOT relevant to my question. I ask all (and you) about collation precedence, and article by you link told about operator precedence. Understand difference?

  • November 13, 2014 at 11:11 am

    #1758344

    Hello man.

    This question is very relevant. In my tests, I also found that + operator and CASE operator dont behave according the expected.

    In the BOL, the CASE operator it used in a example, and one of the examples the use of case works, when it uses the COLLATE clause (explicit).

    But, if you look at table definition, the types are unicode and windows collations.

    But if you try replay the same sample, but with non-unicode type, the query dont works, same with the COLLATE clause.

    I also that CASE and + are case - sensitive. The UNION ALL operator also raise errors.

    The BOL seems incosistent.

    Any have a ideia about this?

Viewing 8 posts - 1 through 7 (of 7 total)

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