A collation conflict in a generated concatenation means two string inputs carry different collations, so the engine cannot give the combined string one clear collation. The reliable fix is to set the collation on the operand where the conflict starts, then check every comparison, sort, or grouping that uses the result. The rules differ by engine, so there is no single line that fixes every case. The examples below are labelled by engine and version.
The sources behind this guide document each engine’s collation rules, but they do not identify a particular query generator, merge tool, or SQL dialect. Treat the steps as a method you apply to your own generated SQL. Locking collation is a sound habit, but only after you have read the final SQL and the collations of its inputs.
| # | Preview | Product | Price | |
|---|---|---|---|---|
| 1 |
|
Concepts of Database Management (MindTap Course List) | $69.76 | Buy on Amazon |
| 2 |
|
Concepts of Database Management | $45.99 | Buy on Amazon |
| 3 |
|
Database Systems: The Complete Book | $184.50 | Buy on Amazon |
| 4 |
|
Database Management Systems | $432.87 | Buy on Amazon |
| 5 |
|
Database Systems: Design, Implementation, & Management (MindTap Course List) | $90.36 | Buy on Amazon |
As an Amazon Associate I earn from qualifying purchases.
What a collation conflict is
Every string expression has a collation. A column carries its declared collation. A literal takes its collation from context. An explicit COLLATE clause overrides both. When a generator joins pieces with +, ||, or CONCAT(), the engine derives one collation for the whole result from those pieces. If the pieces disagree and none of them is explicit, the result has no single usable collation, and any operation that needs one will fail.
Recommended Free Tools
The failure usually appears later than the concatenation itself. The SELECT list that builds the string may run without complaint. The error tends to surface in a WHERE comparison, an ORDER BY, a GROUP BY, a DISTINCT, or a join condition built from the same expression.
#1 Best Overall
Step 1: Inspect the generated expression
- Capture the final SQL text the generator produces, not the template. List every operand of every concatenation operator and function call in it.
- For each column operand, read its collation from the catalog. Use the query for your engine:
| Engine | Query that lists column collations |
|---|---|
| SQL Server | SELECT c.name, c.collation_name FROM sys.columns AS c WHERE c.object_id = OBJECT_ID(N'dbo.Customers'); |
| MySQL 8.4 | SELECT COLUMN_NAME, COLLATION_NAME FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'customers'; |
| PostgreSQL 17 | SELECT a.attname, c.collname FROM pg_attribute AS a JOIN pg_collation AS c ON c.oid = a.attcollation WHERE a.attrelid = 'customers'::regclass AND a.attnum > 0; |
- Note which operands are literals or variables. They take their collation from context, so a literal can sit quietly beside a conflicting column and hide the real source of the problem.
- Identify the operation that consumes the result. A value that is only returned to a client may never meet a collation check. A value used in a comparison, sort, grouping, or join must be checked.
Step 2: Decide which collation to apply
Choose the collation from the meaning of the comparison, not from the database default. Work through these points in order:
- Find the value the result is compared with or sorted against. If it is a column, its collation is the usual target, because that is the rule the data already follows.
- Apply the collation at the operand inside the generated fragment. Setting it on the outer query leaves the fragment’s behaviour dependent on where it is reused.
- Keep the fragment identical everywhere it appears. If a generator emits the concatenation once in the SELECT list and again in the WHERE clause with different operand order or different operands, the two copies can resolve differently. Generate the expression once and reuse the same text.
- Avoid
DATABASE_DEFAULTas a shortcut in SQL Server. It makes the result depend on the current database’s default collation, which may differ between development, test, and production. Use it only when that dependency is intended.
Engine by engine
SQL Server (Transact-SQL)
Microsoft’s documentation in Collation Precedence (Transact-SQL) defines four labels, listed here from highest to lowest precedence:
Rank #2
| Label | Typical source | Effect when combined |
|---|---|---|
| Explicit | An expression with a COLLATE clause |
Wins over every other label |
| Implicit | A column | Wins over coercible-default; two different implicit collations produce No-collation |
| Coercible-default | A literal or variable | Takes the collation of the other operand |
| No-collation | The result of combining conflicting implicit collations | Stays No-collation when combined with another non-explicit expression; can cause a compile-time error when a collation-sensitive operation uses it |
Illustrative schema, SQL Server, following the precedence rules in the Microsoft page above:
Quick wins for a faster PC:
Fix the driver behind crashes, sound loss and screen glitchesFind Drivers →Repair Windows errors before they cause bigger problemsFix Now →CREATE TABLE dbo.Customers (FirstName varchar(50) COLLATE Latin1_General_CI_AS NOT NULL, LastName varchar(50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL);nn-- Fails: two conflicting implicit collations produce No-collation, which the comparison cannot usenSELECT FirstName + ' ' + LastName AS FullName FROM dbo.Customers WHERE FirstName + ' ' + LastName = N'Ada Lovelace';nn-- Works: the explicit COLLATE on FirstName takes precedence over the implicit LastNamenSELECT FirstName + ' ' + LastName AS FullName FROM dbo.Customers WHERE (FirstName COLLATE Latin1_General_CI_AS) + ' ' + LastName = N'Ada Lovelace';The second version uses Latin1_General_CI_AS because it is the collation of FirstName in this example, so the comparison follows that column’s rules. The explicit label now controls the expression, and LastName‘s collation no longer decides the result. If the rules for LastName also matter, apply an explicit collation to it as well.
Rank #3
Microsoft documents three concatenation options. Their availability and behaviour differ, so confirm them against the version you run:
| Option | Documented availability | Notes |
|---|---|---|
+ |
SQL Server, documented as a concatenation option | A NULL operand makes the whole result NULL |
CONCAT() |
SQL Server, documented as a concatenation option | Treats NULL arguments as empty strings |
|| |
Documented for SQL Server 2025 (17.x) and specified Azure and Fabric services, per Microsoft’s || (String Concatenation) (Transact-SQL) | Not available on earlier builds covered by that page; check the page for your product before using it |
MySQL 8.4
MySQL’s Reference Manual page Collation Coercibility in Expressions (MySQL 8.4) ranks each argument by coercibility. The engine picks the argument with the lowest value:
Rank #4
| Coercibility value | Argument kind | Notes |
|---|---|---|
| 0 | Explicit COLLATE clause |
Strongest; overrides the others |
| 1 | Concatenation of two strings with different collations | Result has no collation of its own |
| 2 | Column, or stored routine variable or parameter | Implicit |
| 4 | Literal string | Coercible |
| Other values | Other argument kinds | Listed on the same manual page |
When two operands have the same coercibility, the result depends on character set and collation. MySQL converts automatically in some Unicode and non-Unicode cases, but the manual documents an error when equal-strength operands in the same character set use different collations. Do not rely on automatic conversion in generated SQL.
Do these 3 things before closing this tab:
1Scan for outdated or missing drivers - takes under a minute2Clear out junk files and repair common Windows errors3Fix the driver behind crashes, sound loss and screen glitches-- MySQL 8.4: two utf8mb4 columns with different collations, both coercibility 2nCREATE TABLE customers (first_name VARCHAR(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci, last_name VARCHAR(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin);nn-- Fails with an "Illegal mix of collations" errornSELECT CONCAT(first_name, ' ', last_name) AS full_name FROM customers;nn-- Works: the explicit COLLATE has coercibility 0 and winsnSELECT CONCAT(first_name COLLATE utf8mb4_0900_ai_ci, ' ', last_name) AS full_name FROM customers;The fixed version uses utf8mb4_0900_ai_ci as an example. That collation is accent- and case-insensitive, so choose the collation that matches the comparison you need. A SQL Server fix cannot be copied into MySQL, because the label precedence there is different from MySQL’s coercibility values.
PostgreSQL 17
PostgreSQL’s Collation Support chapter (PostgreSQL 17 documentation) uses its own collation objects and conflict rules. Do not map them onto SQL Server’s labels or MySQL’s coercibility numbers. The practical distinction is:
- A conflict between two explicit collations is an error immediately, typically reported as a collation mismatch between explicit collations.
- A conflict between implicit collations is not an error by itself. The result’s collation becomes indeterminate, and an error is raised only when a collation-sensitive operation needs one. That usually appears as
could not determine which collation to use for string comparison.
List the collations available on your server with SELECT collname FROM pg_collation;. The example below uses C and en_US, which exist on many systems, but the second name depends on your operating system and locale setup.
Quick Recap
-- PostgreSQL 17: two text columns with different collationsnCREATE TABLE customers (first_name text COLLATE "C", last_name text COLLATE "en_US");nn-- The SELECT list can run, but ORDER BY needs one collation and fails with the implicit conflictnSELECT first_name || ' ' || last_name AS full_name FROM customers ORDER BY first_name || ' ' || last_name;nn-- Works: the explicit collation on first_name controls the expressionnSELECT (first_name COLLATE "C") || ' ' || last_name AS full_name FROM customers ORDER BY (first_name COLLATE "C") || ' ' || last_name;Comparing the three engines
| Question | SQL Server | MySQL 8.4 | PostgreSQL 17 |
|---|---|---|---|
| How is the result’s collation derived? | From labels: explicit, implicit, coercible-default; conflicting implicit collations give No-collation | From coercibility values; the lowest value wins | From PostgreSQL’s collation derivation rules for explicit, implicit, and default collations |
| What happens on conflict? | No-collation fails only when a collation-sensitive operation consumes it | Error when equal-strength operands in the same character set use different collations | Explicit conflicts error at once; implicit conflicts error only when an operation needs one collation |
| Where can an explicit collation go? | On an operand or expression with COLLATE |
On an operand with COLLATE |
On an operand with COLLATE |
| Concatenation syntax | +, CONCAT(), and || on SQL Server 2025 (17.x) and listed Azure and Fabric services |
CONCAT(); behaviour of || is not stated in the MySQL 8.4 coercibility reference |
|| |
Verify the merged query
- Run the merged query on each engine and version you deploy to. A query that passes on a development instance with a different default collation can fail in production.
- Test with sample rows that differ only in case and accents, such as
Ada,ADA, andAdà. Collation changes comparison and sort results, so a single-case sample proves little. - Check the ORDER BY output with the same sample rows. A sort that looks right in a plain SELECT can change once the expression feeds an ORDER BY.
- If the expression touches an indexed column, compare execution plans before and after the change to confirm that the index is still used where you expect.
Troubleshooting by error message
- SQL Server: “Cannot resolve the collation conflict” usually means a No-collation result reached a comparison, sort, or grouping. Apply an explicit
COLLATEto the operand that starts the conflict. - MySQL: “Illegal mix of collations” means two equal-strength operands in the same character set use different collations. Add an explicit
COLLATEto one operand, and make the choice match the comparison. - PostgreSQL: “collation mismatch between explicit collations” means two explicit
COLLATEclauses disagree. Remove one or align both to the same collation. - PostgreSQL: “could not determine which collation to use for string comparison” means implicit collations conflict in a comparison or sort. Add an explicit collation to the operand that starts the conflict.
- Same query, different ordering across environments usually points to a dependency on the database default collation. Replace the default-dependent behaviour with an explicit collation that you choose.
]]>
Product prices and availability are accurate as of the date/time indicated and are subject to change. Any price and availability information displayed on Amazon at the time of purchase will apply.
