Skip to main content
Share Your Experience: Take the 2024 Developer Survey
84 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 ...
bikeman868's user avatar
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). ...
Solomon Rutzky's user avatar
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. ...
Tom V's user avatar
  • 15.7k
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 ...
Solomon Rutzky's user avatar
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 ...
Solomon Rutzky's user avatar
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 ...
Solomon Rutzky's user avatar
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;
gbn's user avatar
  • 69.9k
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 '%...
Shaulinator's user avatar
  • 3,142
30 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 ...
Solomon Rutzky's user avatar
27 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 using ...
Solomon Rutzky's user avatar
27 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 ...
Solomon Rutzky's user avatar
26 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_unicode_520_ci in my SQL dump file.
Francesco Borzi's user avatar
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 (...
Solomon Rutzky's user avatar
20 votes

mysql to mariadb: unknown collation utf8mb4_0900_ai_ci

In MariaDB use utf8mb4_unicode_520_ci instead, or you may even use uca1400_as_ci if you have a newer version of MariaDB. Don't use "_general_ci", as it does not correctly sort or compare ...
thomasrutter's user avatar
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 ...
Solomon Rutzky's user avatar
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 ...
Solomon Rutzky's user avatar
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 ...
Solomon Rutzky's user avatar
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 ...
John Eisbrener's user avatar
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 '%...
BradC's user avatar
  • 9,964
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, ...
Paul White's user avatar
  • 85.7k
14 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', ...
Laurenz Albe's user avatar
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 ...
Patrick Mevzek's user avatar
13 votes

Best General Purpose Character Set and Collation for MySQL

For any version of MySQL or MariaDB, use utf8mb4 with its default COLLATION. (Well, that was not available before 5.5.3.) 0900 refers to Unicode's 9.00. 520 refers to Unicode's 5.20. No number refers ...
Rick James's user avatar
  • 78.4k
12 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 ...
cafecoder905's user avatar
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, ...
Solomon Rutzky's user avatar
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 ...
Dan Guzman's user avatar
  • 28.3k
11 votes

Why are there different Database Collations for different languages?

(The following copies portions of the first part of my answer to a related DBA.StackExchange Q&A: Why not use SQL_Latin1_General_CI_AS for a global system? and adds some additional information.) ...
Solomon Rutzky's user avatar
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 ...
Brent Ozar's user avatar
11 votes

Trigger to change Database collation on creation

You would need to use dynamic SQL, and the EVENTDATA() function. USE master GO CREATE TRIGGER trg_DDL_ChangeCOllationDatabase ON ALL SERVER FOR CREATE_DATABASE AS SET NOCOUNT ON; DECLARE @...
Erik Darling's user avatar
11 votes
Accepted

N'Șc' considered duplicate key of N'C' using Latin1_General_CI_AS collation

The problem is that the old SQL Server Collations (i.e. those with names starting with SQL_) and the first two versions of the Windows Collations (the 80 series which came with SQL Server 2000 and do ...
Solomon Rutzky's user avatar

Only top scored, non community-wiki answers of a minimum length are eligible