Questions tagged [collation]

Collation refers to a set of rules that determine how data is sorted and compared. Character data is sorted using rules that define the correct character sequence, with options for specifying case-sensitivity, accent marks, kana character types and character width.

Collation refers to a set of rules that determine how data is sorted and compared. Character data is sorted using rules that define the correct character sequence, with options for specifying case-sensitivity, accent marks, kana character types and character width.

Collation is often language, locale, and usage-dependent. Rules can change depending on the language being encoded (e.g., English vs. French), the region of the world (e.g., Canadian French vs. Standard French), and the purpose of the collation (e.g., a dictionary vs. a phone book). Further customisations like case-insensitive sorting are also possible.

Resources

1834 questions
86
votes
3 answers

How do I perform an accent insensitive compare (e with è, é, ê and ë) in SQL Server?

I'm looking to compare two varchars in SQL, one would be something like Cafe and the other Café is there a way in SQL that will allow the two values to be compared. For instance: SELECT * FROM Venue WHERE Name Like '%cafe%' So if there is a venue…
ilivewithian
  • 19,476
  • 19
  • 103
  • 165
84
votes
7 answers

When must we use NVARCHAR/NCHAR instead of VARCHAR/CHAR in SQL Server?

Is there a rule when we must use the Unicode types? I have seen that most of the European languages (German, Italian, English, ...) are fine in the same database in VARCHAR columns. I am looking for something like: If you have Chinese --> use…
Peter Gfader
  • 7,673
  • 8
  • 55
  • 56
80
votes
4 answers

Are column and table name case sensitive in MySQL?

If I have a column names called category_id and Category_Id, are they different? And if I have table called category and Category, are they different?
shin
  • 31,901
  • 69
  • 184
  • 271
74
votes
3 answers

Discover collation of a MySQL column

I previously created a MySQL table and now I want to find out what collation some of the fields are using. What SQL or MySQL commands can I use to discover this?
Trindaz
  • 17,029
  • 21
  • 82
  • 111
74
votes
6 answers

SQL Server default character encoding

By default - what is the character encoding set for a database in Microsoft SQL Server? How can I see the current character encoding in SQL Server?
David
  • 19,577
  • 28
  • 108
  • 128
72
votes
8 answers

Illegal mix of collations (utf8mb4_unicode_ci,IMPLICIT) and (utf8mb4_general_ci,IMPLICIT) for operation '='

I got this error; Illegal mix of collations (utf8mb4_unicode_ci,IMPLICIT) and (utf8mb4_general_ci,IMPLICIT) for operation '=' I changed "Collations" to "utf8mb4_unicode_ci". Then tables were truncated and I re-import rows again. But still getting…
Michael42
  • 881
  • 1
  • 8
  • 15
67
votes
2 answers

Doing a join across two databases with different collations on SQL Server and getting an error

I know, I know with what I wrote in the question I shouldn't be surprised. But my situation is slowly working on an inherited POS system and my predecessor apparently wasn't aware of JOINs so when I looked into one of the internal pages that loads…
Andrew G. Johnson
  • 26,603
  • 30
  • 91
  • 135
63
votes
5 answers

How to fix a collation conflict in a SQL Server query?

I am working on a view, wherein I am using an inner join on two tables which are from two different servers. We are using linked server. When running the query I am getting this message: Cannot resolve the collation conflict between…
Almas Mahfooz
  • 896
  • 1
  • 10
  • 19
59
votes
4 answers

What effects does using a binary collation have?

While answering this question, I became uncertain about something that I didn't manage to find a sufficient answer to. What are the practical differences between using the binary utf8_bin and the case insensitive utf8_general_ci collations? I can…
Pekka
  • 442,112
  • 142
  • 972
  • 1,088
58
votes
4 answers

Query to show all tables and their collation

Is there a query that can be run in mysql that shows all tables and their default collation? Even better if there was on that could show all collations on all columns of all tables.
Parris Varney
  • 11,320
  • 12
  • 47
  • 76
57
votes
6 answers

Using JavaScript to perform text matches with/without accented characters

I am using an AJAX-based lookup for names that a user searches in a text box. I am making the assumption that all names in the database will be transliterated to European alphabets (i.e. no Cyrillic, Japanese, Chinese). However, the names will still…
Philip
  • 3,689
  • 3
  • 24
  • 35
57
votes
20 answers

Illegal mix of collations error in MySql

Just got this answer from a previous question and it works a treat! SELECT username, (SUM(rating)/COUNT(*)) as TheAverage, Count(*) as TheCount FROM ratings WHERE month='Aug' GROUP BY username HAVING TheCount > 4 ORDER BY TheAverage DESC, TheCount…
Oliver
  • 809
  • 1
  • 11
  • 18
57
votes
18 answers

A script to change all tables and fields to the utf-8-bin collation in MYSQL

Is there a SQL or PHP script that I can run that will change the default collation in all tables and fields in a database? I can write one myself, but I think that this should be something that readily available at a site like this. If I can come up…
user19302
56
votes
4 answers

Difference between utf8mb4_unicode_ci and utf8mb4_unicode_520_ci collations in MariaDB/MySQL?

I logged into MariaDB/MySQL and entered: SHOW COLLATION; I see utf8mb4_unicode_ci and utf8mb4_unicode_520_ci among the available collations. What is the difference between these two collations and which should we be using?
Flux
  • 9,805
  • 5
  • 46
  • 92
56
votes
1 answer

Cannot Resolve Collation Conflict

I have moved one of our databases (DB1) from SQL Server 2008 to 2012 and when I run the stored procedures I get the following error Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Latin1_General_CI_AS" in the equal…
Silentbob
  • 2,805
  • 7
  • 38
  • 70