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 ...
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 ...
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 ...
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
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.
@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, ...
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 ...
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 ...
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&...
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 ...
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 ...
Sorting strings naturally puts "15" before "2" because the first digit in the "15" is a "1", which sorts before "2". Sorting a "2" stored in a string type before the "15" can be done in a few ways. The most efficient way is to have the collation itself handle this internally. This option ...
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 ...
This question is not so related to databases but more on Unicode handling and rules.
Based on https://docs.microsoft.com/en-us/sql/t-sql/statements/windows-collation-name-transact-sql Latin1_General_100_CS_AS means: "Collation uses the Latin1 General dictionary sorting rules and maps to code page 1252" with the added CS = Case Sensitive and AS = Accent ...
COLLATE operates per predicate or per expression, depending on context. COLLATE is mainly used to control how the string values are being compared or sorted. Hence it is most commonly used in JOIN, WHERE, and HAVING predicates, as well as GROUP BY and ORDER BY clauses (in which case it can be used per column / expression). It needs to be applied to string ...
Without having an example of the data as well as the table DDL from the O.P., it is difficult to say for certain what the exact cause of this error is for the O.P. However, this behavior (and hence problem) can happen for others for the following reason:
Some Code Pages (which are determined by the Collation of each CHAR / VARCHAR field) allow for double-...