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.

35 questions with no upvoted or accepted answers
Filter by
Sorted by
Tagged with
7
votes
0answers
5k 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
0answers
187 views

Azure SQL DB migration from Visual Studio raises collation error

I want to run a migration in my Azure hosted database. The migration works perfect in several local machines. When I try targeting the Azure database it raises the following exception: Column X is ...
3
votes
0answers
724 views

Output from `pg_dump --schema-only` has inconsistent order

I'm trying to compare schemas between 2 (identical) databases using pg_dump --schema-only in PostgreSQL 9.3.9, but the diff of the output shows the ordering is inconsistent for two triggers: 2896c2896 ...
2
votes
0answers
2k views

Use LIKE in column with arabic

I have a SQL Server 2012 DB with a column that includes Arabic text. The column is nvarchar(MAX) with collation Arabic_CI_AI_KS_WS. To test if LIKE searches are working I copied some of the text ...
2
votes
0answers
132 views

Linked servers not ignoring case after reboot

We have a SQL Server 2014 database with collation SQL_Latin1_General_CP1_CI_AS, which is case insensitive. It is linked to another SQL server database with the same collation. The second database ...
2
votes
0answers
1k views

Prevent mysqldump from writing column-level collate and charset?

Using mariadb 10.x, it looks like the mysqldump utility adds column-level collate even if the table was created without them. Is there a way to suppress this behavior? Table created: CREATE TABLE `...
2
votes
0answers
3k views

Collation in variables

After having many issues with ISO-8859-1 and reading a lot about collations I finally understand that I need to use charset UTF8 collate utf8_unicode_ci. So I configure my whole application with these ...
2
votes
0answers
1k views

What do LC_TIME and LC_NUMERIC settings actually change in PostgreSQL?

According to the PostgreSQL 9.5 documentation, LC_TIME affects "Formatting of dates and times" and LC_NUMERIC - "Formatting of numbers", but I can't seem to see any difference from changing these. ...
2
votes
0answers
347 views

Import from dbf - collation

I select data from dbf files through OPENROWSET Visual FoxPro OLEDB. Despite forcing collation on columns (e.g. SELECT name COLLATE Polish_CI_AS FROM Employees) the polish specific characters keep ...
2
votes
0answers
460 views

Performance Implications of Explicit Collation Conversions across Linked Servers

I locally host host databases from two vendors on two different SQL instances, but on the same network. These servers frequently communicate with each other (through joins and whatnot), so we have ...
2
votes
0answers
550 views

Avoiding Functions() in the Where clause

Okay, while analyzing queries against a SQL Server 2008 R2 database, I happened upon the following query: (@p__linq__0 nvarchar(4000)) SELECT TOP (1) [Extent1].[OrderSOId] AS [OrderSOId], [Extent1] ...
2
votes
0answers
556 views

mysql/mariadb: ifnull changes collation

It seems that 'ifnull' changes the default collation that is set to be used. I have tried on both mysql and mariadb: Server version: 5.5.34-MariaDB-1~precise-log mariadb.org binary distribution ...
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
0answers
570 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
0answers
326 views

SQL AlwaysON Turkish Collation Problem During Setup

I have 2 servers with SQL Server 2014 SP2 and windows 2012 R2 installed on them. When I tried to create availability group it failed with the primary replica is not active But when I tried to ...
1
vote
0answers
809 views

Trying to fix collation mess

In our MySQL database we have set several collations. e.g. Connection collation utf8_unicode_ci Server configured default collation utf8_unicode_ci Database default collation utf8_general_ci Table ...
1
vote
0answers
100 views

SQL Server 2008 R2 SP2 Execution Plan not getting missing index suggestions

I have several SQL Server 2008 R2 SP2 that have server collation Latin1_General_BIN. These instances will not give me the missing index suggestion with performing an execution plan. I have other ...
1
vote
0answers
263 views

Speed up changing of collation?

I'm trying to change collation on all our databases, and doing it on one of the databases (~15gb) took me 42 minutes in testing even though I put it on SSD disc. Do anyone know any way to speed it up? ...
1
vote
1answer
1k views

Search for Arabic text ignoring diacritics, alef hamza differences, and kashida in SQL Server and Oracle

Well, I have a table with an Arabic column which may contain variant forms of the same word in multiple rows, for example the word "أسمى" might be in the following forms: with diacritics: "أَسْمَى"; ...
1
vote
2answers
4k views

mysqldump collation issues

I have tried my hand at building a shell script to backup our wordpress database using tar balls and chron jobs on a Debian environment. The problem I am facing now is that our wp database after ...
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. ...
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 ...
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 ...
0
votes
0answers
95 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 ...
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 ...
0
votes
0answers
157 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 ...
0
votes
0answers
87 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 ...
0
votes
1answer
325 views

Cleaning up bad data with REGEXP_REPLACE too aggressive with non latin chars

Trying to use REGEXP_REPLACE to clean up a text field in a table. Here's my query: select title original ,REGEXP_REPLACE(title, '[^a-zA-Z0-9]+', ' ') clean from tmp_table2 WHERE txn_title like '%?%' ...
0
votes
0answers
367 views

Resolving “there is unknown charset/collation names” warnings on views

I recently moved our MySQL 5.6 server over to MariaDB 10.3.10 and, for the most part, have not had any problems. We have a couple of databases that have close to 1000 views which are now filling the ...
0
votes
1answer
38 views

Code not working due to collation?

Let me preface my question by saying, that my teacher stared at my code for 2h and could only guess that it might be the collation. Here's the problem: for an exercise we were asked to create a movie ...
0
votes
1answer
78 views

Match similar special character in filters

I have to perform a search on a table with Turkish city names. The users would enter the first 4 characters of a city and I need to fetch the results from SQL. For example, user wants to find the ...
0
votes
0answers
58 views

mysql collation-server not respected

I've configured my server (5.5.41) with: character-set-server = utf8 collation-server = ...
0
votes
1answer
172 views

Implicitly (or proxy) convert to Nvarchar on incoming queries from legacy or 3rd party applications

We have a 3rd party application of which we don't have the source code. This application executes queries on our database without using the "N" prefix before literal strings. It does this in inserts ...
-1
votes
1answer
2k views

Workbench Error Code 1267 - utf8_unicode and utf8_general

I've recently just swapped all of a databases tables and columns to utf8_unicode_ci from the weird collection of about 3-5 different collations they were previously (don't ask... not my database) So, ...
-1
votes
1answer
51 views

converting mysql datebase

I have a question about converting mysql databases, I doing now new site, and want to use my old database, which is in latin_swedish_1 colacolation I want to convert this old database into ...