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.
0
votes
1answer
16 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.
...
3
votes
2answers
143 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 ...
2
votes
2answers
57 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.
...
0
votes
1answer
42 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(...
1
vote
1answer
46 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
...
0
votes
0answers
35 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 ...
1
vote
2answers
63 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 ...
2
votes
2answers
165 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
...
3
votes
2answers
521 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 ...
6
votes
2answers
60 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 '...
4
votes
1answer
432 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_=...
1
vote
1answer
61 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, ...
1
vote
1answer
124 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". ...
3
votes
2answers
68 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
2answers
155 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....
2
votes
0answers
373 views
Use LIKE in column with arabic
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
2answers
206 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 ...
0
votes
1answer
32 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
3answers
135 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
vote
2answers
2k 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 "...
4
votes
1answer
2k 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('...
2
votes
1answer
70 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 ...
6
votes
1answer
206 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 ...
0
votes
1answer
56 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 ...
3
votes
3answers
7k 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-...
4
votes
1answer
83 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' +...
3
votes
1answer
1k 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 ...
11
votes
1answer
163 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 ...
1
vote
2answers
863 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 ...
5
votes
2answers
1k 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 (...
2
votes
1answer
137 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 ...
18
votes
2answers
290 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
...
2
votes
0answers
52 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 ...
4
votes
2answers
232 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]...
4
votes
1answer
327 views
Collate issues with wrong characters
Well, the problem is well known, but I'm looking for a smarter solution if there's one.
For some reason the system doesn't recognize some characters and I can't compare the columns
Here is an ...
1
vote
1answer
612 views
Postgres collate example in select?
I would like to comprehend the different behavior of like and =. Sadly, I can not reproduce the following simple example thus that both queries return a different result:
SELECT 'ä' LIKE 'ae' COLLATE ...
0
votes
0answers
50 views
Illegal mix of collations, 'ALTER' not helping
I'm trying to connect some program to MySQL server and keep getting this error:
Illegal mix of collations (cp1257_general_ci,IMPLICIT) and (latin1_swedish_ci,IMPLICIT) for operation '='
I ...
3
votes
1answer
381 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 ...
4
votes
1answer
488 views
Change default collation when provisioning a SQL Server VM in the Azure portal
Is there a way to change the default SQL collation directly from the "Create virtual machine" wizard in Azure ? The SQL VM images from the gallery are set for SQL_Latin1_General_CP1_CI_AS.
Browsing ...
2
votes
2answers
301 views
Is it OK to Change System Locale after MSSQL2012 installation
I have a MS SQL Server 2012 instance preinstalled on a Windows 2012 R2 production server. The Windows locale was English(EN-US), and SQL Server Collation is SQL_Latin1_General_CP1_CI_AS. The system ...
18
votes
2answers
2k views
Can't update “CO2” to “CO₂” in table row
Given this table:
CREATE TABLE test (
id INT NOT NULL,
description NVARCHAR(100) COLLATE Modern_Spanish_CI_AS NOT NULL
);
INSERT INTO test (id, description) VALUES (1, 'CO2');
I've realised ...
16
votes
1answer
6k views
Ignore accents in 'where'
In our database we have multiple entries with caron/hatschek.
Now our users want to find entries including caron/hatschek when they search for entries without. I'll show this by a simple example:
In ...
2
votes
0answers
71 views
Linked servers not ignoring case after reboot
We have a SQL Server 2014 database with collation SQL_Latin1_General_CP1_CI_AS, which is case insensitive. It is linked to another SQL server database with the same collation. The second database ...
1
vote
2answers
207 views
Text index using text_pattern_ops with utf-8 vs. default operator class with C?
What is happening with each operator class, and why is it necessary to use text_pattern_ops when using utf-8?
When using the default operator class with C, what happens to the text column indexes if ...
0
votes
1answer
241 views
Does sqlite preserve COLLATE on tables in a view?
In sqlite3 collations are defined per-column. If you CREATE TEMPORARY TABLE foo AS SELECT ... the resulting temporary table foo does not inherit any custom collations from the columns in the SELECT. ...
1
vote
1answer
434 views
Creating Oracle 12c R2 user with default collation binary_ci fails
If anyone can give me some guidance on this one I'd really appreciate it.
I am attempting to get a user under Oracle 12c R2 configured with default collation binary_ci.
CREATE USER <some user> ...
2
votes
0answers
362 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
1answer
933 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 ...
2
votes
1answer
214 views
Correct case for each reserved word in SQL Server? [closed]
Is the most correct spelling for int upper case INT or lower case int?
There are many other reserved words in SQL Server.
How to find out the best case for each word?
For example the picture below ...
11
votes
4answers
2k views
Why is mixing column collations in a single database considered bad?
There are two reasons that prompts me to ask this question:
tSQLt
The T-SQL testing framework tSQLt considers it an issue of "High Severity" when there exists columns with a non-default collation. ...
