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.
369 questions
0
votes
1
answer
74
views
Any downsides of using UTF8 collation in SQL Server 2019?
We have some columns in the table that could possibly get some non-ANSI characters and we must store them. Before the SQL Server 2019 there was the only 1 option - NVARCHAR data type, however starting ...
-1
votes
1
answer
21
views
Website Content Not Displaying Correctly in Vietnamese After Restoring Database from Backup [closed]
I used the Scheduled Backups feature in Virtualmin to back up my website. My hosting setup used the latest version of Virtualmin on a VPS running Ubuntu 22.04. Unfortunately, my VPS expired because I ...
0
votes
0
answers
78
views
A collation that ignores cedillas for comparison?
Cedillas are mutations applied to letters in some languages, like portuguese, french, turkish and romanian. Some words: "coração", "açúcar", "linhaça" (letters 'Ç', 'Ş', ....
1
vote
1
answer
34
views
Illegal mix of collations
I am managing a MariaDB 10.6 Server for a bunch of people. I also dump the databases for backup purposes. Recently, one of the databases started to make problems while dumping:
mysqldump: Couldn't ...
1
vote
1
answer
54
views
MySQL: using NAME_CONST internally
I'm using MySQL 8.0.32
I have some questions about an internal conversion, that I don't understand. In addition to this, sometimes this conversion is shown in SHOW FULL PROCESSLIST, as its execution ...
1
vote
1
answer
48
views
Collation difference in PostgreSQL on AWS RDS and local machine
I have an issue with collation in PostgreSQL when sorting a result set. We use AWS RDS PostgreSQL, and we want to sort query results in a specific way. Locally, everything works perfectly on my laptop ...
11
votes
1
answer
1k
views
I can't select a certain record with like %value%
I have a table with this entry
ID
Navn
2700
Aaen Agro I/S v/Niels-Christian Aaen
Where Navn has the collation Danish_Norwegian_CI_AS. But when I
SELECT * FROM [dbo].[Bruger] WHERE [Navn] LIKE N'%...
0
votes
0
answers
33
views
Can I change collation_connection permanently? Having issues running queries in Navicat on a remote MariaDB server
I try to have everything utf8 with utf8mb4_unicode_ci collation. As far as I can see, everything is.
But occasionally in Navicat, I get an error message about an illegal mix of collations - that and ...
0
votes
1
answer
402
views
Why is the character set for the uca1400_ai_ci collation NULL?
I've been working with database collations of late. I generally don't like ambiguous queries such as SHOW COLLATION so I eventually found the source via the following query:
SELECT *
FROM ...
0
votes
1
answer
233
views
Does SQL Server application database and tempdb collation mismatch affect performance
I installed the SQL Server 2019 instance for our ERP with the default French_CI_AS collation.
The ERP uses Latin1_general_BIN and doesn't seem to have any problems. I only realised the potential issue ...
4
votes
1
answer
379
views
MSG 14262: The specified @server_name ('<server_name>') does not exist
We have an SQL Server 2019 instance (let's call it 'sample_instance') with server collation of Turkish_CI_AS. When I try to create a job, SSMS creates the part of the script that adds the target ...
0
votes
1
answer
13
views
Hidden Unicode General Collation via mysqli_get_charset even though everything else is 520 and 1400
I did a server migration and have been doing an audit. On the new server I finally had the freedom to upgrade MariaDB to 10.11 (needed 10.10 to get Unicode 14.0 support) and was previously stuck at ...
0
votes
0
answers
56
views
Mariadb 10.11 on Debian 12 and character_set_database
I have a stock mariadb instance on debian 12 with no edits to the config files.
When I create a table w/o specifying character set and collation, the table is created with latin1 and it's collation.
...
2
votes
0
answers
40
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 ...
1
vote
0
answers
55
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 &...
0
votes
0
answers
93
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 ...
0
votes
1
answer
155
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 ...
0
votes
1
answer
65
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
...
0
votes
2
answers
37
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 &...
3
votes
2
answers
292
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 ...
3
votes
2
answers
1k
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'ß' ...
3
votes
1
answer
108
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) ...
0
votes
1
answer
354
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', ...
1
vote
1
answer
166
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 ...
1
vote
0
answers
275
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 ...
0
votes
0
answers
156
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, ...
-3
votes
1
answer
532
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 ...
1
vote
2
answers
436
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=> ...
0
votes
0
answers
156
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;
...
5
votes
1
answer
1k
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 ...
2
votes
1
answer
335
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 ...
0
votes
1
answer
667
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:
...
2
votes
1
answer
581
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:...
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 ...
38
votes
3
answers
54k
views
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 ...
2
votes
0
answers
300
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')
)
...
2
votes
1
answer
44
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 (...
5
votes
3
answers
2k
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 ...
5
votes
1
answer
816
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 ...
5
votes
1
answer
949
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 = ...
0
votes
1
answer
1k
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 ...
4
votes
2
answers
772
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 @ ...
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, ...
3
votes
1
answer
5k
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 ...
5
votes
2
answers
2k
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 ...
4
votes
1
answer
645
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:
...
7
votes
2
answers
1k
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(...
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 ...
7
votes
4
answers
463
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 ...
2
votes
1
answer
468
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 ...