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
127 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" ...
2
votes
1answer
111 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
1answer
991 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
221 views

Mysterious inequality behavior when comparing date strings

Can someone explain this mysterious Postgres behavior? I'm performing the following queries at the psql command line. mydb=> select '20150526' > '2015-05-25'; -[ RECORD 1 ] ?column? | t ...
0
votes
1answer
3k 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
32 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
0answers
34 views

Characterset in SQL Server - Migrating data from Oracle 8i to SQL Server 2012

I'm migrating data from Oracle 8i to a new MS SQL Server instance (2012). My Oracle database has: NLS_CHARACTERSET = US7ASCII I'm completely new to SQL Server - how do I configure the SQL Server ...
0
votes
1answer
112 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 ...
11
votes
3answers
37k views

SQL Server 2005/2008 UTF-8 Collation/Charset

I can't find option(s) directly to set UTF-8 rellated Collations/Charsets in SQL Server 2005/2008, same as is possible to set in another SQL engines, but in SQL Server 2005/2008 are there only Latin ...
0
votes
1answer
35 views

Cannot set character_set_database and character_set_server to utf8mb4

I'm trying to convert my MySQL server and its already existing schemas to the utf8mb4 character set. Preliminary Notes: I'm using Xampp (with MySQL 5.6.24). Default options are read from the ...
0
votes
1answer
42 views

Illegal mix of collations (utf8_unicode_ci,IMPLICIT) and (utf8_turkish_ci,IMPLICIT) for operation '=' and why?

Hello I got error in mariadb. In my command two columns have different collate. one is utf8_unicode_ci (a) other is utf8_turkish_ci (b). Why I got error when I compare these columns ( where a = b ) ...
13
votes
2answers
11k 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 ...
6
votes
4answers
1k views

Is there a collation to sort the following strings in the following order 1,2,3,6,10,10A,10B,11?

I have a database with a VARCHAR column that contains integers of varying length. I want to sort them so 10 comes after 9, not 1, and 70A comes after 70. I was able do this with PATINDEX(), a CTE, and ...
3
votes
0answers
24 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 ...
2
votes
0answers
42 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: ...
20
votes
4answers
668 views

What's up with the collation of some columns in sys.databases?

I'm attempting to run an UNPIVOT on various columns contained in sys.databases across various versions of SQL Server, ranging from 2005 to 2012. The UNPIVOT is failing with the following error ...
3
votes
1answer
239 views

“String or binary data would be truncated” error copying nvarchar(10) to char(10)

I am inserting values from one SQL table / column into another. The datatypes are different for these columns due to other reasons but I do not understand why nvarchar(10) source and char(10) ...
4
votes
1answer
2k views

Storing Japanese characters in a table

I am using SQL Server 2008 R2 and I want to store Japanese characters in one of the columns of my table. Say I want to store Japanese names, how can I achieve that? Is there an easy way?
6
votes
1answer
170 views

How to find values with multiple consecutive upper case characters

For a project that I am working on, I need to identify values that have incorrect uppercase. For example, I need it to identify the following types of values: Mr JOHN Smith MR John Smith Mr John ...
10
votes
3answers
408 views

How To Strip Hebrew Accent Marks

I need a Char Encoding Trick to Strip Hebrew Accent Marks. Sample Before בְּרֵאשִׁ֖ית בָּרָ֣א אֱלֹהִ֑ים אֵ֥ת הַשָּׁמַ֖יִם וְאֵ֥ת הָאָֽרֶץ Sample After בראשית ברא אלהים את השמים ואת הארץ
4
votes
2answers
92 views

Contained DB Collation error

When changing a database to partially contained I am getting the following error: Cannot resolve the collation conflict between "Latin1_General_CI_AS" and "Latin1_General_100_CI_AS_KS_WS_SC" in ...
7
votes
2answers
1k views

Automatic Translation when Converting Unicode to non-Unicode / NVARCHAR to VARCHAR

Unicode code point 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 ...
3
votes
2answers
385 views

Cannot remove char 0x0000 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 NCHAR(0x0000) characters from a bad import. I'm using SQL Server 2008 R2. The ...
0
votes
2answers
299 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 ...
9
votes
3answers
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
211 views

COLLATION and UNION ALL

I have the following queries: SELECT * FROM OPENQUERY([SERVERA], 'SELECT ''SERVERA'',[PhoneNum] ,[PhoneExt] ,COUNT(2) as CNT FROM [DB].[dbo].[ResourcePhone] WHERE PhoneNum like ...
2
votes
1answer
97 views

What is Collation Compatibility_60_406_30001 in SQL Server

We have an SQL Server 2014 (recently upgraded from 2008 R2) instance where one of the databases has some strange settings for collation: In the Properties window for the database we have the ...
0
votes
0answers
32 views

DB with SQL_Latin1_General_CP1_CI_AS collation and insert multi language

I have a DB with SQL_Latin1_General_CP1_CI_AS collation and we are trying to import an excel file with multiple language characters. it is not importing any characters and showing NULL. we are using ...
2
votes
1answer
53 views

Having Sharepoint on a different Collation than the system Databases?

After installing Sharepoint I realized that the collation of all Sharepoint databases is Latin1_General_CI_AS_KS_WS while my server collation is Latin1_General_CI_AS. From official documentation I see ...
5
votes
2answers
1k views

Latin1_General_BIN performance impact when changing the database default collation

I have set the database collation to Latin1_General_BIN, to make string comparisons case-sensitive. Will this have an impact on performance? Will it have any impact on DML or DDL operations in the ...
2
votes
1answer
664 views

How to disable case sensitivity in SQL Server 2012?

I tried to update collation_name in sys.databases. I am trying to change 'SQL_Latin1_General_CP1_CS_AS' to 'SQL_Latin1_General_CP1_CI_AS' in order to disable case sensitivity in table. update d set ...
4
votes
1answer
126 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 ...
4
votes
3answers
6k 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 ...
0
votes
0answers
42 views

Searching/selecting database content and smart quotes

I have a utf8 encoded mysql table where the content has been entered using smart quotes. My client is complaining that when users search the site content, the relevant results don't show up. This is ...
2
votes
1answer
126 views

What collation to use for Ukraine?

We have an accounting program with an SQL Server database and would like to start a Ukrainian version. I am not sure which collation to use: Ukrainian or Cyrillic_General? With a non-technical ...
0
votes
0answers
55 views

Firebird 2.5 - difference between starting with and = (equal) string searches

Background I have DB that was created on Windows, FB 2.5.0 (long ago). It was subsequently upgraded, over the years, to 2.5.4. Yesterday I wanted to test it on Ubuntu, which has FB 2.5.2 maintained ...
1
vote
2answers
130 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 ...
2
votes
1answer
129 views

Why does MS SQL Server return a result for empty string check when Unicode string is not empty

select * from (select N'ግዜ ' as t) as t2 where t= '' the string 'ግዜ ' matches the above check, why is this?
0
votes
0answers
36 views

Where can I find exact rules of a collation?

Is there any documentation anywhere for the exact sorting/equality rules defined by a collation in Windows (SQL Server), for instance "Finnish_Swedish_CI_AS"? I know about CI/CS, AI/AS etc, but I'm ...
7
votes
0answers
432 views

Why is my PostgreSQL ORDER BY case-insensitive?

I have Postgres 9.4.4 running on Debian and I get the following ORDER BY behavior: veure_test=# show LC_COLLATE; lc_collate ------------- en_US.UTF-8 (1 row) veure_test=# SELECT ...
3
votes
1answer
387 views

Add a new collation to a Postgres database

I have these collations: postgres=# select * from pg_collation; collname | collnamespace | collowner | collencoding | collcollate | collctype ...
3
votes
0answers
281 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
1answer
574 views

SQL Server collation mismatch

I have database server with collation: SQL_Latin1_General_CP1_CI_AS I got database backup file which collation is: Latin1_General_CI_AS When I'm restoring this database and executing some SQL, I'm ...
1
vote
1answer
415 views

MySQL: COLLATE performance

I have a table called users with a column firstname and with collation utf8_bin I want to know what happens under the hood when I execute an query like: SELECT * FROM `users` `a` WHERE ...
1
vote
2answers
464 views

Collation conflict when running dynamic sql FOR XML PATH

I am getting the following error message when running the query below: Cannot resolve the collation conflict between "Latin1_General_CI_AS" and "Latin1_General_BIN" in the equal to operation ...
2
votes
0answers
961 views

SQL fulltext accent insensitive [closed]

I am using SQL Server fulltext containstable and the column which is indexed has collation Czech_CS_AS, so why when I have word "byt" it returns records with word "být" too. I thought that the ...
2
votes
1answer
125 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 ...
2
votes
1answer
135 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
1answer
222 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: ...
0
votes
1answer
30 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 ...