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.

Filter by
Sorted by
Tagged with
0 votes
0 answers
13 views

Save Japanese string to the mysql 8.0 database

To accept Japanese strings and save them in MySQL 8.0, I setup the field with the utf8mb4_0900_ai_ci collation, but on some strings, it returns the "Conversion from collation utf8_general_ci into ...
Rijin's user avatar
  • 1
2 votes
1 answer
227 views

An issue with a character in Kazakh case insensitive collations

I have an issue with Сyrillic characters 'E' and 'e' which should be equal when a case insensitive collation is used. And that is correct for all collations apart from the Kazakh ones. I checked all ...
Denis Rubashkin's user avatar
0 votes
1 answer
47 views

PostgreSQL doesn't sort according to `LC_COLLATE=C` even though DB is configured to

UPDATE: after initdbing with libc local provider (instead of icu), it started to work fine. Is this a bug or ICU simply assigns something else to "C"? I created a database explicitly with: ...
Stanislav Bashkyrtsev's user avatar
2 votes
1 answer
204 views

Explicitly define a column collation even if it's the database default

SQL Server seems to go out of its way to remove a COLLATE restriction on a nvarchar column if the collation is the DB default. My DB default is SQL_Latin1_General_CP1_CI_AS. If I create a table thus:...
Jez's user avatar
  • 685
6 votes
3 answers
2k views

Why not use two columns instead of collation for case insensitive data?

I need to store emails (like 'ivan@email.com') in Postgres 15, be able to search them in case-insensitive manner ('iVaN@email.com', 'IVAN@email.com', etc are the same), and be able to retrieve the ...
IvanD's user avatar
  • 291
2 votes
0 answers
58 views

How set default collation in Oracle by default BINARY_CI for new Tables?

I wrote script to change all tables default collation: BEGIN FOR rc IN ( SELECT * FROM ALL_ALL_TABLES WHERE owner='SchemaName' AND TABLE_NAME NOT IN ('__EFMigrationsHistory') ) ...
Alex's user avatar
  • 21
2 votes
1 answer
26 views

What exactly are all these English "collate" variants in PG?

When searching through all available "collates" related to "English" in PostgreSQL, I find these descriptions very odd: English (United States) English (United Kingdom) English (...
user291937's user avatar
5 votes
3 answers
481 views

Differences in PostgreSQL 14 collation behavior on Linux and Mac

I have PostgreSQL installed on my Mac and Ubuntu Server as shown below: atsweb=# select version(); version ...
Christian Alis's user avatar
5 votes
1 answer
239 views

postgresql matching or converting utf-8 variant strings

Postgres 13 I am looking for a way to search UTF-8 text that may have variant character representations ( what is the proper term for this? ie 𝐋𝐈𝐅𝐄 vs life ) within postgresql. I am running into ...
unomi's user avatar
  • 153
4 votes
1 answer
272 views

Using collations with JSONB

I have a postgres database and a case insensitive collation. The collation was created by CREATE COLLATION IF NOT EXISTS case_insensitive (provider = icu, locale = 'und-u-ks-level2', deterministic = ...
Pux's user avatar
  • 141
0 votes
1 answer
199 views

Modify Collation of a column

mysql Ver 8.0.25 Got this error in apache log: Error query: SQLSTATE[HY000]: General error: 1267 Illegal mix of collations (utf8mb4_unicode_ci,IMPLICIT) and (utf8_general_ci,COERCIBLE) for operation ...
dr0i's user avatar
  • 121
2 votes
2 answers
115 views

Numeric collation sorts by digits instead of value- postgres

I have a table of users with ids in the following form user123@domain.com. When searching and sorting the users, I need user1@domian.com to be before user14@domain.com but since 4 is “smaller” than @ ...
Amit Keinan's user avatar
2 votes
1 answer
239 views

sp_blitz fails with: Msg 468, Level 16, State 9 Cannot resolve collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Latin1_General_BIN"

Has anyone had an issue with running sp_blitz on an instance using the Latin1_BIN collation? I created the stored procedure without error, but when I ran it I got the following error message: Msg 468, ...
SQLSeeker's user avatar
2 votes
1 answer
714 views

Collation conflict when creating stored procedure

I'm trying to create a stored procedure but it gives me the error message Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Latin1_General_BIN2" in ...
Martin Riddar's user avatar
5 votes
2 answers
596 views

Case-Sensitive Collation on a select query in SQL Server

Our SQL Server's collation is set to SQL_Latin1_General_CP1_CI_AS I have a requirement where we have the same values with different cases but SQL Server treats them the same since our collation is set ...
Hanro Rachel's user avatar
4 votes
1 answer
522 views

Fix corrupted utf8 nvarchar value that was inserted without N prefix

Is it possible to restore the UTF8 data from a corrupted nvarchar, that was used without the N prefix? For example, in the following snippet, I'd like @n1_fixed to have the correct value based on @n1: ...
HeyJude's user avatar
  • 425
6 votes
2 answers
467 views

Collation conflict when comparing sql_variant with varchar literal

I found that queries below return different results on two Azure Hyperscale databases that are running the same compatibility level, same set options etc. IF CAST(ServerProperty('Edition') AS nvarchar(...
Lukas.Navratil's user avatar
5 votes
2 answers
365 views

At finding a substring, find the end position as well

A LIKE clause can test if a string occurs in another and the CHARINDEX function can give the start position of the first match. In my case, I'm interested in the end position though, which is, due to ...
John's user avatar
  • 755
7 votes
4 answers
394 views

Relationships between like matching and comparison

Naively I assumed that the relationship between a like 'foo%' and a >= 'foo' is that the latter matches the former and some additional rows that come after in the index order. More generally, I ...
John's user avatar
  • 755
2 votes
1 answer
337 views

simulate case sensitive collation in ssms

I know that it is not possible to set a collation for a session in sql server. I have different servers with different collations, however, one of them is a case sensitive collation and that can be ...
Marcello Miorelli's user avatar
1 vote
1 answer
556 views

SQL Server collation and its equivalent in Postgres

Legacy system, backed by SQL Server, has unique indexes using collation Latin1_General_CP1_CI_AI. It compares things using charset Latin 1, encoded using CP-1252 (CP1 part), case insensitive, accent ...
Ginden's user avatar
  • 113
2 votes
1 answer
226 views

SQL Server PATINDEX issue/bug when using different case-sensitive collations

I have a function (which I found here years ago) that uses STUFF / PATINDEX to strip out non-alphanumeric characters from a string. When running on a Case Insensitive collation, it works fine. ...
Jay Austin's user avatar
1 vote
1 answer
264 views

Case sensitivity not working

I have an issue where I keep getting the value 'CtP_PETER_Fact' out of the query below. It should be a case sensitive where-clause. I tried it in a few different ways: setting the COLLATE statement ...
Niels Broertjes's user avatar
2 votes
2 answers
1k views

MySQL What charset/collation for Case insensitivity and Accent sensitivity?

I am looking for a charset/collation that would make it so when I do a SELECT * FROM table_name WHERE username = "Warrior" It only returns me the rows where username = "Warrior", ...
lyeaf's user avatar
  • 155
4 votes
1 answer
6k views

Storing special characters (German, French, Spanish) in SQL Server result in weird strings

I'm trying to store the German ß in SQL Server, but it results in ß. For example "Gleimstraße" is stored as "Gleimstraße" But this also happens for other characters: "König-...
Adam's user avatar
  • 245
2 votes
1 answer
1k views

mysql: setting default default_collation_for_utf8mb4?

How to set the value for "default_collation_for_utf8mb4" to "utf8mb4_general_ci", globally , or when the mysql 8.0 server started ? I tried [mysqld] init_connect='SET ...
neobie's user avatar
  • 131
6 votes
1 answer
3k views

Sort order with numbers as strings

For demonstration purposes, I have a sample table with a varchar column called numberstring with the values: -4 32 0 4 16 8 -8 1024 When selecting using ORDER BY numberstring, I find that different ...
Manngo's user avatar
  • 2,549
0 votes
1 answer
3k views

Collation won't set when creating new database

I am trying to set the collation for a new database in PostgreSQL 13 but it does not seem to take effect: postgres=# CREATE DATABASE assets ENCODING 'UTF8' LC_COLLATE 'C' LC_CTYPE 'en_US.UTF-8'; ...
leetbacoon's user avatar
2 votes
1 answer
72 views

Alter the collation of a column in temp-file

I have a routine in a DB that uses a temp-file. But the collation of the DB doesn't match the collation of the temp-DB. I can't change the collation of the DB, it's a client DB. I tried this: BEGIN ...
Florian's user avatar
  • 21
1 vote
1 answer
6k views

Change SQL Server 2019 instance Collation After installation, and new problem

How to made this problem on SQL Server 2019 Install SQL Server with wrong collation 2month ago Revived a report from SharePoint admin about has a problem with collation Resolve problem with MSSQLTIPs ...
Amirhossein's user avatar
1 vote
2 answers
338 views

Determine Ideal Collation Set for correct data storage

Alright so I have a MariaDB DB and I've just noticed some weird storage issues, like the following: My JSON columns (which in MariaDB are LONGTEXT) have an automatically set collation of utf8mb4_bin. ...
DevelJoe's user avatar
  • 163
4 votes
2 answers
1k views

Identify the query causing collation conflict

I have inherited a new SQL Server database instance. The SQL Server Agent error Log is full of collation errors - new errors are arising every 10 seconds. But detail of error is minimal - 'cannot ...
doninwales's user avatar
2 votes
1 answer
265 views

How to reverse only English words?

I've an nvarchar column with English and Hebrew letters in some fields. In the fields where the English and Hebrew are together, the English letters are in reverse order (this is how the ERP program ...
Avi's user avatar
  • 71
1 vote
2 answers
658 views

AWS Aurora Postgres 12 Setting Collation

I posted this on stack overflow as well, but didn't get much traction over there. If I am not able to get some thoughts on here, then I will probably open a support ticket with AWS. I am trying to set ...
Eshara Mondal's user avatar
1 vote
1 answer
1k views

Collation for accent-insensitive comparison on Postgres?

On PG 13 documentation, there are several examples of ICU collations for specialized purposes. It is also mentioned that ICU locales exist that allow creating collations to ignore accents, and that ...
ARX's user avatar
  • 1,319
1 vote
1 answer
139 views

Can I set in MariaDB the collation based on a IETF language tag?

I have an application where each user account can set their preferred application language. I store those language preferences as IETF BCP47 language tag (e.g. "de", "en-US"). Now, ...
Florian's user avatar
  • 113
0 votes
4 answers
735 views

Why can't set variables related collation as utf8mb4_unicode_ci?

Show mysql version here: mysql --version mysql Ver 15.1 Distrib 10.5.11-MariaDB, for debian-linux-gnu (x86_64) using EditLine wrapper And my mariadb's configuration file. cat /etc/mysql/mariadb....
showkey's user avatar
  • 224
3 votes
1 answer
107 views

case sensivity incongruity

I'm trying to learn and understand collations and how postgresql compare and order strings but I'm finding an incongruity and I don't know what I'm missing. The query: SELECT datname, datcollate FROM ...
Héctor's user avatar
  • 257
14 votes
1 answer
1k views

Why does a comparison between 'tr' & 'tR' fail on a SQL Server with Vietnamese_CI_AI collation?

There seems to be something special about 'tR' in Vietnamese collation. Appreicate if anyone who knows about it can explain in simple terms. This issue was discovered during the installation of our ...
QFirstLast's user avatar
2 votes
1 answer
1k views

What is the impact of converting latin1/latin1_swedish_ci to utf8mb4/utf8mb4_unicode_ci?

I was facing some issues with the character's encoding. Those are resolved by updating the CHARACTER and COLLATE for some columns in the table. So my concern is Is this conversion safe? Or can this ...
Mehar's user avatar
  • 121
1 vote
1 answer
1k views

MySQL dump to MariaDB issue with collation

I have a django app running on a staging server (windows server 2012) with a MariaDB database. I have a git hook running mysqldump locally when I push to the staging branch. On the server I have a ...
Samoht's user avatar
  • 13
0 votes
1 answer
397 views

What does this statement mean: SELECT ... COLLATE SQL_Latin1_General_CP1_CI_AS AS [-‌- Logins To Be Created --]

What does this statement mean? SELECT ... COLLATE SQL_Latin1_General_CP1_CI_AS AS [-- Logins To Be Created --] It is part of a script I'm trying to adapt for listing out the SQL Server logins in an ...
PTL_SQL's user avatar
  • 287
4 votes
1 answer
627 views

What is difference between Arabic_100_CS_AS_KS_WS_SC_UTF8 and Latin1_General_100_CS_AS_KS_WS_SC_UTF8?

Starting from SQL Server 2019, it supports UTF-8 as collation. However, according to the following queries: SELECT COLLATIONPROPERTY('Arabic_100_CS_AS_KS_WS_SC_UTF8', 'CodePage') SELECT ...
Eng.Fouad's user avatar
  • 143
4 votes
2 answers
6k views

Best General Purpose Character Set and Collation for MySQL

Currently, whenever I create a new MySQL database, I use utf8mb4 as a character set and utf8mb4_unicode_520_ci for the collation, e.g.: CREATE DATABASE IF NOT EXISTS db_name DEFAULT CHARACTER SET ...
GTS Joe's user avatar
  • 183
1 vote
1 answer
2k views

Cannot resolve the collation conflict between "Latin1_General_CI_AS" and "SQL_Latin1_General_CP1_CI_AS" in the equal to operation

Need urgent help on this. We have applied latest sql patches on one of the server (SQL 2014, SP3, CU4) and somehow we were not able to start the SQL due to following error. Cannot resolve the ...
Chickoo's user avatar
  • 17
1 vote
1 answer
4k views

Storing emojis in MySQL

I'm new to MySQL database. I want to store emojis in my database. I already have data in my database, and I don't want to lose it. I don't know the proper way of storing the utf8mb4 character-set ...
Cavin blake's user avatar
5 votes
1 answer
2k views

Msg 6355 "Conversion of one or more characters from XML to target collation impossible" when querying sys.dm_exec_query_plan

I like to find missing indexes on the go, looking at the execution plans! It can potentially give me an indication where further to look at if I want to improve something that is currently running. ...
Marcello Miorelli's user avatar
2 votes
1 answer
687 views

Equivalent of utf8_general_ci in Postgres/ICU?

In MySQL there is a collation utf8_general_ci which provides case-insensitive comparisons in a variety of languages. For example, these are all 1 (true): SELECT 'ı' = 'I' SOLLATE 'utf8_general_ci'; ...
AndreKR's user avatar
  • 537
3 votes
1 answer
3k views

Is there a difference between text_pattern_ops and COLLATE "C"?

If I have a text column name with a collation, for example tr-TR, and I run a query SELECT * FROM t WHERE name LIKE 'a%' then this will do a seq scan. If I now create an index CREATE INDEX ON t(name ...
AndreKR's user avatar
  • 537
2 votes
2 answers
1k views

How to create a new column in SELECT CASE when string contains Arabic words?

I have a problem with a select case when statement, I would like to add a new column in the select case when statement, I got the result but with ????? because I have set the column to Arabic words, I ...
abdou31's user avatar
  • 61

1
2 3 4 5
7