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
129 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 ...
3
votes
1answer
2k 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 ...
0
votes
1answer
4k 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
2answers
102 views

Why are there different Database Collations for different languages? [closed]

There are different database collations, I want to know the need for different database collations with respect to different languages.
0
votes
1answer
165 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" ...
3
votes
1answer
10k 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 utf8mb4'...
0
votes
1answer
246 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 ...
0
votes
1answer
27 views

SQL Server to MySQL migration, replace '\xA0' with a regular space

I am having some trouble while migrating data from SQL Server to MySQL--it seems some data does not want to place nice. I am recieving the error: Incorrect string value: '\xA0\xA0' for column '...
4
votes
2answers
620 views

Why not use SQL_Latin1_General_CI_AS for a global system?

What would be some really good reasons to not use the SQL_Latin1_General_CI_AS collation on a system which handles English, German, Japanese, and Chinese data? I'm having a really hard time finding ...
2
votes
1answer
38 views

Databases created with different collations - why?

We have a SQL Server 2014 instance, which houses many related databases. New ones are added via our script regularly. When our app is used to create the database - the databases are created with ...
1
vote
1answer
186 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 ...
4
votes
1answer
135 views

How do I set a SQL Server Unicode / NVARCHAR string to an emoji or Supplementary Character?

I want to set a Unicode string variable to particular character based on its Unicode code point. I want to use a code point beyond 65535, but the SQL Server 2008 R2 database has a collation of ...
4
votes
1answer
9k views

Setting default charset/collation for MySQL database

I have a MySQL DB that used to be latin1, and I changed all the columns in all the tables to be utf8. However, I evidently didn't do all I had to do, because I just found that I have a crazy mixture ...
10
votes
2answers
11k 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 query's results is ordering it according to the user's language during the ...
3
votes
1answer
142 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
214 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 varchar(...
0
votes
0answers
28 views

SQL Server Collation conflict

The problem is, one of the server had wrong collation, so I tried fixing it by using sqlservr -m -T4022 -T3659 -s"" -q" SQL_Latin1_General_CP1_CI_AS". It worked perfectly, it fixed all the system ...
0
votes
1answer
50 views

Stored Procedure Is Slower Than Query Due to Collation

I'm running two servers with MySql installed. I'm having an issue with a slow stored procedure (approximately 3 secs vs 0.1 seconds), solely on my production server, that is very quick on development ...
2
votes
0answers
23 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. ...
0
votes
0answers
19 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
65 views

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

Well, I have a table with an Arabic column which may contains variant forms of the same word in multiple rows, for example the word "أسمى" might be in the following forms: 1- with diacritics: "...
6
votes
3answers
136 views

Collation conflict … Could not use dbo.sysdac_instances

After a migration of databases to a new 2014 server with default collation Latin1_General_CI_AS, but where most of the databases have a collation of Latin1_General_BIN, an attempt to import an Excel ...
1
vote
3answers
415 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 ...
1
vote
2answers
44 views

Can a table column be written to treat two different values as the same value?

The title is a bit generic, but the specific issue is that the United Kingdom of Great Britain and Northern Ireland has two commonly used 2-character codes. ISO 3166-1 specifies the code GB as the ...
3
votes
2answers
2k 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 Polish,...
0
votes
1answer
44 views

How do I make a locale available in Postgresql?

I needed to add new locales to our postgresql server (PostgreSQL 9.4.4 on x86_64-unknown-linux-gnu, compiled by gcc (Ubuntu 4.8.2-19ubuntu1) 4.8.2, 64-bit ). As the locales were not yet installed on ...
12
votes
1answer
917 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
51 views

Cloning a database to another collation

I'm not sure if this is the best way to migrate data to another collation but I've done the following: Scripted the source database and changed the collation in the script to my required one Ran the ...
3
votes
0answers
57 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 ...
1
vote
1answer
78 views

What is default behavior of PostgreSQL without specifying COLLATION?

What is default behavior of PostgreSQL, if i don't specify collation externally? One of the main questions, is it case-sensitive or not?
1
vote
1answer
233 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 mydb=&...
0
votes
0answers
42 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 ...
11
votes
3answers
42k 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
67 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 ) ...
14
votes
2answers
13k 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
58 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
76 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
1k 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
780 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
3k 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
342 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 ...
11
votes
3answers
502 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
194 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 ...
8
votes
2answers
2k 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 ...
4
votes
2answers
620 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 ...
10
votes
3answers
3k 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
829 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
110 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
63 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 ...