Questions tagged [collation]

A collation is a set of rules that determine how data is sorted and compared for comparing characters in a character set.

Filter by
Sorted by
Tagged with
17
votes
4answers
2k views

How To Strip Hebrew Accent Marks

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

Specify ICU collations as `ENCODING`, `LC_COLLATE`, and `LC_CTYPE` in Postgres 10

Postgres 10 gains the ability to use International Components for Unicode (ICU) collations rather than depending on host OS implementations. See More robust collations with ICU support in PostgreSQL ...
6
votes
3answers
1k 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 ...
23
votes
1answer
22k 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 ...
27
votes
4answers
68k 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 ...
14
votes
3answers
67k views

How to do a case-insensitive LIKE in a case-sensitive database?

My vendor requires the data warehouse database to be case sensitive, but I need to do case-insensitive queries against it. In a case-sensitive database, how would you write this to be case-...
13
votes
2answers
18k 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 ...
7
votes
2answers
27k 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
17k 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
1answer
2k views

Does PostgreSQL support ICU collation's options and settings?

ICU Specifies different LDML Collation Settings. Some of them seem pretty interesting, especially the ones on case and accent, “Ignore accents”: strength=primary “Ignore accents” but take case ...
12
votes
4answers
3k 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 ...
7
votes
3answers
9k views

PostgreSQL: difference between collations 'C' and 'C.UTF-8'

In PostgreSQL, what is the difference between collations C and C.UTF-8? Both show up in rows of pg_collation. Is it perhaps the case that C.UTF-8 is the same as C with encoding UTF-8 regardless or ...
2
votes
2answers
3k views

SQL Server default collation vs database with different collation - potential problems?

I'm struggling with the design of new database that has to use a different collation (Y Collation) than the SQL Server Instance default collation (X Collation). I'm looking for potential problems. ...
2
votes
1answer
6k 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 ...
25
votes
3answers
23k 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 ...
32
votes
1answer
39k 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 ...
18
votes
2answers
34k 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 ...
15
votes
2answers
46k views

Set value of character_set_client to utf8mb4

I'm trying to convert my DB to utf8mb4 following this guide. I have set: [client] default-character-set=utf8mb4 [mysql] default-character-set=utf8mb4 [mysqld] init-connect='SET NAMES utf8mb4' ...
6
votes
3answers
3k 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 ...
32
votes
1answer
17k 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 ...
19
votes
2answers
1k views

Accent Sensitive Sort

Why do these two SELECT statements result in a different sort order? USE tempdb; CREATE TABLE dbo.OddSort ( id INT IDENTITY(1,1) PRIMARY KEY , col1 NVARCHAR(2) , col2 NVARCHAR(2) ); GO ...
5
votes
2answers
6k 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
1k views

Collation issue after update (KB3072779) of SQL Server 2012

If found the following error while starting my SQL Server 2012 instance, after the update KB3072779 performed by Windows Update (Windows Server 2012 Foundation). Creating procedure ...
6
votes
3answers
5k 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
1answer
2k views

PostgreSQL nondeterministic collations are not supported for LIKE

I am using Postgresql v12. I created a collation like this: CREATE COLLATION ci (provider = icu, locale = 'tr_TR', deterministic = false); I used that collation in a table: create table testtable1 ...
2
votes
0answers
348 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 ...
0
votes
1answer
6k views

setting a default collation for a postgresql database

I have created a database with Turkish collation. createdb -l tr_TR test_tr -E LATIN5 -T template0 Then a table with a row. \c test_tr create table turkish(one text); insert into turkish values(...
9
votes
3answers
1k views

Trigger to change Database collation on creation

I'm trying to create a Trigger, to alter collation of a database on its creation, but how can I catch the database name to use inside the trigger? USE master GO CREATE TRIGGER ...
8
votes
1answer
2k views

Case-insensitive collation still comparing case-sensitive

I am currently trying to create a table with a text column which will compare case insensitive by default. This is because we have a third party program that executes a search on our database. The ...
3
votes
2answers
401 views

Default collation not working?

I don't understand why the default collation on my psqlserver 11 instance returns a different result than using with explicit collation. Details: show LC_CTYPE; -- de-AT show LC_COLLATE; ...
3
votes
1answer
1k 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. ...
2
votes
1answer
126 views

Is there a difference between text_pattern_ops and COLLATE "C"?

If I have a text column name with a collation, for example tr-TR, and I run a query SELECT * FROM t WHERE name LIKE 'a%' then this will do a seq scan. If I now create an index CREATE INDEX ON t(name ...
2
votes
1answer
911 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 ...
2
votes
2answers
2k 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 This ...
2
votes
1answer
5k views

Error 468 MS SQL Server : Couldn't resolve collation conflict between Arabic_CI_AI and Arabic_CI_AS when enumerating publications

When I try to expand the list of local publications under Replication element in SQ Server Management Studio, I get the following error : Here is the detailed error log : =============================...
0
votes
1answer
174 views

postgresql 12.3 convert existing DB to use case-insensitive collation?

postgresql 12.3 in a container with Alpine Linux 3.10 I would like to convert an existing DB to use case-insensitive collation. Is it possible? I mean, without dropping it and recreating from scratch. ...