A collation is a set of rules that determine how data is sorted and compared for comparing characters in a character set.

learn more… | top users | synonyms

0
votes
1answer
18 views

Will column size change when trying to use single unicode character in place of multiple ASCII characters?

I have a column in utf8_unicode_ci collation for text type in MySQL. The content is in Unicode, I have used tags like {br}, {v}, {t}, {h} etc.., throught the database. I am now thinking of changing ...
2
votes
1answer
30 views

Was using `set datcollate='C'` a mistake?

In a pre-production environment running django 1.6 on PostgreSQL 9.1 we had a problem with database records arriving in a different order from what the application was expecting. So we ran this ...
4
votes
1answer
142 views

SQL Server ; working around collation mismatches

Imagine the following scenario: I have two servers which are on two separate private networks. A linked server has been created, and there is a table in server A which has an updated version of the ...
18
votes
3answers
1k views

Which collation should I choose for a muiti-language website?

Does a collation have any influence over a query speed? Does the size of a table change depending of the collation? If I want to build a website that must support all possible languages (lets take ...
1
vote
0answers
25 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 ...
1
vote
1answer
80 views

MySQL hang during “show collation” on Amazon RDS

MySQL is hanging when I try to do SHOW COLLATION on my Amazon RDS instance. I can use SHOW COLLATION fine with my local database, and other SQL commands work fine on my RDS instance. But Amazon SHOW ...
1
vote
2answers
37 views

MySQL Duplicate entry for a greek word

I have a column which is unique and when I add Αγάθη and Αγαθή (which are different) it says #1062 - Duplicate entry 'Αγαθή' for key 'word'. My collation is set to utf8_general_ci. Is there any ...
2
votes
1answer
53 views

What is the impact of LC_CTYPE on a PostgreSQL database?

So, I've few Debian servers with PostgreSQL on it. Historically, those servers and PostgreSQL are localized with the Latin 9 charset and back then it was fine. Now we have to handle things like ...
0
votes
0answers
16 views

mysql collation-server not respected

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

Operator “~<~” uses varchar_pattern_ops index while normal ORDER BY clause doesn't?

Let's say I have a table with 1 000 000 records. Structure of table is: create table individual ( id serial primary key, surname varchar(128), "name" varchar(128), patronymic ...
0
votes
0answers
42 views

How to convert a MySQL database from utf8mb4 to utf8?

I would like to convert a MySQL database, which has utf8mb4 character set and utf8mb4_unicode_ci collation, to utf8 and utf8_unicode_ci. How to do that please? Thank you in advance for your ...
1
vote
1answer
153 views

Not able to solve issue: Incorrect string value: '\xC5\xABt\xC4\x97'

I am facing database error Incorrect string value: '\xC5\xABt\xC4\x97'. I did a lot search and tries a few ways, include move from utf8 to utf8mb4. But still not able to solve it. Following are the ...
0
votes
1answer
70 views

Storing math,physics equations in mysql Database

I am working on a project where different unicode characters has to be stored in table. math equations, physics formulae , subscript X2 , superscript X2. greek characters like Link I have made all ...
6
votes
1answer
4k views

How to determine the collation of a table in PostgreSQL?

I want to script a check of the collations used on my tables in PostgreSQL, but googling for Postgresql detect collation is not working well for me, and the documentation is not making this an easy ...
1
vote
1answer
72 views

utf8 and utf8mb4

I am converting a few of my tables to utf8mb4 as well as the connection from PHP to MySQL to utf8mb4. This is so that I can support characters used for Emoji in my website. I have two questions ...
-1
votes
1answer
40 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 ...
5
votes
2answers
122 views

Is there a (non-binary) MySQL collation that doesn't treat different mathematical symbols as the same character?

I've run into a real headache with MySQL's collations and non-BMP characters (ones with Unicode codepoints above U+FFFF). Basically, given a table and data like: CREATE TABLE `math` ( `id` int(11) ...
2
votes
1answer
84 views

Help Changing Collate of ALL databases

I have approximately 80 Databases with various collations. I would like to modify the collation of all 80 databases. I have the following code: USE [COLLATE_ADM] GO SET ANSI_NULLS ON GO SET ...
1
vote
1answer
125 views

Custom Collation on MySQL Unknown [closed]

I've created a new collation as per the instructions mysql. In fact this tutorial was the exact use-case I needed. I've added the collation, and restarted mysql. When I try to run ALTER TABLE ...
4
votes
1answer
425 views

Converting a Unicode value to a non-Unicode value (SQL Server)

Unicode 9619 is a character called "Dark shade" (http://unicode-table.com/en/search/?q=9619) ▓. Using the SQL_Latin1_General_CP1_CI_AS collation and 1252 code page, I would expect that casting / ...
1
vote
0answers
60 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 ...
3
votes
2answers
1k views

What's the meaning of the Collation Code (blank, 90, 100, 110) in SQL Server 2012

During the installation of SQL Server 2012, in the Collation tab, i can choose between French_CI_AS and French_100_CI_AS. What do they mean and which one is newer? When there's no Code, is it newer ...
4
votes
3answers
4k views

SQL Server Collation for Arabic, Hebrew, English and French

I have new SQL Server database which need to have support in 3 different languages. What character sets and collations do I need to choose if I want my database to support the following: Hebrew ...
1
vote
1answer
107 views

Cannot remove char 0x00 from NVARCHAR column

So , I know all about the Replace function and a bug with char(0). I have a column (NVARCHAR(128)) that has some CHAR(0x00) from a bad import. I'm using SQL Server 2008 R2 The collation for the ...
1
vote
0answers
46 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? ...
3
votes
1answer
80 views

PL/pgSQL - Indexes vs Collation vs Pattern Ops

I'm using PostgreSQL 9.3. Having already read articles and answers on DBA stackexchange: Why would you index text pattern ops on a text column PostgreSQL documentation: Indexes - Opclass Pattern ...
2
votes
2answers
94 views

Using collate: When to use and when not to use

In one of my applications we had tsql like shown below SELECT DISTINCT [Dblist].[DbName] AS [DbName] FROM [Common].dbo.[Dblist] WHERE dbname not in ( SELECT [name] FROM ...
9
votes
2answers
2k views

Treating certain Arabic characters as identical

In Arabic we have characters like ا (alef) and أ (alef with hamza). Users write them interchangeably and we want to search them interchangeably. SQL Server treats them as separate characters. How ...
2
votes
1answer
61 views

Should I use utf8_unicode_ci or utf8_slovak_ci?

I was using utf8_slovak_ci collation until now, but I read that utf8_unicode_ci sorts very wide range of languages. Does it mean, I do not need to use separate collation for every language and I can ...
2
votes
0answers
119 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] ...
-1
votes
1answer
92 views

Does changing the collation of a column change TIMESTAMP (rowversion) values?

I need to run ALTER TABLE ... ALTER COLUMN ... COLLATE Czech_CI_AS ... for multiple columns in a database. All those tables have TIMESTAMP column which is used for database synchronization. Will ...
0
votes
1answer
142 views

In DB2 LUW 9.5, is there any way to change the collating sequence without recreating the db?

The documentation says "no" :-) I'm looking for some heady old DBA to whisper dark secrets about cludgy hacks that I'll get fired for sure for using ;-)
0
votes
0answers
90 views

Python - ascii encoding error when updating field content in SQL Server table

I know there are plenty of posts and blog explanations about unicode errors, but I still can't figure out to handle it in my particular case. I've already asked this question on GIS.StackExchange but ...
1
vote
1answer
526 views

Why is there a collation conflict between system views

I'm using the following statement to retrieve a list of triggers from the database: select tr.name from sys.triggers tr union all select tr.name from sys.server_triggers tr; However on my local SQL ...
0
votes
1answer
50 views

How can I find duplicate key violations in a different collation?

I have a MySQL table that has the collation for one of its varchar columns in the primary key set to utf8_general_ci. I want to change the collation to utf8_unicode_ci so it stops treating "gmail.com" ...
0
votes
0answers
117 views

utf8mb4 in SQL Server Express

We have some web applications based on databases using utf8mb4 as character set and utf8mb4_Standard as collation. We saw that we can use any character we want with this setting. In SQL Server ...
4
votes
2answers
5k views

How to choose a collation for international database?

I'm designing a database which will store data in different languages (using UTF-8), so I think the best way to display the queries results is ordering it according to the user's language during the ...
0
votes
2answers
140 views

Thai and English in a postgresql

I have a postgresql DB with Thai and English values/strings in several tables. Currently I have the following settings | Encoding | Collate | Ctype | | UTF8 | en_US.UTF-8 | en_US.UTF-8 ...
5
votes
1answer
8k views

Moving from SQL 2005 [SQL_Latin1_General_CP1_CI_AS] to 2008 - will I lose any features by using 'backwards compatibility'

We're moving from SQL 2005 [Instance and DB have collation of SQL_Latin1_General_CP1_CI_AS] to SQL 2008 [which defaults to Latin1_General_CI_AS]. I completed a SQL 2008 R2 installation, and used ...
4
votes
1answer
250 views

SQL Server: TSQL or safe steps needed: Change collation en masse for group of tables

Looking for a safe TSQL (preferred) or safe steps (rather automated if possible) to change collation on dozens of tables in one throw. Background: We have a db, SQL Server 2005, with ~100 tables. ...
1
vote
3answers
1k views

Inserting Serbian Latin and Cyrillic characters

I am using SQL Server 2012 Express as part of my asp.net development workstation. Database and table collation is set to Latin1_General_CI_AI. Attribute types are nvarchar(max) and varchar(max). ...
5
votes
1answer
4k views

MySQL: Illegal mix of collations

I've tried using a stored procedure to create indices, and got the following error: ERROR 1267 (HY000): Illegal mix of collations (utf8_general_ci,IMPLICIT) and ...
0
votes
1answer
137 views

DB collation is used for comparison instead of column collation

I am moving data from one SQL Server to another. lets call them source and destination SQL server. The source SQL Server has collation SQL_Latin1_General_CP1_CI_AS and the destination has collation ...
0
votes
1answer
68 views

SORT_SEQUENCE is wrong in as400 library indexes. Can this cause performance issues?

I have 4 AS400s running JDEdwards. Lately a performance issue occured as described here: AS400 Performace issue Although i am not an expert in AS400 , i tried to find out the cause. I think it came ...
7
votes
1answer
2k views

Why would you index text_pattern_ops on a text column?

Today Seven Databases in Seven Weeks introduced me to per-operator indexes. You can index strings for pattern matching the previous queries by creating a text_pattern_ops operator class index, as ...
2
votes
1answer
4k views

Set value of character_set_client to utf8mb4

I'm trying to convert my DB to utf8mb4 by following this guide. I have set: [client] default-character-set = utf8mb4 [mysql] default-character-set = utf8mb4 [mysqld] init-connect='SET NAMES ...
5
votes
1answer
807 views

sql_slovak_cp1250_ci_as vs Slovak_CI_AS

Can someone explain to me what is the difference between collation sql_slovak_cp1250_ci_as vs Slovak_CI_AS? I have a db set to sql_slovak_cp1250_ci_as and shared hosting supports only Slovak_CI_AS. ...
1
vote
0answers
175 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 ...
2
votes
1answer
57 views

PosgtreSQL - Change multiple fields collation in one query

I have small database with few text fields with "default" collation. I don't want to recreate database. What is the query to alter all problematic fields at once? To change the single one I can use ...
1
vote
1answer
2k views

MySQL - Server collation utf8_unicode_ci vs table collation utf8_bin: compatibility and performance

I'm not sure whether this belongs to SE DBA, but it sounds more logical ... Our MySQL 5.5.32 on Ubuntu 12.04 64 uses utf8_unicode_ci for the Server collation and some tables, like phpBB3, uses ...