Update in 2019-10-29
As mentions by @Manuel Jordan in comments, utf8mb4_0900_ai_ci is the new default in MySQL 8.0, so the following is now again a better practice:
CREATE DATABASE mydatabase CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;
Answer before 2019-10-29
Note: The following is now considered a better practice (see bikeman868's answer):
You should use:
CREATE DATABASE mydb CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
Note that utf8_general_ci is no longer recommended best practice. See the related Q & A:
What's the difference between utf8_general_ci and utf8_unicode_ci on Stack Overflow.
The UCS-2 encoding is always 2 bytes per character and has a range of 0 - 65535 (0x0000 - 0xFFFF). UTF-16 (regardless of Big Endian or Little Endian) has a range of 0 - 1114111 (0x0000 - 0x10FFFF). The 0 - 65535 / 0x0000 - 0xFFFF range of UTF-16 is 2 bytes per character while the range above 65536 / 0xFFFF is 4 bytes per character.
Windows and SQL Server ...
What are the downside of using LC_CTYPE='C' over a specific
The documentation mentions the relationship between locales and SQL features in Locale Support:
The locale settings influence the following SQL features:
Sort order in queries using ORDER BY or the standard comparison operators on textual data
The upper, lower, and initcap functions
First: There is no distinction, collation-wise, between biblical Hebrew and modern Hebrew. We are just dealing with Hebrew.
Second: Regardless of anything else, you want to use the newest set of collations, which are the _100_ series as they have newer / more complete sort weights and linguistic rules than the older series with no version number in the name ...
There is no such thing as a "vendor-agnostic" view of Collations, nor even "version-agnostic", since their implementations -- including which aspects can be made insensitive and their naming conventions -- are vendor-specific and change over time.
Here is a summary of what I have found, and the details are in the longer section below the line:
This problem can be solved using accent insensitive collations.
Your database is probably using a AS (Accent Sensitive) collation so by default it will search for the exact match including accents.
You could instruct the WHERE clause to use another collation than the database default by specifying a collation with the comparison.
In this dbfiddle I ...
To check for non-default collations on columns, you can use the following query:
where collation_name is not null
order by table_schema,
To find the collation of the database, you need to query ...
The C collation is the right choice.
Everything is a bit faster without locale. And since no collation is right anyway, create the database without collation, meaning with C.
It may be a pain to have to provide a collation for many operations. There shouldn't be a noticeable difference in speed between the default collation and an ad-hoc collation, though. ...
The trick here is to realize that these characters that you see in the question with the "accents" aren't really the characters (i.e. "These aren't the droidscharacters you are looking for" ;-) ). The "accents" are various types of notations indicating things like:
vowels (lines and dots that are typically under the letters):
base letter "ה" = "h"; "הֶ" =...
Collations in SQL Server determine the rules for matching and sorting character data. Normally, you would choose a collation first based on the comparison semantics and sorting order the consumers of the data require.
Humans generally do not find that binary collations produce the sorting and comparison behaviours they expect. So, although these offer the ...
indexing against case insensitive strings yet the case of the data is persisted. How does this actually work?
This is actually not a SQL Server specific behavior, it's just how these things work in general.
So, the data is the data. If you are speaking about an index specifically, the data needs to be stored as it is else it would require a look-up in the ...
[0-9] is not some type of regular expression defined to just match digits.
Any range in a LIKE pattern matches characters between the start and end character according to collation sort order.
RANK() OVER (ORDER BY Symbol COLLATE Latin1_General_CI_AS) AS Rnk
WHERE Symbol LIKE '[0-9]' COLLATE ...
@gbn already explained the basic reason and fix, but the specific reason for the behavior that you are seeing is this:
You are using a VARCHAR literal (no N prefix) instead of an NVARCHAR literal (string with N prefix), hence the Unicode character will get converted into VARCHAR.
VARCHAR is an 8-bit encoding that is, in most cases, one byte per character, ...
The documentation often gives you an answer to such questions. Like in this case, too:
The operator classes text_pattern_ops, varchar_pattern_ops, and bpchar_pattern_ops support B-tree indexes on the types text, varchar,
and char respectively. The difference from the default operator
classes is that the values are compared strictly character by
First of all, apologies for such a long answer, as I feel that still there is a lot of confusion when people talk about terms like collation, sort order, code page, etc.
From BOL :
Collations in SQL Server provide sorting rules, case, and accent sensitivity properties for your data. Collations that are used with character data types such as char and ...
You can append a new collation to your select query to find case sensitive or insensitive.
-- Case sensitive example
WHERE Name collate SQL_Latin1_General_CP1_CS_AS like '%hospitalist%'
-- Case insensitive example
WHERE Name collate SQL_Latin1_General_CP1_CI_AS like '%hospitalist%'
Just be aware of the ...
So en_US.UTF-8 and en_US.utf8 have different sort orders?
No, these both are the same, just a different naming convention.
I'm mystified by why the Debian version appears to be case-insensitive and the OS X version is not.
Yes, you are correct. This is the default behavior on Mac. Collations don't work on any BSD-ish OS (incl. OSX) for UTF8 encoding.
The recommendation of keeping all column collations to the database default seems more like guidelines or best practices to me.
You are entirely correct here.
Why is it considered such a serious error by some?
For the same reason that you will often hear / read that "you should never use:"
etc, etc, etc
You can get some info by inspecting a backup file using the RESTORE HEADERONLY command. The linked documentation explains what all of the result set fields are and mean, but the ones you are looking for should be:
8 = SQL Server 2000
9 = SQL Server 2005
10 = SQL Server 2008 or 2008 R2 (see [SoftwareVersionMinor] for distinction)
11 = ...
The official word from Microsoft:
Some of the columns that contain pre-defined strings (like types, system descriptions, and constants) are always fixed to a specific collation – Latin1_General_CI_AS_KS_WS. This is irrespective of instance/database collation. The reason is that this is system metadata (not user metadata) and basically these strings are ...
The behavior you are seeing here is due, in a general sense, to the fact that the Unicode Collation Algorithm (UCA) allows for complex, multi-level sorting. More specifically:
Sorting is not Comparison:
Determining whether two strings are the same or different is fairly straight forward (given a particular locale/language and set of sensitivities). But ...
Disclosure: I’m the author of How to support full Unicode in MySQL databases, the guide you’re following.
Where did you save the modified settings? Check where mysqld loads the default options from. It’s usually /etc/my.cnf (as mentioned in the guide) but it may be different on your system. Run the following command to find out:
$ mysqld --help --verbose 2&...
While you can use a scalar function such as UPPER or LOWER and you can re-collate the column so that it's no longer case sensitive, these approaches all require data conversion be done against the base data which will never allow for an index seek. You also are leading your LIKE with a wildcard, so this isn't as much of a concern for you in this scenario ...
Both this and the COLLATE answer will impact performance, due to them making the query non-SARGable, but the easiest way to do so (as Edgar suggested in a comment) is:
WHERE LOWER(Name) LIKE '%hospitalist%'
WHERE UPPER(Name) LIKE '%HOSPITALIST%'
I suggest you pick a collation that provides the default Unicode ordering. That way, you get sane results even if you don't override the collation in each query. Unfortunately, most (all?) operating systems don't provide a locale that is simply named "default Unicode" or something like that, so you will have to guess and/or research a good choice. For ...
The one without the code is a version 90 (SQL Server 2005) collation, used by previous versions of Windows / SQL Server. The collation including 100 is newer (SQL Server 2008).
There's a bit of an overview of the differences in the SQL Server 2008 documentation, here, however it doesn't go into great detail.
If you're developing a new application, you may ...
Collation determines two things.
The code page used (and hence characters that can be stored) for non unicode data.
The comparison semantics for all textual data.
(NB: SQL Server 2012 also introduces _SC, supplementary character, collations for UTF-16 encoding rather than UCS-2 but these are not relevant to your question here)
You need to use NCHAR(1 - 4000) or NVARCHAR, either as NVARCHAR(1 - 4000) or NVARCHAR(MAX) for storing anywhere from 4001 to just over 1,073,741,822 characters (or possibly less if storing any supplementary characters as described below).
Technically, you can store Japanese characters in VARCHAR fields if you use a Japanese_* Collation that is associated ...