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.
301
questions
1
vote
1answer
19 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 ...
1
vote
1answer
28 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 ...
0
votes
1answer
42 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 ...
4
votes
1answer
164 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 ...
3
votes
2answers
280 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 ...
1
vote
1answer
238 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 ...
0
votes
1answer
99 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 ...
5
votes
1answer
217 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.
...
2
votes
1answer
43 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';
...
2
votes
1answer
57 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 ...
2
votes
2answers
273 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 ...
0
votes
2answers
68 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, ...
3
votes
3answers
260 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 ...
1
vote
1answer
85 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 ...
3
votes
2answers
1k 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 ...
1
vote
1answer
93 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 ...
6
votes
3answers
753 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....
1
vote
1answer
242 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 ...
1
vote
3answers
71 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 ...
1
vote
3answers
337 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 ...
4
votes
3answers
2k 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:
...
2
votes
1answer
89 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
-----...
8
votes
1answer
406 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 ...
4
votes
1answer
373 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++.
...
3
votes
1answer
358 views
What is the difference between different utf8mb4 binary collations?
What is the difference between utf8mb4_0900_bin vs utf8mb4_bin binary collations?
1
vote
1answer
407 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&...
0
votes
1answer
319 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 ...
1
vote
4answers
901 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 ...
1
vote
1answer
552 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 ...
0
votes
1answer
154 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.
...
4
votes
2answers
351 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?
1
vote
1answer
94 views
Change collation in easy way
I'm a developer and sometime I face with restoring a DB with a different collation than the server.
Our application uses the tempdb too and as result some joins fail due of the different collation.
So ...
1
vote
2answers
147 views
Changing from utf8 to utf8mb4
MySql database at the moment has:
Character Set: utf8
Default Collation Name: utf8_unicode_ci
I want to change it to:
utf8mb4
utf8mb4_unicode_ci
I would also do the same thing for all tables in ...
3
votes
1answer
2k views
PostgreSQL nondeterministic collations are not supported for LIKE
I am using Postgresql v12.
I created a collation like this:
CREATE COLLATION ci (provider = icu, locale = 'tr_TR', deterministic = false);
I used that collation in a table:
create table testtable1 ...
0
votes
0answers
175 views
How to convert UTF8 general in UTF8 unicode?
Short:
My tables have the collation utf8mb4_unicode_ci, but the data itself is utf8mb4_general_ci and now I like to know how I can convert it into unicode?
Long:
I used this query to convert all my ...
3
votes
3answers
268 views
Is there any performance issue with including the “COLLATE” as part of the query as opposed to setting it for the column/table?
PostgreSQL has this "collate" concept. You can either tell a table's column to always have a "collate", or you can do it in the query.
If I do it in the query, does PG punish me somehow performance-...
2
votes
1answer
360 views
What is the meaning of “-x-icu” in PostgreSQL's “collates”?
I had this query:
SELECT * FROM table ORDER BY label ASC;
Since the labels are not in English, they didn't get sorted in the right order (ones beginning with "ö" were not in the bottom/end).
I ...
4
votes
2answers
245 views
sp_Blitz fails with collation error
SQL Server version (output from select @@VERSION): Microsoft SQL Server 2017 (RTM-CU19) (KB4535007) - 14.0.3281.6 (X64) (Build 14393: )
Installed sp_Blitz from Brent Ozar, stored procedure creates ...
0
votes
1answer
156 views
Sybase ASE 15.7 sort ordering
I have my sort ordering in Sybase as:
45 190 altdict - Alternate (lower-case first) dictionary ordering
Am I right that alternate means "aAbBcC..zZ" ordering?
What should I do to change ordering ...
1
vote
1answer
806 views
MySQL 8.016 master with MariaDB 10.2 slave on AWS RDS, Character set '#255' is not a compiled character set
I have a MySQL 8.016 master and a MariaDB 10.2 slave, both running on AWS RDS. This was working perfectly until today, when I figured I wanted to do chain replication instead, like this
MySQL 8.016 ->...
0
votes
1answer
24 views
MySql8 Trailing String comparison
I am planning an upgrade of a legacy MySQL5.6 Database to MYSQL8.0.19 database.
All my testing has been successful but have one issue preventing me from going live.
By default in MYSQL5.6 the ...
1
vote
2answers
917 views
How to force mysqldump to produce /*!40101 SET character_set_client = utf8mb4 */;
I am attempting to dump my database, which is set to, and only contains, utf8mb4 and appropriate collations. I use the following line:
mysqldump --add-drop-table --add-locks --allow-keywords --...
1
vote
1answer
281 views
Different collation between System and User databases in SQL Server [duplicate]
Recently, I have came across a situation where I found there is different SQL Collation is set for System databases and user databases on a sql instance in production.
Like: Danish_Norwegian_CI_AS ...
8
votes
1answer
633 views
Is it possible to determine the default Collation and server version of a database without a full restore?
I work for a company who regularly receives large SQL database backups from clients for support purposes. We support multiple SQL versions and collations, but currently have to manually confirm our ...
1
vote
0answers
281 views
Difference between LC_COLLATE='C' and LC_COLLATE='C.UTF-8' in PostgreSQL [duplicate]
I need guidance on the exact distinction, if any, between LC_COLLATE='C' and LC_COLLATE='C.UTF-8' in PostgreSQL. It seems to me that they behave exactly the same and that the codeset (with bearing on ...
0
votes
0answers
96 views
Will MariaDB treat ut8/utf8mb3 and utf8mb4 collations as compatible?
Background on relevant character sets
MySQL and MariaDB support the deprecated utf8mb3 character set and collations. (utf8 is currently an alias for utf8mb3.) This supports all Unicode code points on ...
1
vote
2answers
578 views
Oracle to T-SQL OPENQUERY special character conversion issues
I'm struggling to figure out where the character encoding issue on my Linked server may be coming from here. The ZPDT_PAT_ALPHA column should have a degrees symbol at the end, as shown by the DUMP.
...
2
votes
2answers
69 views
Why are collations specified at the column level?
The charset defines how the row data is saved into the file store.
Row 17: <CHARSET('utf8', 'hello'), 17, CHARSET('latin-1', 'sup')>
But it seems that collation only affects sorting of these ...
8
votes
1answer
2k views
Case-insensitive collation still comparing case-sensitive
I am currently trying to create a table with a text column which will compare case insensitive by default. This is because we have a third party program that executes a search on our database. The ...
0
votes
1answer
496 views
MySQL- Performance implications of changing a column collation from latin1_swedish_ci to latin1_bin
I am a developer so was hoping someone with DBA experience could help me out here.
I am using a MySQL database (MYSQL 5.6).
I came across a situation where a particular column stores Ids generated ...
