I accepted Doug Currie answer, but I want to add some “algorithm” how to do it, because sqlite3 documentation is very strange (at least for me). Ok, we have working sqlite3 and now: Download ICU extension for sqlite Compile it: gcc -shared icu.c `icu-config –ldflags` -o libSqliteIcu.so It is for Linux. I also needed to … Read more
Binary collation compares your string exactly as strcmp() in C would do, if characters are different (be it just case or diacritics difference). The downside of it that the sort order is not natural. An example of unnatural sort order (as in “binary” is) : A,B,a,b Natural sort order would be in this case e.g … Read more
It sets how the database server sorts (compares pieces of text). in this case: SQL_Latin1_General_CP1_CI_AS breaks up into interesting parts: latin1 makes the server treat strings using charset latin 1, basically ascii CP1 stands for Code Page 1252 CI case insensitive comparisons so ‘ABC’ would equal ‘abc’ AS accent sensitive, so ‘ü’ does not equal … Read more
I am contributing here, as the OP asked: How to change collation of database, table, column? The selected answer just states it on table level. Changing it database wide: ALTER DATABASE <database_name> CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; Changing it per table: ALTER TABLE <table_name> CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; Good practice is to … Read more
Building on your test data, but this works with arbitrary data. This works with any number of elements in the string. Register a composite type made up of one text and one integer value once per database. I call it ai: CREATE TYPE ai AS (a text, i int); The trick is to form an … Read more
change database collation: ALTER DATABASE <database_name> CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci; change table collation: ALTER TABLE <table_name> CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci; change column collation: ALTER TABLE <table_name> MODIFY <column_name> VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci; What do the parts of utf8mb4_0900_ai_ci mean? 3 bytes — utf8 4 bytes — utf8mb4 (new) v4.0 … Read more
Coerce to an accent insensitive collation You’ll also need to ensure both side have the same collation to avoid errors or further coercions if you want to compare against a table variable or temp table varchar column and because the constant value will have the collation of the database Update: only for local variables, not … Read more
The main difference is sorting accuracy (when comparing characters in the language) and performance. The only special one is utf8_bin which is for comparing characters in binary format. utf8_general_ci is somewhat faster than utf8_unicode_ci, but less accurate (for sorting). The specific language utf8 encoding (such as utf8_swedish_ci) contain additional language rules that make them the … Read more
In general, utf8_general_ci is faster than utf8_unicode_ci, but less correct. Here is the difference: For any Unicode character set, operations performed using the _general_ci collation are faster than those for the _unicode_ci collation. For example, comparisons for the utf8_general_ci collation are faster, but slightly less correct, than comparisons for utf8_unicode_ci. The reason for this is … Read more
This is generally caused by comparing two strings of incompatible collation or by attempting to select data of different collation into a combined column. The clause COLLATE allows you to specify the collation used in the query. For example, the following WHERE clause will always give the error you posted: WHERE ‘A’ COLLATE latin1_general_ci = … Read more