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.
335
questions
5
votes
3
answers
2k
views
Why not use two columns instead of collation for case insensitive data?
I need to store emails (like 'ivan@email.com') in Postgres 15, be able to search them in case-insensitive manner ('iVaN@email.com', 'IVAN@email.com', etc are the same), and be able to retrieve the ...
- 728
0
votes
1
answer
1k
views
MySQL- Performance implications of changing a column collation from latin1_swedish_ci to latin1_bin
I am a developer so was hoping someone with DBA experience could help me out here.
I am using a MySQL database (MYSQL 5.6).
I came across a situation where a particular column stores Ids generated ...
CommunityBot
- 1
2
votes
0
answers
23
views
How set default collation in Oracle by default BINARY_CI for new Tables?
I wrote script to change all tables default collation:
BEGIN
FOR rc IN (
SELECT * FROM ALL_ALL_TABLES WHERE owner='SchemaName' AND TABLE_NAME NOT IN ('__EFMigrationsHistory')
)
...
- 21
1
vote
1
answer
24
views
What exactly are all these English "collate" variants in PG?
When searching through all available "collates" related to "English" in PostgreSQL, I find these descriptions very odd:
English (United States)
English (United Kingdom)
English (...
- 40.6k
22
votes
3
answers
20k
views
mysql to mariadb: unknown collation utf8mb4_0900_ai_ci
I have a mysql 8.0 that I exported using mysqldump. I am trying to import it onto a Mariadb 10.4 database with phpmyadmin, both are the most current versions. Each time I do it though, I get:
Error: ...
- 101
2
votes
0
answers
2k
views
Prevent mysqldump from writing column-level collate and charset?
Using mariadb 10.x, it looks like the mysqldump utility adds column-level collate even if the table was created without them. Is there a way to suppress this behavior?
Table created:
CREATE TABLE `...
4
votes
3
answers
323
views
Differences in PostgreSQL 14 collation behavior on Linux and Mac
I have PostgreSQL installed on my Mac and Ubuntu Server as shown below:
atsweb=# select version();
version ...
- 141
4
votes
1
answer
2k
views
Importing Chinese characters via SSIS
I'm trying to import file that contains Chinese characters into a table. File encoding is Big5 (Traditional).
Here is a sample file like one that I need to import: https://www.pastiebin.com/...
CommunityBot
- 1
0
votes
1
answer
90
views
Modify Collation of a column
mysql Ver 8.0.25
Got this error in apache log:
Error query: SQLSTATE[HY000]: General error: 1267 Illegal mix of collations (utf8mb4_unicode_ci,IMPLICIT) and (utf8_general_ci,COERCIBLE) for operation ...
- 121
5
votes
1
answer
116
views
postgresql matching or converting utf-8 variant strings
Postgres 13
I am looking for a way to search UTF-8 text that may have variant character representations ( what is the proper term for this? ie 𝐋𝐈𝐅𝐄 vs life ) within postgresql.
I am running into ...
- 2,661
2
votes
2
answers
78
views
Numeric collation sorts by digits instead of value- postgres
I have a table of users with ids in the following form user123@domain.com.
When searching and sorting the users, I need user1@domian.com to be before user14@domain.com but since 4 is “smaller” than @ ...
- 40.6k
4
votes
1
answer
214
views
Using collations with JSONB
I have a postgres database and a case insensitive collation.
The collation was created by
CREATE COLLATION IF NOT EXISTS case_insensitive (provider = icu, locale = 'und-u-ks-level2', deterministic = ...
- 2,661
0
votes
1
answer
1k
views
Cleaning up bad data with REGEXP_REPLACE too aggressive with non latin chars
Trying to use REGEXP_REPLACE to clean up a text field in a table.
Here's my query:
select title original ,REGEXP_REPLACE(title, '[^a-zA-Z0-9]+', ' ') clean
from tmp_table2
WHERE txn_title like '%?%'
...
CommunityBot
- 1
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 ...
- 78.7k
1
vote
1
answer
944
views
What is the impact of converting latin1/latin1_swedish_ci to utf8mb4/utf8mb4_unicode_ci?
I was facing some issues with the character's encoding. Those are resolved by updating the CHARACTER and COLLATE for some columns in the table. So my concern is if this conversion is safe? Or does ...
CommunityBot
- 1
2
votes
1
answer
155
views
sp_blitz fails with: Msg 468, Level 16, State 9 Cannot resolve collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Latin1_General_BIN"
Has anyone had an issue with running sp_blitz on an instance using the Latin1_BIN collation? I created the stored procedure without error, but when I ran it I got the following error message:
Msg 468, ...
- 67.4k
2
votes
1
answer
353
views
Collation conflict when creating stored procedure
I'm trying to create a stored procedure but it gives me the error message
Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Latin1_General_BIN2" in ...
- 67.4k
5
votes
2
answers
436
views
Case-Sensitive Collation on a select query in SQL Server
Our SQL Server's collation is set to SQL_Latin1_General_CP1_CI_AS
I have a requirement where we have the same values with different cases but SQL Server treats them the same since our collation is set ...
- 67.4k
1
vote
1
answer
860
views
mysql: setting default default_collation_for_utf8mb4?
How to set the value for "default_collation_for_utf8mb4" to "utf8mb4_general_ci", globally , or when the mysql 8.0 server started ?
I tried
[mysqld]
init_connect='SET ...
CommunityBot
- 1
1
vote
2
answers
2k
views
Sort name by Thai character is not working
I am trying to sort a table by name. The name are in Thai character;
>Select name from product_categ
name
--------------------------------------------
...
CommunityBot
- 1
3
votes
1
answer
2k
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 ...
- 163k
4
votes
1
answer
490
views
Fix corrupted utf8 nvarchar value that was inserted without N prefix
Is it possible to restore the UTF8 data from a corrupted nvarchar, that was used without the N prefix?
For example, in the following snippet, I'd like @n1_fixed to have the correct value based on @n1:
...
- 26.3k
6
votes
2
answers
386
views
Collation conflict when comparing sql_variant with varchar literal
I found that queries below return different results on two Azure Hyperscale databases that are running the same compatibility level, same set options etc.
IF CAST(ServerProperty('Edition') AS nvarchar(...
- 78.7k
5
votes
2
answers
288
views
At finding a substring, find the end position as well
A LIKE clause can test if a string occurs in another and the CHARINDEX function can give the start position of the first match.
In my case, I'm interested in the end position though, which is, due to ...
- 745
1
vote
1
answer
613
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.
...
CommunityBot
- 1
7
votes
4
answers
384
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 ...
- 745
4
votes
1
answer
4k
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
2
votes
1
answer
329
views
simulate case sensitive collation in ssms
I know that it is not possible to set a collation for a session in sql server.
I have different servers with different collations, however, one of them is a case sensitive collation and that can be ...
- 15.4k
2
votes
5
answers
5k
views
Locale is incompatible error in Postgresql 10 when replicating from Ubunu to Win 10
The locale available in my Ubuntu PG-10 DB is en_US.UTF-8. When doing PG_Basebackup to a PG-10 DB in Win-10, Psql failed to connect with error
psql: FATAL: database locale is incompatible with ...
- 1
0
votes
1
answer
389
views
SQL Server collation and its equivalent in Postgres
Legacy system, backed by SQL Server, has unique indexes using collation Latin1_General_CP1_CI_AI. It compares things using charset Latin 1, encoded using CP-1252 (CP1 part), case insensitive, accent ...
- 40.6k
2
votes
1
answer
202
views
SQL Server PATINDEX issue/bug when using different case-sensitive collations
I have a function (which I found here years ago) that uses STUFF / PATINDEX to strip out non-alphanumeric characters from a string. When running on a Case Insensitive collation, it works fine. ...
- 67.4k
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 ...
- 4,398
2
votes
2
answers
1k
views
MySQL What charset/collation for Case insensitivity and Accent sensitivity?
I am looking for a charset/collation that would make it so when I do a
SELECT * FROM table_name WHERE username = "Warrior"
It only returns me the rows where username = "Warrior", ...
- 73.8k
1
vote
1
answer
2k
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=&...
- 163k
1
vote
1
answer
192
views
Case sensitivity not working
I have an issue where I keep getting the value 'CtP_PETER_Fact' out of the query below. It should be a case sensitive where-clause. I tried it in a few different ways: setting the COLLATE statement ...
- 78.7k
6
votes
1
answer
3k
views
Sort order with numbers as strings
For demonstration purposes, I have a sample table with a varchar column called numberstring with the values:
-4
32
0
4
16
8
-8
1024
When selecting using ORDER BY numberstring, I find that different ...
- 78.7k
0
votes
1
answer
3k
views
Collation won't set when creating new database
I am trying to set the collation for a new database in PostgreSQL 13 but it does not seem to take effect:
postgres=# CREATE DATABASE assets ENCODING 'UTF8' LC_COLLATE 'C' LC_CTYPE 'en_US.UTF-8';
...
- 29.2k
2
votes
1
answer
61
views
Alter the collation of a column in temp-file
I have a routine in a DB that uses a temp-file. But the collation of the DB doesn't match the collation of the temp-DB. I can't change the collation of the DB, it's a client DB.
I tried this:
BEGIN
...
- 21
1
vote
1
answer
5k
views
Change SQL Server 2019 instance Collation After installation, and new problem
How to made this problem on SQL Server 2019
Install SQL Server with wrong collation 2month ago
Revived a report from SharePoint admin about has a problem with collation
Resolve problem with MSSQLTIPs ...
- 216
1
vote
2
answers
284
views
Determine Ideal Collation Set for correct data storage
Alright so I have a MariaDB DB and I've just noticed some weird storage issues, like the following:
My JSON columns (which in MariaDB are LONGTEXT) have an automatically set collation of utf8mb4_bin. ...
- 73.8k
1
vote
1
answer
3k
views
Use LIKE in column with arabic [closed]
I have a SQL Server 2012 DB with a column that includes Arabic text. The column is nvarchar(MAX) with collation Arabic_CI_AI_KS_WS. To test if LIKE searches are working I copied some of the text ...
- 16.2k
25
votes
1
answer
29k
views
Ignore accents in 'where' clause
In our database we have multiple entries with grave accents, carets or carons (aka hačeks). These accents are collectively also known as diacritical marks.
Now our users want to find entries including ...
- 28.6k
4
votes
2
answers
904
views
Identify the query causing collation conflict
I have inherited a new SQL Server database instance.
The SQL Server Agent error Log is full of collation errors - new errors are arising every 10 seconds. But detail of error is minimal - 'cannot ...
- 78.7k
2
votes
1
answer
238
views
How to reverse only English words?
I've an nvarchar column with English and Hebrew letters in some fields.
In the fields where the English and Hebrew are together, the English letters are in reverse order (this is how the ERP program ...
- 78.7k
18
votes
4
answers
87k
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-...
1
vote
2
answers
596
views
AWS Aurora Postgres 12 Setting Collation
I posted this on stack overflow as well, but didn't get much traction over there. If I am not able to get some thoughts on here, then I will probably open a support ticket with AWS.
I am trying to set ...
- 23.8k
1
vote
1
answer
992
views
Collation for accent-insensitive comparison on Postgres?
On PG 13 documentation, there are several examples of ICU collations for specialized purposes. It is also mentioned that ICU locales exist that allow creating collations to ignore accents, and that ...
- 40.6k
0
votes
4
answers
633
views
Why can't set variables related collation as utf8mb4_unicode_ci?
Show mysql version here:
mysql --version
mysql Ver 15.1 Distrib 10.5.11-MariaDB, for debian-linux-gnu (x86_64) using EditLine wrapper
And my mariadb's configuration file.
cat /etc/mysql/mariadb....
- 816
1
vote
1
answer
111
views
Can I set in MariaDB the collation based on a IETF language tag?
I have an application where each user account can set their preferred application language. I store those language preferences as IETF BCP47 language tag (e.g. "de", "en-US"). Now, ...
- 73.8k
3
votes
1
answer
104
views
case sensivity incongruity
I'm trying to learn and understand collations and how postgresql compare and order strings but I'm finding an incongruity and I don't know what I'm missing.
The query:
SELECT datname, datcollate FROM ...
- 247