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
1 answer
22 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 ...
user avatar
  • 101
2 votes
1 answer
103 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. ...
user avatar
1 vote
1 answer
54 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 ...
user avatar
1 vote
2 answers
79 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", ...
user avatar
  • 13
4 votes
1 answer
416 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-...
user avatar
  • 229
0 votes
0 answers
100 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 ...
user avatar
  • 111
6 votes
1 answer
496 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 ...
user avatar
  • 2,347
0 votes
1 answer
162 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'; ...
user avatar
2 votes
1 answer
39 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 ...
user avatar
  • 21
1 vote
1 answer
795 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 ...
user avatar
1 vote
2 answers
78 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. ...
user avatar
  • 149
4 votes
2 answers
273 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 ...
user avatar
3 votes
1 answer
171 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 ...
user avatar
  • 81
1 vote
2 answers
263 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 ...
user avatar
1 vote
1 answer
253 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 ...
user avatar
  • 1,087
1 vote
1 answer
34 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, ...
user avatar
  • 113
0 votes
4 answers
260 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....
user avatar
  • 126
3 votes
1 answer
89 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 ...
user avatar
  • 181
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 ...
user avatar
1 vote
1 answer
411 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 if this conversion is safe? Or does ...
user avatar
  • 111
1 vote
1 answer
518 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 ...
user avatar
  • 13
0 votes
1 answer
131 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 ...
user avatar
  • 231
4 votes
1 answer
332 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 ...
user avatar
  • 143
4 votes
2 answers
2k 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 ...
user avatar
  • 163
1 vote
1 answer
1k 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 ...
user avatar
  • 17
0 votes
1 answer
1k 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 ...
user avatar
5 votes
1 answer
1k 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. ...
user avatar
2 votes
1 answer
249 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'; ...
user avatar
  • 425
3 votes
1 answer
1k 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 ...
user avatar
  • 425
2 votes
2 answers
642 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 ...
user avatar
  • 61
0 votes
2 answers
76 views

Strange behavior of TOP 1 clause sql server

I have a SQL Server database and restored it on another server on a different machine. Now I run following same query on both DBs which produces same below result set. select person_id, visit_id, ...
user avatar
3 votes
3 answers
1k views

to change collation for system database SQL Server 2017

We are using fresh SQL Server 2017 CU23 Standard Edition database with "SQL_Latin1_General_CP1_CI_AS". Now, I want to change all system databases (master , model , temp, msdb) to another ...
user avatar
  • 1,048
1 vote
1 answer
305 views

MySQL illegal mix of collations, ASCII to UTF-8

I have a MySQL 8.0 table like this, which is UTF-8 apart from one field, which holds an ASCII UUID that doesn't need UTF-8 overhead: CREATE TABLE `things` ( `id` bigint unsigned NOT NULL ...
user avatar
  • 251
3 votes
2 answers
4k views

How do I fix the Code Page in SSIS Lookup Transformation to be 65001?

I have an SQL server 2019 with databases and tables all set with the collation Latin1_General_100_CI_AS_SC_UTF8. The relevant table has code and desc columns which are both of data type varchar. In ...
user avatar
1 vote
1 answer
521 views

Is LC_COLLATE different from COLLATE?

Postgres 12 on Windows 7 This works: CREATE DATABASE "foo" ENCODING 'UTF8' LC_COLLATE = 'en-US' LC_CTYPE = 'en-US' TEMPLATE template0 Afterwards, running SHOW LC_COLLATE ...
user avatar
  • 425
8 votes
3 answers
2k views

How to treat numbers within strings as numbers when sorting ("A3" sorts before "A10", not after)

For all of these queries: SELECT label FROM personal.storage_disks ORDER BY label ASC; SELECT label FROM personal.storage_disks ORDER BY label COLLATE "C" ASC; SELECT label FROM personal....
user avatar
1 vote
1 answer
718 views

Does PostgreSQL handle case-insensitive database as good as others? [closed]

First things first, please forgive me if my question seems so naive (I am not a DBA). I look up to see how can I make PostgreSQL case-insensitive and I found the Nondeterministic Collations section of ...
user avatar
1 vote
3 answers
119 views

Handle development issues when dealing with different collation for the instance/database setting

Looking for some guidance on how we can handle the scripts/schemas when being run on different collation in sql server. We have a process where multiple stored procs are created lets say with schema ...
user avatar
1 vote
3 answers
931 views

Japanese characters in VARCHAR column using Japanese_Unicode_CI_AS

I am loading a table with the Japanese names from CSV file that are changing into question marks into SQL Table. The table that is storing these values is a varchar column. I understand that the ...
user avatar
4 votes
3 answers
5k views

Case Insensitive ORDER BY clause using COLLATE

I have spent a long time looking for this, and I am getting mixed messages. In other DBMSs (tested in SQLite, Oracle, MariaDB, MSSQL) I can override the default sort order using the COLLATE clause: ...
user avatar
  • 2,347
2 votes
1 answer
226 views

postgres: do I really need to use Collate and Ctype when restoring database?

Need to import in Linux a Windows postgres exported database. In windows it's listed as follows: Name | Owner | Encoding | Collate | Ctype | Access privileges -----...
user avatar
  • 123
8 votes
1 answer
671 views

Changing collation of SQL Database in Production environment to minimize downtime

We have a scenario in which we want to change the collation of our production database (including columns) from SQL_Scandinavian_Pref_CP850_CI_AS to Finnish_Swedish_CI_AS. We have developed scripts to ...
user avatar
4 votes
1 answer
866 views

SQL Server with French_CI_AS Collation date conversion

I have a French installation of SQL Server using French_CI_AS Collation. For legacy reasons, a table column stores data as VARCHAR which is later converted to the right type, akin to variant in C++. ...
user avatar
4 votes
1 answer
981 views

What is the difference between different utf8mb4 binary collations?

What is the difference between utf8mb4_0900_bin vs utf8mb4_bin binary collations?
user avatar
  • 394
1 vote
1 answer
995 views

ERROR: collation "database_default" for encoding "UTF8" does not exist

Moving application from SQL Server to PostgreSQL Database. I am getting this error from one of the queries used in a Java file: ERROR: collation "database_default" for encoding "UTF8&...
user avatar
0 votes
1 answer
408 views

Is it possible to specify a different collation for the data vs the entities within a single database?

I have a database with a case sensitive collation because the data inside of it should be treated as case sensitive in the context of how it's used. The entities and their column names within that ...
user avatar
  • 24.7k
1 vote
4 answers
2k views

Error "the system cannot find the file specified" when opening a large SQL Server file

I have generated the script of my whole SQL Server database into a file and it's more than 36 GB. When I try to open it, I get the following error: The system cannot find the file specified. At ...
user avatar
  • 119
1 vote
1 answer
1k views

What is the SQL Server equivalent for Oracle character set AL32UTF8?

While porting data from oracle to Azure sql database I am facing the following issue where, empty data in Oracle is converted as ➞➞➞ in SQL Server Pic1: Data as in Oracle DB; Pic2: Data as in Sql ...
user avatar
  • 131
0 votes
1 answer
357 views

postgresql 12.3 convert existing DB to use case-insensitive collation?

postgresql 12.3 in a container with Alpine Linux 3.10 I would like to convert an existing DB to use case-insensitive collation. Is it possible? I mean, without dropping it and recreating from scratch. ...
user avatar
  • 101
4 votes
2 answers
541 views

Does MySQL 8 ASCII vs utf8mb4_0900_ai_ci size differ when only using ASCII characters?

If I only use only ASCII characters, will VARCHAR (255) with utf8mb4_0900_ai_ci be larger on disk than VARCHAR (255) using ASCII?
user avatar
  • 177

1
2 3 4 5
7