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.
45
questions
18
votes
2
answers
21k
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 ...
- 1,355
17
votes
4
answers
2k
views
How To Strip Hebrew Accent Marks
I need a Char Encoding Trick to Strip Hebrew Accent Marks.
Sample Before
בְּרֵאשִׁ֖ית בָּרָ֣א אֱלֹהִ֑ים אֵ֥ת הַשָּׁמַ֖יִם וְאֵ֥ת הָאָֽרֶץ
Sample After
בראשית ברא אלהים את השמים ואת הארץ
- 273
15
votes
4
answers
4k
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 ...
- 2,667
6
votes
3
answers
2k
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 ...
- 347
3
votes
1
answer
3k
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 ...
- 537
31
votes
1
answer
29k
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 ...
- 2,298
28
votes
4
answers
69k
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 ...
- 1,545
19
votes
4
answers
90k
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-...
- 2,602
16
votes
3
answers
21k
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 ...
- 318
13
votes
2
answers
21k
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 ...
- 2,298
7
votes
2
answers
32k
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?
- 203
7
votes
1
answer
21k
views
Add a new collation to a Postgres database
I have these collations:
postgres=# select * from pg_collation;
collname | collnamespace | collowner | collencoding | collcollate | collctype
------------+---------------+-----------+------------...
- 205
3
votes
1
answer
3k
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 ...
- 60.4k
2
votes
2
answers
5k
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.
...
- 143
2
votes
1
answer
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 ...
- 123
39
votes
1
answer
25k
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 ...
- 505
34
votes
1
answer
45k
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 ...
- 1,841
28
votes
3
answers
24k
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 ...
- 437
20
votes
2
answers
2k
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
...
- 203
18
votes
2
answers
39k
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 ...
- 183
18
votes
2
answers
53k
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'
...
- 285
9
votes
2
answers
10k
views
Database collation different from tempdb collation
I just received the source code and database of an application from my client (it was developed by another company, from a different country) and the application is throwing some exceptions related to ...
- 355
9
votes
3
answers
2k
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 ...
- 7,274
8
votes
1
answer
4k
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 ...
- 191
8
votes
1
answer
7k
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 ...
- 319
7
votes
4
answers
394
views
Relationships between like matching and comparison
Naively I assumed that the relationship between a like 'foo%' and a >= 'foo' is that the latter matches the former and some additional rows that come after in the index order. More generally, I ...
- 755
6
votes
1
answer
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 ...
- 10.1k
6
votes
3
answers
7k
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 ...
- 1,539
6
votes
3
answers
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 ...
- 163
5
votes
2
answers
24k
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 ...
- 632
5
votes
2
answers
7k
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 ...
- 203
5
votes
1
answer
1k
views
Different sort despite same collation in Postgres
I am getting different sort results for items with diacritics even though the collation being used is apparently the same.
So, after installing Postgress.app in my mac box for development I create ...
- 153
4
votes
1
answer
6k
views
Storing special characters (German, French, Spanish) in SQL Server result in weird strings
I'm trying to store the German ß in SQL Server, but it results in ß.
For example "Gleimstraße" is stored as "Gleimstraße"
But this also happens for other characters:
"König-...
- 245
3
votes
2
answers
2k
views
SSMS - How to do case-insensitive searches in Object Explorer
Preface: I am not referring to queries.
There are numerous places in SSMS that allow for filtering, such as the Object Explorer and Profiler, but these all treat filters as case-sensitive with no ...
- 711
3
votes
2
answers
825
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; ...
- 233
3
votes
1
answer
2k
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
2
answers
9k
views
How do I fix the Code Page in SSIS Lookup Transformation to be 65001?
I have an SQL server 2019 with databases and tables all set with the collation Latin1_General_100_CI_AS_SC_UTF8.
The relevant table has code and desc columns which are both of data type varchar.
In ...
2
votes
1
answer
12k
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(...
- 123
2
votes
2
answers
3k
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 ...
- 15.6k
2
votes
1
answer
696
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.
...
- 121
2
votes
1
answer
7k
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 :
=============================...
- 621
2
votes
1
answer
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 ...
- 155
2
votes
1
answer
1k
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 ...
- 203
2
votes
0
answers
407
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 ...
- 303
0
votes
1
answer
3k
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 ...
- 23