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

21
votes
3answers
46k views

How to change SQL Server collation

How could I change the SQL Server 2008 R2 Express Default Collation for the whole server and a particular database? Is there a way to do it using visual interface of SQL Server Management Studio? In ...
21
votes
3answers
2k 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 ...
20
votes
4answers
937 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 ...
13
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 ...
12
votes
1answer
815 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 ...
11
votes
3answers
484 views

How To Strip Hebrew Accent Marks

I need a Char Encoding Trick to Strip Hebrew Accent Marks. Sample Before בְּרֵאשִׁ֖ית בָּרָ֣א אֱלֹהִ֑ים אֵ֥ת הַשָּׁמַ֖יִם וְאֵ֥ת הָאָֽרֶץ Sample After בראשית ברא אלהים את השמים ואת הארץ
11
votes
3answers
41k 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 ...
10
votes
2answers
10k 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 ...
10
votes
1answer
4k 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 ...
9
votes
2answers
9k views

Why are non-digits LIKE [0-9]?

My server's default collation is Latin1_General_CI_AS, as determined by this query: SELECT SERVERPROPERTY('Collation') AS Collation; I was surprised to discover that with this collation I can match ...
9
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 ...
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 ...
7
votes
2answers
3k 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 ...
7
votes
1answer
7k 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 ...
7
votes
1answer
581 views

Dependant T-SQL comparison collation

Is there any way to set the collation for a string comparison based on an independent variable or column value? Something akin to SELECT COL1, col2 FROM [TABLE] WHERE COL3 LIKE '%string%' ...
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 ...
6
votes
3answers
124 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 ...
6
votes
1answer
293 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 ...
5
votes
2answers
3k 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 ...
5
votes
1answer
1k 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. ...
5
votes
1answer
384 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 ...
5
votes
2answers
197 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) ...
5
votes
1answer
7k 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 ...
5
votes
1answer
367 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. ...
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?
4
votes
1answer
61 views

How do I set a Unicode string variable to an emoji in SQL Server?

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 ...
4
votes
2answers
370 views

How can I extract a lessequal char from SQL_Latin1_General_CP1_CI_AS?

When I create a table like this create table char_test( item varchar(10) collate SQL_Latin1_General_CP1_CI_AS ) go I can store varchars containing ≥ insert into char_test values ('≥'); When ...
4
votes
2answers
170 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 ...
4
votes
3answers
8k 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 ...
4
votes
1answer
2k views

Unicode support of PostgreSQL and its performance

How is the Unicode support of PostgreSQL? If I want to save Unicode data does it have to go through the conversion process while storing and retrieving? If yes then what is the performance of such ...
4
votes
1answer
150 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
1answer
444 views

How do I resolve Postgresql error, 'no collation was derived for column “foo” with collatable type citext'?

Just recently I upgraded from Postgresql 8.4.11 to 9.1.3. I used pg_dump in the process. Now I'm getting an error: ERROR: no collation was derived for column "vin" with collatable type citext ...
4
votes
1answer
169 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 ...
3
votes
1answer
657 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) ...
3
votes
2answers
2k views

Installed SQL Server 2000 with wrong collation.. how to correct for entire server instance?

I installed SQL 2000 Developer (yes, old, don't ask) on my laptop ... with the wrong collation. Oops! It's showing Latin1_General_CI_AS, but it turns out I need to use SQL_Latin1_General_CP1_CI_AS ...
3
votes
2answers
1k 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 ...
3
votes
2answers
551 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 ...
3
votes
1answer
949 views

Dutch characters in DB2 problem

Ok, first of all, I have really limited knowledge about databases. Problem that I have is next: I inserted few rows with data that contains Dutch characters, such as names of different countries. ...
3
votes
1answer
919 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
2answers
486 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 ...
3
votes
1answer
9k 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 ...
3
votes
2answers
2k views

Multiple charsets and Collations for a Multinational Database

After decades of business speaking with companies that adhere to the general Latin1 Collation, my company is facing the issue of storing information in a different charset and collation: Greek. So, ...
3
votes
1answer
44 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
2answers
578 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 ...
3
votes
1answer
136 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
868 views

Casesensitive vs. Caseinsensitive Collatesequences?

I just started working with the SQLite database places.sqlite. Fact is that this database uses case sensitive collate sequences making writing queries as funny as in Oracle. I want to know, are ...
3
votes
0answers
49 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 ...
3
votes
0answers
48 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
346 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 ...