Skip to main content
OverflowAI is here! AI power for your Stack Overflow for Teams knowledge community. Learn more

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
2 votes
0 answers
17 views

Postgres uses wrong index when using collation

I have a table with 1000 records. I created two indexes with different collation. create INDEX test_en on webshop.address (tenant_id, city collate "en-x-icu"); create INDEX test_de on ...
Florat's user avatar
  • 21
1 vote
0 answers
29 views

Does PostgreSQL trigram search support collations?

I'm building a trigram search using PostreSQL extension pg_trgm. I want the search to be case insensitive and to ignore accents. I've tried to achieve that by using this collation: CREATE COLLATION &...
Draex_'s user avatar
  • 111
0 votes
0 answers
26 views

How to create a PostgreSQL collation ignoring all accents

I'm trying to develop a trigram search using pg_trgm PostgeSQL extension. The search should be case insensitive and ignore accents in Slovak language. I've tried to achieve this by creating a custom ...
Draex_'s user avatar
  • 111
0 votes
1 answer
39 views

In Postgres, duplicate rows on a column are not detected by select group by, but unique index built is failing with duplicate values

I have a table with users and column username. I am trying to build an index on the users#username column but it is failing with an error for a duplicate value. The value detected is an Arabic ...
p.matsinopoulos's user avatar
0 votes
1 answer
33 views

odbc--call failed. [Microsoft][ODBC SQL Server Driver][SQL Server]A collation conflict ... in the UNION ALL operator ... cannot be resolved. (#451)

I union all the distinct columns of the INFORMATION_SCHEMA.COLUMNS views of many databases which I query with a direct query: select TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME from xyz ...
questionto42's user avatar
0 votes
2 answers
27 views

List of characters managed by a collation

I'm migrating to MySQL 8.0.33. We are changing character set to utf8mb4. For collation, I'm wondering among different options. I'm looking for documentation to review the list of characters that are &...
yaki_nuka's user avatar
  • 141
3 votes
2 answers
276 views

What is the effect of using different COLLATE values in a WHERE clause?

I read the documentation 'Using COLLATE in SQL Statements' https://dev.mysql.com/doc/refman/8.0/en/charset-collate.html, but it doesn't say much about the effect of using different COLLATE values to ...
Computist's user avatar
  • 165
3 votes
2 answers
711 views

What is the collation used while comparing Unicode string literals in SQL Server 2019?

My understanding is that the collation for comparing Unicode string literals is determined by the database collation. My database is using SQL_Latin1_General_CP1_CI_AS collation. When I compare N'ß' ...
QFirstLast's user avatar
3 votes
1 answer
33 views

postgreSQL collation problem when comparing a column value with a given value

Assume that we created a table and inserted a record with the following script. CREATE TABLE your_table ( your_column VARCHAR COLLATE "tr-TR-x-icu" ); INSERT INTO your_table(your_column) ...
Orhan Sönmez's user avatar
0 votes
1 answer
158 views

ERROR: operator class does not exist for access method "btree"

I've encountered an issue while attempting to execute a script. Here's the scenario: -- Script to create collation CREATE COLLATION case_insensitive (PROVIDER = icu, LOCALE ='und-u-ks-level2', ...
Shiwangini's user avatar
1 vote
1 answer
75 views

Postgres sorting order not respecting default C collation?

I have a postgres DB running in an AL2023 docker image: postgres=# select version(); select version(); version ...
Richard Wheeldon's user avatar
0 votes
0 answers
83 views

MySQL 8: show deprecated error only as warning

I'm migrating MySQL from 5.7 to 8.0.32. I know character set utf8mb3 and its collations are deprecated. But we have lots of functionality and data with utf8mb3, and we don't have more time to change ...
yaki_nuka's user avatar
  • 141
1 vote
0 answers
95 views

How to correctly set the database, table, and column collation in MySQL?

Background I am experiencing problems setting the collation in my local MySQL database I am using for a Drupal site I am developing. I attempted to create a local database with charset utf8mb4 and ...
GeorgeCiesinski's user avatar
0 votes
0 answers
88 views

Having the same charset and collation between Redshift and MySQL

I looking to migrate data between Redshift table and MySQL that has a string column. There are a few differences between each engine: Redshift is case sensitive, and by default MySQL isn't. MySQL, ...
Nir's user avatar
  • 533
-3 votes
1 answer
486 views

System dbs have different collation

i am new on this forum.i have installed an instance of sql srv 2016 sp2 called DNF containing 2 dbs and also the system dbs.after providing the instance to the dev team the realise that they need to ...
Toni's user avatar
  • 1
1 vote
2 answers
202 views

Why is the default collation not applied in PostgreSQL queries?

OS: Windows 10 Docker OS: Alpine Linux 3.18.3 DB: PostgreSQL 15 I created a new PostgreSQL database named test1 with zh-x-icu as the default collation using the following command: postgres=> ...
David H. J.'s user avatar
0 votes
0 answers
88 views

Creating a new database in MySQL is always uses latin1_bin collate even if latin1_general_ci has been specified

When creating a new database where I specify the character set and the collating sequence the database is created with the correct characterset but the wrong collating sequence. -- DROP DATABASE test; ...
SoftAllan's user avatar
  • 101
5 votes
1 answer
890 views

How to determine the collation of an index in PostgreSQL?

In PostgreSQL 15, when an index is created with an implicit collation, the default collation used for the index is documented: By default, the index uses the collation declared for the column to be ...
soliz's user avatar
  • 293
2 votes
1 answer
295 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
472 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
457 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
7 votes
3 answers
3k views

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

I need to store emails (like '[email protected]') in Postgres 15, be able to search them in case-insensitive manner ('[email protected]', '[email protected]', etc are the same), and be able to retrieve the ...
IvanD's user avatar
  • 311
2 votes
0 answers
196 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
36 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
1k 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
638 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
731 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
784 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
3 votes
2 answers
579 views

Numeric collation sorts by digits instead of value- postgres

I have a table of users with ids in the following form [email protected]. When searching and sorting the users, I need [email protected] to be before [email protected] but since 4 is “smaller” than @ ...
Amit Keinan's user avatar
2 votes
1 answer
1k 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
3 votes
1 answer
3k 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
1k 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
601 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
  • 447
7 votes
2 answers
805 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
1k 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
  • 765
7 votes
4 answers
437 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
  • 765
2 votes
1 answer
418 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
2 votes
1 answer
2k 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
  • 123
2 votes
1 answer
391 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
673 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
4 votes
2 answers
3k 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
  • 177
4 votes
1 answer
13k 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
3k 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
6k 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,819
3 votes
1 answer
7k 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
171 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
  • 31
2 votes
1 answer
11k 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
573 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
2k 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
381 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
2 3 4 5
8