In real life, most character sets have many characters: not just “ A” and “ B” but whole alphabets, sometimes multiple alphabets or eastern writing systems with thousands of characters, along with many special symbols and punctuation marks. It is a little more complex than a binary collation. We call this a case-insensitive collation. The collation is a set of rules (only one rule in this case): “compare the encodings.” We call this simplest of all possible collations a binary collation.īut what if we want to say that the lowercase and uppercase letters are equivalent? Then we would have at least two rules: (1) treat the lowercase letters “ a” and “ b” as equivalent to “ A” and “ B” (2) then compare the encodings. What we've just done is apply a collation to our character set.
Because 0 is less than 1, we say “ A” is less than “ B”. The simplest way to do this is to look at the encodings: 0 for “ A” and 1 for “ B”. Suppose that we want to compare two string values, “ A” and “ B”. The letter “ A” is a symbol, the number 0 is the encoding for “ A”, and the combination of all four letters and their encodings is a character set. We give each letter a number: “ A” = 0, “ B” = 1, “ a” = 2, “ b” = 3. Suppose that we have an alphabet with four letters: “ A”, “ B”, “ a”, “ b”. Let's make the distinction clear with an example of an imaginary character set. A collation is a set of rules for comparing characters in a character set. Please keep in mind that, as pointed out by Jacob Stamm in the comments, "casting columns to compare them will cause any indexing on that column to be ignored".įor much greater detail about this collation business, I highly recommend eggyal's excellent answer to this same question.Įither change the collation of one (or both) of the strings so that they match, or else add a COLLATE clause to your expression.Īs documented under Character Sets and Collations in General:Ī character set is a set of symbols and encodings. Or, SELECT * FROM table ORDER BY BINARY a Your solution might look something like this: SELECT * FROM table WHERE BINARY a = BINARY b Here is an example that uses the COLLATE clause: SELECT * FROM table ORDER BY key COLLATE latin1_general_ci Īnother option is to use the BINARY operator:īINARY str is the shorthand for CAST(str AS BINARY). Your solution is to specify a shared collation for the two columns within the query.
The clause COLLATE allows you to specify the collation used in the query.įor example, the following WHERE clause will always give the error you posted: WHERE 'A' COLLATE latin1_general_ci = 'A' COLLATE latin1_general_cs
This is generally caused by comparing two strings of incompatible collation or by attempting to select data of different collation into a combined column.
0 kommentar(er)
