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

334 questions
211
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,221
  • 2
  • 11
  • 4
40
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.
  • 503
  • 1
  • 4
  • 6
38
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
  • 495
  • 1
  • 4
  • 6
34
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,831
  • 1
  • 17
  • 30
33
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,152
  • 4
  • 16
  • 25
31
votes
1 answer

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,298
  • 4
  • 25
  • 39
28
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
  • 437
  • 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,525
  • 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,313
  • 7
  • 26
  • 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
3 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
  • 211
  • 1
  • 2
  • 3
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
  • 68,431
  • 22
  • 166
  • 303
20
votes
2 answers

Accent Sensitive Sort

Why do these two SELECT statements result in a different sort order? USE tempdb; CREATE TABLE dbo.OddSort ( id INT IDENTITY(1,1) PRIMARY KEY , col1 NVARCHAR(2) , col2 NVARCHAR(2) ); GO INSERT dbo.OddSort (col1, col2) VALUES (N'e',…
Aram
  • 203
  • 1
  • 5
20
votes
2 answers

Can't update "CO2" to "CO₂" in table row

Given this table: CREATE TABLE test ( id INT NOT NULL, description NVARCHAR(100) COLLATE Modern_Spanish_CI_AS NOT NULL ); INSERT INTO test (id, description) VALUES (1, 'CO2'); I've realised I can't fix a typographic issue: SELECT * FROM…
Álvaro González
  • 1,059
  • 4
  • 16
  • 30
19
votes
4 answers

SQL Server 2005/2008 UTF-8 Collation/Charset

I can't find option(s) directly to set UTF-8 rellated Collations/Charsets in SQL Server 2005/2008, same as is possible to set in another SQL engines, but in SQL Server 2005/2008 are there only Latin and SQL collations. Is there some option to…
mKorbel
  • 597
  • 2
  • 12
  • 25
1
2 3
22 23