Database Administrators Stack Exchange
Database Administrators Stack Exchange

Questions tagged [collation]

A collation is a set of rules that determine how data is sorted and compared for comparing characters in a character set.

A collation is a set of rules that determine how data is sorted and compared for comparing characters in a character set.

Here some kind of collation rules:

  • Case sensitivity
  • Accent sensitivity
  • Kana Sensitivity
  • Width sensitivity

Depending on the RDBMS we can have all or a subset of collation

370 questions
221
votes
2 answers

Create a MySQL database with charset UTF-8

I'm new to MySQL and I would like to know: How can I create a database with charset utf-8 like I did in navicat? create mydatabase; ...seems to be using some kind of default charset.
user3397998
  • 2,321
  • 2
  • 13
  • 4
55
votes
5 answers

mysql to mariadb: unknown collation utf8mb4_0900_ai_ci

I have a mysql 8.0 that I exported using mysqldump. I am trying to import it onto a Mariadb 10.4 database with phpmyadmin, both are the most current versions. Each time I do it though, I get: Error: Unknown collation utf8mb4_0900_ai_ci Then I went…
Frosty
  • 551
  • 1
  • 4
  • 3
45
votes
2 answers

What is the impact of LC_CTYPE on a PostgreSQL database?

So, I've few Debian servers with PostgreSQL on it. Historically, those servers and PostgreSQL are localized with the Latin 9 charset and back then it was fine. Now we have to handle things like Polish, Greek or Chinese, so changing it become a…
Gregoire D.
  • 553
  • 1
  • 4
  • 6
40
votes
2 answers

How to determine the collation of a table in PostgreSQL?

I want to script a check of the collations used on my tables in PostgreSQL, but googling for Postgresql detect collation is not working well for me, and the documentation is not making this an easy search. Can anyone tell me how I would check this?
Jeremy Holovacs
  • 1,271
  • 4
  • 20
  • 27
40
votes
1 answer

Why is my PostgreSQL ORDER BY case-insensitive?

I have Postgres 9.4.4 running on Debian and I get the following ORDER BY behavior: veure_test=# show LC_COLLATE; lc_collate ------------- en_US.UTF-8 (1 row) veure_test=# SELECT regexp_split_to_table('D d a A c b CD Capacitor', ' ') ORDER BY…
Curtis Poe
  • 515
  • 1
  • 4
  • 6
39
votes
3 answers

Collation version mismatch

I replicated a database, it's working fine but when I enter the replication database it displays the following message: The database was created using collation version 2.31, but the operating system provides version 2.35. Rebuild all objects in…
Miqueias Kevison
  • 750
  • 1
  • 5
  • 9
36
votes
2 answers

Why would you index text_pattern_ops on a text column?

Today Seven Databases in Seven Weeks introduced me to per-operator indexes. You can index strings for pattern matching the previous queries by creating a text_pattern_ops operator class index, as long as the values are indexed in lowercase. CREATE…
Iain Samuel McLean Elder
  • 2,408
  • 5
  • 26
  • 39
35
votes
1 answer

How do I set a SQL Server Unicode / NVARCHAR string to an emoji or Supplementary Character?

I want to set a Unicode string variable to particular character based on its Unicode code point. I want to use a code point beyond 65535, but the SQL Server 2008 R2 database has a collation of SQL_Latin1_General_CP1_CI_AS. According to Microsoft's…
Riley Major
  • 1,965
  • 4
  • 19
  • 37
30
votes
3 answers

How to choose a collation for international database?

I'm designing a database which will store data in different languages (using UTF-8), so I think the best way to display the query's results is ordering it according to the user's language during the query itself (because there are more than one…
Tae
  • 457
  • 1
  • 5
  • 8
28
votes
4 answers

How to change SQL Server collation

How could I change the SQL Server 2008 R2 Express Default Collation for the whole server and a particular database? Is there a way to do it using visual interface of SQL Server Management Studio? In the Server Properties window (and in the…
rem
  • 1,555
  • 3
  • 20
  • 22
25
votes
3 answers

Which collation should I choose for a muiti-language website?

Does a collation have any influence over a query speed? Does the size of a table change depending of the collation? If I want to build a website that must support all possible languages (lets take for e.g. Google) which would be the recommended…
BrunoLM
  • 3,533
  • 7
  • 28
  • 22
25
votes
1 answer

Ignore accents in 'where' clause

In our database we have multiple entries with grave accents, carets or carons (aka hačeks). These accents are collectively also known as diacritical marks. Now our users want to find entries including these diacritical marks when they search for…
lumo
  • 445
  • 2
  • 6
  • 13
21
votes
4 answers

How to do a case-insensitive LIKE in a case-sensitive database?

My vendor requires the data warehouse database to be case sensitive, but I need to do case-insensitive queries against it. In a case-sensitive database, how would you write this to be case-insensitive? Where Name like '%hospitalist%'
James
  • 2,668
  • 5
  • 28
  • 51
21
votes
4 answers

What's up with the collation of some columns in sys.databases?

I'm attempting to run an UNPIVOT on various columns contained in sys.databases across various versions of SQL Server, ranging from 2005 to 2012. The UNPIVOT is failing with the following error message: Msg 8167, Level 16, State 1, Line 48 The type…
Hannah Vernon
  • 70,928
  • 22
  • 177
  • 323
20
votes
3 answers

Set value of character_set_client to utf8mb4

I'm trying to convert my DB to utf8mb4 following this guide. I have set: [client] default-character-set=utf8mb4 [mysql] default-character-set=utf8mb4 [mysqld] init-connect='SET NAMES…
qwaz
  • 305
  • 1
  • 2
  • 7
1
2 3
…
24 25