82
votes
Create a MySQL database with charset UTF-8
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 ...
- 920
50
votes
Accepted
How do I set a SQL Server Unicode / NVARCHAR string to an emoji or Supplementary Character?
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). ...
- 67.7k
38
votes
Accepted
Ignore accents in 'where' clause
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.
...
- 15.6k
35
votes
Which collation should I use for biblical Hebrew?
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 ...
- 67.7k
33
votes
Accepted
Does any DBMS have a collation that is both case-sensitive and accent-insensitive?
TL;DR
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 ...
- 67.7k
31
votes
Why is my PostgreSQL ORDER BY case-insensitive?
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 ...
- 8,544
31
votes
Accepted
Why does a comparison between 'tr' & 'tR' fail on a SQL Server with Vietnamese_CI_AI collation?
Given that this behavior is present in the newer version of that collation, and that combinations such as "fr" and "fR" do match (as expected), it could only be culture-specific ...
- 67.7k
30
votes
Accepted
Can't update "CO2" to "CO₂" in table row
The subscript 2 is not part of the varchar character set (in any collation, not just Modern_Spanish). So make it a nvarchar constant:
UPDATE test SET description = N'CO₂' WHERE id = 1;
- 69.5k
30
votes
Accepted
How to do a case-insensitive LIKE in a case-sensitive database?
You can append a new collation to your select query to find case sensitive or insensitive.
-- Case sensitive example
SELECT *
FROM TABLE
WHERE Name collate SQL_Latin1_General_CP1_CS_AS like '%...
- 3,122
27
votes
Accepted
Latin1_General_BIN performance impact when changing the database default collation
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 ...
- 80.3k
27
votes
How to treat numbers within strings as numbers when sorting ("A3" sorts before "A10", not after)
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 ...
- 67.7k
26
votes
Accepted
How does case-insensitive collation work?
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 ...
- 67.7k
22
votes
Can't update "CO2" to "CO₂" in table row
@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 (...
- 67.7k
22
votes
Accepted
PostgreSQL: difference between collations 'C' and 'C.UTF-8'
The PostgreSQL documentation leaves a lot to be desired (just sayin' 😼 ).
To start with, there is only one encoding for a particular database, so C and C.UTF-8 in your UTF-8 database are both ...
- 67.7k
19
votes
Accepted
Why is mixing column collations in a single database considered bad?
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 ...
- 67.7k
18
votes
Accepted
Is it possible to determine the default Collation and server version of a database without a full restore?
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 ...
- 67.7k
17
votes
Accepted
What's up with the collation of some columns in sys.databases?
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 – ...
- 178k
17
votes
Accent Sensitive Sort
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 ...
- 67.7k
17
votes
How to do a case-insensitive LIKE in a case-sensitive database?
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 ...
- 9,242
17
votes
mysql to mariadb: unknown collation utf8mb4_0900_ai_ci
I've solved my issue by replacing all occurrences of utf8mb4_0900_ai_ci with utf8mb4_general_ci in my SQL dump file.
- 271
16
votes
How to do a case-insensitive LIKE in a case-sensitive database?
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 '%...
- 9,752
15
votes
Accepted
Sort order with numbers as strings
String sort order is determined by the collation. Users in different locations expect data to be sorted differently and the collation codifies those expectations.
When not explicitly specified, ...
- 80.3k
13
votes
Accepted
Accent Sensitive Sort
This question is not so related to databases but more on Unicode handling and rules.
Based on https://learn.microsoft.com/en-us/sql/t-sql/statements/windows-collation-name-transact-sql ...
- 1,091
12
votes
Accepted
What are the rules for using COLLATE in a query?
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, ...
- 67.7k
12
votes
Accepted
Case-insensitive collation still comparing case-sensitive
The version of ICU shipped with the Windows builds is a pretty old one, so maybe that's the reason.
Try
CREATE COLLATION collat_ci (
provider = 'icu',
locale = '@colStrength=secondary',
...
- 42.6k
12
votes
Accepted
Fix corrupted utf8 nvarchar value that was inserted without N prefix
It is not possible to recover the original value.
Character constants without the N prefix to denote national characters are translated according to the database default collation code page. In cases ...
- 26.7k
11
votes
What is the impact of LC_CTYPE on a PostgreSQL database?
In reference to Daniel’s accepted answer about sorting using collations, please be aware that if you are running PostgreSQL on a Mac that your preferred collation may not function as you expect due to ...
- 111
11
votes
Latin1_General_BIN performance impact when changing the database default collation
Given that this is an existing database that already has tables defined in it, there are some very serious implications to the action of changing the database collation, beyond the potential ...
- 67.7k
11
votes
Accepted
"String or binary data would be truncated" error copying nvarchar(10) to char(10)
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 ...
- 67.7k
11
votes
Accepted
Any way of optimizing joins between 2 databases with different collations?
If you can add columns to tables, you can try persisted computed columns, and index those. For example:
ALTER TABLE dbo.DeliveryDetail ADD DeliveryNoteNo_Collated COLLATE whatever PERSISTED;
CREATE ...
- 42.5k
Only top scored, non community-wiki answers of a minimum length are eligible