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

17
votes
2answers
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 ...
16
votes
3answers
31k 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 ...
9
votes
2answers
2k 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
2answers
1k 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
1answer
21k 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 ...
6
votes
1answer
488 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%' ...
5
votes
1answer
2k 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 ...
5
votes
1answer
518 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. ...
4
votes
3answers
668 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 ...
4
votes
2answers
2k 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 ...
4
votes
1answer
813 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 ...
4
votes
1answer
2k 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 ...
4
votes
1answer
117 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
327 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 ...
3
votes
1answer
3k 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 ...
3
votes
2answers
892 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
311 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 ...
3
votes
1answer
441 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
2answers
939 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
697 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
45 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
1answer
49 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 ...
2
votes
1answer
135 views

Why cp1256 collation is not 1 byte in MySQL?

I've read an article that cp1256 collation takes 1 byte for Arabic characters, but when I created an index on the column that was cp1256 with type of varchar(900), only 767 characters picked up for ...
2
votes
2answers
4k views

SQL Server 2008 collation arabic

We are currently having a problem loading arabic into a SQL Server 2008 database. We want to load بِسْمِ اللَّهِ الرَّحْمَٰنِ الرَّحِيمِ but in the database it changes to بِسْمِ اللَّهِ ...
2
votes
2answers
3k views

sql server collation for arabic or hebrew and english and french

I have new sql server database which need to have support in 3 differnt languages which collation i need to choose if i want my database to support both hebrew or arabic and also english and also ...
2
votes
1answer
2k views

What's the differences between utf8_general_ci and utf8_unicode_ci and utf8_binary collation in MySQL?

I'm can't find the documentation of MySQL on this topic. Anyone can give some explanations please?
2
votes
1answer
672 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 ...
2
votes
2answers
684 views

changing TEMPORARY TABLE default collation

when I use CREATE TEMPORARY TABLE to create a temporary table, the collation of this table is utf8_unicode_ci by default. why by default it is utf8_unicode_ci? How can I change the default?
2
votes
1answer
2k views

SQL Server 2005 collation issue during installation

Every time I try to install SQL Server 2005 on to a Windows 7 machine it installs properly but the collation installed is sql_latin1_general_as instead of sql_latin1_general_cp1_ci_as. I have read ...
2
votes
0answers
53 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] ...
2
votes
0answers
434 views

SQL fulltext accent insensitive

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 ...
1
vote
2answers
727 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 ...
1
vote
1answer
3k views

Invalid default value for DateTime when changing to utf8_general_ci

I'm trying to run a script to modify a database, this query ALTER TABLE cmContentVersion CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci; gives this error ERROR 1067 (42000) at line 68: ...
1
vote
1answer
88 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 ...
1
vote
3answers
370 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). ...
1
vote
2answers
2k views

Correct Code Page or Collation in SQL Server for Portuguese characters

I'm inserting data (Bulk insert and merge update) for a Portuguese data source (CSV files) into my database. Although the DB collation is Latin1-General, case-insensitive, accent-sensitive, ...
1
vote
1answer
1k 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
3answers
543 views

Why are there different levels of MySQL collation/charsets?

Looking at the MySQL server variables the server and database collation are set to latin1_swedish_ci but the collaction_connection is utf8_general_ci. Additionally, the collation/charset is set at ...
1
vote
2answers
1k views

SQL Server 2012 : how to add missing collation

When we run: ALTER DATABASE abc COLLATE Latin1_General_CP1_CI_AS ; GO we receive: Msg 448, Level 16, State 3, Line 1 Invalid collation 'Latin1_General_CP1_CI_AS'. How can we add the missing ...
1
vote
1answer
732 views

Changing collation type database-wide in MySQL

How do I retroactively update the collation type database wide without dropping and recreating it? I accidentally created a database without specifying UTF8 as the default collation type. I then went ...
1
vote
1answer
372 views

MySQL Character set and Collation Issue.?

I Have a table when i select from the table i got the invalid data as mysql> select budgetID,StartDate,modifiedBy from Table_name order by budgetID desc limit 10; ...
1
vote
1answer
382 views

Design SQL Server 2008 new install … which collation?

A customer has a SQL Server 2005 instance, collation SQL_Latin1_General_CP1_CI_AS, with 8 databases ... for a new application, we have been asked to install a SQL Server 2008 with collation ...
1
vote
0answers
110 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 ...
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
0answers
59 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
0answers
472 views

SQL Server: Collation of the resource database

I'm trying to use stopwords so that some words are skipped by the Full-Text Indexer. I can add those stopwords in the table sys.fulltext_stopwords. When trying to get a list of stopwords and ...
1
vote
0answers
112 views

What would make a MySQL database create tables in a different collation other than its default?

I am adding tables to a Wordpress database on my localhost and it is changing my collation from utf8_general_ci to latin1_swedish_ci. I'm kinda stumped because I'm not familiar with working directly ...
1
vote
0answers
398 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 ...
1
vote
0answers
789 views

Server Collation Change Error [closed]

I am using following command to change the collation of my SQL Server R2 (10.50.1600): setup.exe /QUIET /ACTION=REBUILDDATABASE /INSTANCENAME=MSSQLServer /SQLSYSADMINACCOUNTS=sa /SAPWD=**** ...
0
votes
3answers
712 views

SQL Server 2000 dateformat permanent change

I am using SQL Server 2000 for an old system web site. I've just hit an issue that is going to cause me endless hassles down the road. When I installed the server on my local system I took all the ...