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
1
vote
1
answer
1k
views
Why does setting my column as Always Encrypted change its collation from SQL_Latin1_General_CP1_CI_AS to Latin1_General_BIN2?
I am encrypting a VARCHAR(50) NULL column as randomized. When doing this, the collation is changed from SQL_Latin1_General_CP1_CI_AS to Latin1_General_BIN2.
I cannot find any resources explaining why ...
- 169
15
votes
3
answers
19k
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 ...
- 308
1
vote
2
answers
2k
views
MySQL and MariaDB: COLLATE on table vs COLLATE on all columns?
Will the following table behave or perform any differently
CREATE TABLE t1 (
foo char(60) COLLATE utf8_bin,
bar char(64) COLLATE utf8_bin,
baz char(80) COLLATE utf8_bin
) DEFAULT CHARSET=utf8;
...
- 59.6k
7
votes
2
answers
13k
views
Change default collation for character set utf8mb4 to utf8mb4_unicode_ci
When creating a database without specifying a character set or collation the servers defaults are used (as expected).
MariaDB [(none)]> SHOW VARIABLES LIKE '%_server' ;
+----------------------+----...
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
2k
views
MySQL Case Sensitive Join on Varchar
I have two tables, both with identical data types, character sets, and collation explicitly specified.
CLERK CHAR(3) CHARACTER SET latin1 COLLATE latin1_bin NULL
For a case-sensitive join on the ...
- 2,687
2
votes
1
answer
540
views
Error: 468 trying to connect to SQL server with SQL Server management studio
I'm trying to connect to SQL Server 12 with SQL Server management studio 2014.
The connection is succesful, but when I try to see properties of a specific database with mouse right button I get this ...
- 123
0
votes
0
answers
243
views
After upgrading from PG 9.3 to 10 indices became corrupted
So recently I was upgrading the database in production from Postgres 9.3 to 10. In addition to upgrade, we wanted to move the database to another machine. I know, doing it "in place" would be easier ...
- 1
4
votes
3
answers
422
views
Sql server 2016 Change only server and without all databases [duplicate]
How can i change server collation without changing whole databases, because i have
many databases which aren't belong to me, so i want change server collation and my database only.
SQL Server ...
- 141
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 '%?%'
...
- 143
1
vote
0
answers
714
views
Setting utf8mb4_unicode_ci as the default for all utf8mb4 character set tables
I am running MySQL 5.7.19 in RDS and recently went through a conversion of everything to Character Set = UTF8MB4 and Collation = UTF8MB4_unicode_ci. I set the following parameter group settings and ...
- 11
1
vote
1
answer
368
views
Why doesn't MySQL coerce the collation to the column-specified, when comparing to a literal?
I have a table with a collation defined on a column:
CREATE TEMPORARY TABLE test_table (
utf8_col VARCHAR(255) CHARACTER SET utf8 COLLATE utf8_bin
);
INSERT INTO test_table VALUES('ã');
According ...
- 327
6
votes
1
answer
548
views
SQL Server 2016 Collation and Internal table Glitch?
My Steps:
installed SQL 2016 with collation SQL_Latin1_General_CP850_CI_AS
Restored Database to Stage instances with same collation as Database.(original Backup from 2012)
Ran > "C:\Program Files\...
6
votes
2
answers
2k
views
Process to change collation on a database
I've been asked to change the collation on a database that has been in production for a few years as it was not set correctly at the time of deployment.
So my research shows that in order change ...
- 7,869
2
votes
1
answer
1k
views
Implications of changing SQL Server collation
We are currently using SQL Server 2016 and currently the collation set on our database is SQL_Latin1_General_CP1_CI_AS. Our Analaysis services' collation is set to SQL_Latin1_General_CP1_CS_AS.
The ...
- 131
2
votes
2
answers
2k
views
Using CONVERT TO CHARACTER SET for changing only collation?
I think the answer to this is an obvious yes, but I'm having trouble finding confirmation on it. All the questions related to charsets seems to be about changing the charset and not only collations.
...
- 63
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
2
votes
2
answers
3k
views
Database Collation problem when comparing two databases
I have changed the collation of test database using the follwoing query:
USE master;
GO
ALTER DATABASE test
COLLATE SQL_Latin1_General_CP1_CI_AI ;
GO
--Verify the collation setting.
...
- 231
2
votes
1
answer
11k
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
1
vote
1
answer
154
views
MySQL not using indexes
My query does not seem to bother for using indexes.
Collations are the same on each table.
EXPLAIN SELECT SUM(it.revenue) FROM invoice_items AS it
INNER JOIN invoice AS i ON i.id = it.invoice_id
...
- 113
0
votes
0
answers
476
views
Resolving "there is unknown charset/collation names" warnings on views
I recently moved our MySQL 5.6 server over to MariaDB 10.3.10 and, for the most part, have not had any problems. We have a couple of databases that have close to 1000 views which are now filling the ...
- 393
2
votes
1
answer
3k
views
How to insert a Unicode character verbose into a varchar DB?
I need to insert this character '●' into a VARCHAR column of a MSSQL database with collation set as SQL_Latin1_General_CP1_CI_AS (or at least mock what my Python + Windows MSSQL Driver might have done)...
- 807
1
vote
2
answers
93
views
SQL Server Execution Plan on server level
Case: I have application which is doing search from database. That is going very slowly (more than 40 seconds). With trace I caught a query. When I run query in SSMS, it takes 2-3 seconds, but when I ...
- 454
2
votes
2
answers
2k
views
How to join global temp tables in SQL Server?
I have four global temp tables (##A,##B,##C,##D) on which I want to perform a left join in SQL Server 2008. By global temp tables I mean I created them using a code like this:
SELECT *
INTO ##A
...
- 307
3
votes
2
answers
3k
views
SQL Server 2019 UTF-8 Support Benefits
I'm already quite comfortable with using COMPRESS() and DECOMPRESS() in an internal forum software for our company (Currently in SQL Server 2017), but trying to make the database as efficient as ...
- 31
6
votes
2
answers
229
views
VARCHAR memory buffer appears to be breaking in ChineseTaiwan Collation
It is difficult for me to write something in English. I hope you understand.
I used a translator and reviewed it.
I created two temporary databases. The collations are 'Korea_Wansung_CI_AS' and '...
minchul.kang
5
votes
1
answer
2k
views
What are the rules for using COLLATE in a query?
I am putting a script together, that gives me the permissions on database objects.
SELECT permission_order=710
,permission_type = 'Object Level Permissions'
,db = db_name(),
login_=...
- 15.4k
1
vote
1
answer
672
views
SSIS Data Flow Task Violates Unique Constraint But Linked Server Insert Succeeds
Ultimately, what I am trying to do is create an SSIS package in the QA environment for another team which truncates the destination tables and inserts from the source tables. However, on one table, ...
- 67
3
votes
1
answer
3k
views
Are utf8_bin and utf8_general_cs the same?
Here's the situation:
I have come across several posts for "utf8_bin vs utf8_general_ci".
Similarly, "utf8_general_cs vs utf8_general_ci".
But I can't find a post for "utf8_bin vs utf8_general_cs". ...
- 624
3
votes
2
answers
186
views
Making collations uniform across multiple SQL Server 2008 databases [closed]
I have been assigned to try and make collations uniform across databases. Currently, it's a mess, different collations everywhere.
This is mostly solved by using collate database default in queries ...
3
votes
2
answers
2k
views
index not being used due to different collations - is there any work around?
I am running the following query:
SET ROWCOUNT 50
SELECT UL.*
FROM COLA.dbo.tbl_UserLogin ul
INNER JOIN CABrochure.dbo.tbl_country co
ON ul....
- 15.4k
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 ...
1
vote
2
answers
2k
views
Wrong sort order using utf8_unicode_ci and swedish characters like ö
I am confused about what encoding and collation I should use so my queries returns the correct sort order.
Right now my encoding is UTF-8 Unicode and collation utf8_unicode_ci for both firstname and ...
- 117
0
votes
1
answer
44
views
Code not working due to collation?
Let me preface my question by saying, that my teacher stared at my code for 2h and could only guess that it might be the collation.
Here's the problem: for an exercise we were asked to create a movie ...
2
votes
3
answers
177
views
Why does using Format vs Right to apply padding cause estimated number of rows to dramatically change?
I was working on a query at work, that had a left join like
cast(cola as varchar) + '-' + right('000' + cast(colb as varchar), 3) = x
The actual execution plan for this query was fairly close, est ...
- 1,043
1
vote
2
answers
19k
views
Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Latin1_General_CI_AI"
We recently started getting the following error while executing a stored procedure:
System.Data.SqlClient.SqlException (0x80131904): **Cannot resolve the collation conflict between "...
5
votes
1
answer
17k
views
Why do I get incorrect characters when decoding a Base64 string to NVARCHAR in SQL Server?
I've been looking at how to decode Base64 with SQL Server and after searching around many solutions online (some from here) seem to be based upon this sort of method.
SELECT CAST(CAST('...
- 153
2
votes
1
answer
196
views
SQL Server 2008R2 database migration to cloud: case-insensitive collation changed to case-sensitive
Recently felt the fall out (crappy legacy code base) of a database migration by a third party vendor where the collation was swapped from case-insensitive to case-sensitive. I am wondering if the ...
- 133
7
votes
1
answer
3k
views
Why do two identical strings have a different length but the same binary value?
I am trying to return a distinct set of department names from a table - nothing special. However, when using the following query duplicates are displayed:
select distinct department_name
from ...
- 12.9k
0
votes
1
answer
475
views
What is the MySQL equivalent of Postgres' C collation?
I'm working on reimplementing a Postgres schema in MySQL, and one of the things I can't seem to find a clear equivalent for is Postgres' "C" collation.
id TEXT COLLATE "C" NOT NULL
What would be the ...
- 217
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-...
- 2,562
4
votes
1
answer
845
views
Why isn't Unicode character REPLACEd in some cases?
Running this results in "test", as expected:
SELECT
REPLACE(NCHAR(1234), NCHAR(1234), N'test');
However, running this results in "aӒa", which doesn't have "test" in it:
SELECT
REPLACE(N'a' +...
- 1,831
3
votes
1
answer
11k
views
inserting a special character into a table
I have a few characters that are being replaced by a ?. How do you keep the original character in the table? The character that I am trying to insert is a ɬ; Latin Small Letter L With Belt. Is it ...
- 167
11
votes
1
answer
359
views
N'Șc' considered duplicate key of N'C' using Latin1_General_CI_AS collation
I have a table with a unique key that includes an NVARCHAR(50) column (correct or not, but is there). So, when trying to insert Șc or C (doesn't matter the order of the insert) it breaks on the 2nd ...
- 2,806
4
votes
2
answers
14k
views
Character Conversion Issue Importing From CSV File
After doing a load of CSV files, there are various words that are "written" to the database incorrectly.
Some examples:
Diã¡ria should be Diária
Crã©dito should be Crédito
Ligaã§ãµes should be ...
- 523
5
votes
2
answers
9k
views
SQL Server Bulk Insert properly interprets some Unicode characters but not others?
For some reason the MS SQL Server 2016 bulk insert misinterprets/translates Unicode characters:
C9 (É) into 2B (+)
A1 (¡) into ED (í)
A0 ( ) into E1 (á)
AE (®) into AB («)
CB (Ë) into 2D (-)
D1 (...
- 313
2
votes
1
answer
670
views
Changing Collation with a High Availability server
How do I change a DB Collation without removing the DB from high availability group? Can I just take it offline and change it? or I really do need to take it out of high availability group 1st then ...
- 147
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
2
votes
0
answers
65
views
Writing data to new database - collation question [duplicate]
We will be changing the collation of our databases in a few months. I am setting up an environment, to test the new collation with my application. I have created a database with our new collation and ...
- 21
4
votes
2
answers
1k
views
Error while copying data over linked server
I am trying to copy the contents of 1 table from one database to another over a linked server.
I am using the following query to do so
insert into [Schema].[Table] select * from [Server].[Database]...
- 676