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.
274
questions
0
votes
1answer
40 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
81 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
110 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
14 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
183 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
36 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
39 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 ...
1
vote
1answer
447 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
70 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
44 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-...
3
votes
1answer
76 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
186 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
58 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 ...
0
votes
1answer
208 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
13 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 ...
0
votes
2answers
292 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
98 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
572 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
40 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
49 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
244 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
66 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 ...
5
votes
1answer
811 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
199 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 ...
2
votes
1answer
67 views
Extended Events Collation resolution error
When trying to expand the sessions under extended events I am getting a Collation Resolution error in SSMS 18.2 (15.0.18142):
Looking at a trace I have found that this query is what is giving me the ...
3
votes
1answer
67 views
Different sort despite same collation in Postgres
I am getting different sort results for items with diacritics even though the collation being used is apparently the same.
So, after installing Postgress.app in my mac box for development I create ...
3
votes
1answer
170 views
What difference does it make if my COLLATION_CONNECTION is utf8mb4_0900_as_cs or utf8mb4_0900_ai_ci
My default collation_connection was 'utf8_generic_ci' and it caused an issue today with a polish character not fitting, thowing an error (the table has utf8mb4_0900_as_ci)
So I changed the ...
3
votes
2answers
175 views
Default collation not working?
I don't understand why the default collation on my psqlserver 11 instance returns a different result than using with explicit collation.
Details:
show LC_CTYPE; -- de-AT
show LC_COLLATE; ...
6
votes
2answers
275 views
Change collation on partition column (SQL Server 2017)
I'm trying to change collation on column:
ALTER TABLE [dbo].[yourdad] ALTER COLUMN [yourmam] varchar(36) COLLATE SQL_Latin1_General_CP1_CI_AI NOT NULL;
and it gives message like this:
Msg 5074, ...
6
votes
1answer
921 views
Query to find rows containing ASCII characters in a given range
I am using some scripts from another topic, but the accepted answer isn't working for all my data scenarios. I would have asked my question on the original How to check for Non-Ascii Characters post, ...
1
vote
2answers
87 views
Replace function removing Ones / 1 when removing char(185) (superscript 1)
Ok as the title suggests this is occuring.
When using the REPLACE function on char(185) for data in a temporary table, ones ("1") are being removed as well.
SQL SERVER 2008R2
UNICODE Data
DB ...
19
votes
2answers
2k views
Which collation should I use for biblical Hebrew?
Which SQL Server collation should I use for biblical Hebrew? The database under consideration needs to accommodate diacritics (i.e., vowels, accents, trope, etc.).
19
votes
2answers
2k views
How does case-insensitive collation work?
Default collation type in SQL Server allows for indexing against case insensitive strings yet the case of the data is persisted. How does this actually work? I'm looking for the actual nuts and ...
1
vote
1answer
323 views
Is there a MySQL character set and encoding that will allow for both emojis and accents?
I've got a database of terms that get added to by one group of users, and queried against by another.
I was running into problems when people would query for an emoji in the database and my React app ...
2
votes
0answers
2k views
mysql to mariadb: unknown collation utf8mb4_0900_ai_ci
I have a mysql 8.0 that I exported using mysqldump. I am trying to import it onto a Mariadb 10.4 database with phpmyadmin, both are the most current versions. Each time I do it though, I get:
Error: ...
4
votes
1answer
629 views
Importing Chinese characters via SSIS
I'm trying to import file that contains Chinese characters into a table. File encoding is Big5 (Traditional).
Here is a sample file like one that I need to import: https://www.pastiebin.com/...
2
votes
1answer
112 views
Identify the version of a collation from ICU in Postgres
Postgres 10 and later incorporates the International Components for Unicode (ICU) library for text-handling and other internationalization issues.
Changes happen to human languages, such as sorting ...
0
votes
0answers
98 views
Errors with trying to run a stored procedure with local file output
I'm trying to run a stored procedure that I'm passing some variables to.
The output is being writted to a CSV file on a local file share.
The CSV file gets created, but I'm hitting some errors ...
1
vote
2answers
1k views
Locale is incompatible error in Postgresql 10 when replicating from Ubunu to Win 10
The locale available in my Ubuntu PG-10 DB is en_US.UTF-8. When doing PG_Basebackup to a PG-10 DB in Win-10, Psql failed to connect with error
psql: FATAL: database locale is incompatible with ...
1
vote
1answer
381 views
Why does setting my column as Always Encrypted change its collation from SQL_Latin1_General_CP1_CI_AS to Latin1_General_BIN2?
I am encrypting a VARCHAR(50) NULL column as randomized. When doing this, the collation is changed from SQL_Latin1_General_CP1_CI_AS to Latin1_General_BIN2.
I cannot find any resources explaining why ...
4
votes
3answers
4k views
PostgreSQL: difference between collations 'C' and 'C.UTF-8'
In PostgreSQL, what is the difference between collations C and C.UTF-8?
Both show up in rows of pg_collation. Is it perhaps the case that C.UTF-8 is the same as C with encoding UTF-8 regardless or ...
1
vote
2answers
556 views
MySQL and MariaDB: COLLATE on table vs COLLATE on all columns?
Will the following table behave or perform any differently
CREATE TABLE t1 (
foo char(60) COLLATE utf8_bin,
bar char(64) COLLATE utf8_bin,
baz char(80) COLLATE utf8_bin
) DEFAULT CHARSET=utf8;
...
5
votes
2answers
5k views
Change default collation for character set utf8mb4 to utf8mb4_unicode_ci
When creating a database without specifying a character set or collation the servers defaults are used (as expected).
MariaDB [(none)]> SHOW VARIABLES LIKE '%_server' ;
+----------------------+----...
1
vote
2answers
1k views
SQL Server default collation vs database with different collation - potential problems?
I'm struggling with the design of new database that has to use a different collation (Y Collation) than the SQL Server Instance default collation (X Collation). I'm looking for potential problems.
...
1
vote
1answer
644 views
MySQL Case Sensitive Join on Varchar
I have two tables, both with identical data types, character sets, and collation explicitly specified.
CLERK CHAR(3) CHARACTER SET latin1 COLLATE latin1_bin NULL
For a case-sensitive join on the ...
2
votes
1answer
211 views
Error: 468 trying to connect to SQL server with SQL Server management studio
I'm trying to connect to SQL Server 12 with SQL Server management studio 2014.
The connection is succesful, but when I try to see properties of a specific database with mouse right button I get this ...
0
votes
0answers
44 views
After upgrading from PG 9.3 to 10 indices became corrupted
So recently I was upgrading the database in production from Postgres 9.3 to 10. In addition to upgrade, we wanted to move the database to another machine. I know, doing it "in place" would be easier ...
4
votes
3answers
268 views
Sql server 2016 Change only server and without all databases [duplicate]
How can i change server collation without changing whole databases, because i have
many databases which aren't belong to me, so i want change server collation and my database only.
SQL Server ...
1
vote
0answers
471 views
Setting utf8mb4_unicode_ci as the default for all utf8mb4 character set tables
I am running MySQL 5.7.19 in RDS and recently went through a conversion of everything to Character Set = UTF8MB4 and Collation = UTF8MB4_unicode_ci. I set the following parameter group settings and ...
1
vote
1answer
119 views
Why doesn't MySQL coerce the collation to the column-specified, when comparing to a literal?
I have a table with a collation defined on a column:
CREATE TEMPORARY TABLE test_table (
utf8_col VARCHAR(255) CHARACTER SET utf8 COLLATE utf8_bin
);
INSERT INTO test_table VALUES('ã');
According ...
