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

3
votes
1answer
56 views

How is encoding done in SQL database tables for pattern matching?

Are strings in table columns represented as bit patterns or Unicode? Does the database engine bring them into memory in a different representation character set from the disk-persisted ...
0
votes
0answers
9 views

Removing per column and table collation and charset

I recently completed a transition of a database from CHARSET=utf8 COLLATE=latin1 to CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci. During the process tables were migrated one at a time by altering their ...
2
votes
1answer
30 views

Is worth change databases collation from Latin1_General to Modern spanish, migrating to a new server

We are migrating 3 databases tables from Sql Server 2000 to a new sqlserver 2014. I know that for new databases the recommended Collation for spanish is Modern_Spanish. But what is the recommendation ...
1
vote
2answers
77 views

Collation by row, not by column

Is it possible in SQL Server to set collation on individual rows in a table (as opposed to by column, database or server)? If not, perhaps does anyone know if this is a planned feature for SQL Server ...
1
vote
1answer
32 views

How to create Postgres DB with case insensitive collation

I am using the postgres version 9.4.1 64-bit on windows 7 64-bit. I need to create DB with the setting "case sensitive = OFF" but couldn't make this work. I refereed already many question on SO and ...
1
vote
1answer
19 views

How to manually set Code Page and Collation for my SSAS Cube?

When I try to Process my SSAS2012 Olap Cube I get a lot of erros like this Errors in the OLAP storage engine: A duplicate attribute key has been found when processing: Table: 'dbo_ART' ,Column: ...
2
votes
1answer
48 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
154 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 ...
2
votes
0answers
29 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
2answers
42 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 ...
0
votes
0answers
17 views

mysql collation-server not respected

I've configured my server (5.5.41) with: character-set-server = utf8 collation-server = ...
2
votes
1answer
68 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 ...
2
votes
1answer
55 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
181 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 ...
0
votes
1answer
93 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 ...
1
vote
1answer
79 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 ...
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 ...
-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 ...
1
vote
1answer
167 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 ...
5
votes
2answers
133 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
89 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
0answers
64 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
1answer
126 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
49 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? ...
2
votes
2answers
100 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 ...
2
votes
1answer
64 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 ...
3
votes
1answer
81 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 ...
4
votes
1answer
503 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 / ...
2
votes
0answers
122 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
95 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
149 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
91 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
580 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
58 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
135 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 ...
0
votes
2answers
151 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 ...
1
vote
1answer
135 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
260 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
2k 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). ...
0
votes
1answer
144 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
70 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 ...
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 ...
1
vote
0answers
179 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 ...
8
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 ...
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 ...
1
vote
0answers
19 views

Recover original utf8 data from latin1 mysql db [duplicate]

We forgot to change charsets in our mysql db after migration on new server. Current situation: mysql> show variables like 'character%'; +--------------------------+----------------------------+ | ...
1
vote
1answer
82 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 ...
0
votes
1answer
121 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 ...
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 ...