The Tool Desk
Outbyte PC Repair FREEClear out junk files and repair common Windows errorsFree Scan →Outbyte Driver Updater FREEScan for outdated or missing drivers - takes under a minuteDriver Scan →iTechGuides is reader-supported. When you buy through links on our site, we may earn an affiliate commission. As an Amazon Associate I earn from qualifying purchases. Learn more
A generated string concatenation is safe to merge only when you know which collation its result will carry. If the parts come from columns, literals, or expressions with different collations, the combined string may have no usable collation, and the failure shows up later, at the comparison, ORDER BY, or GROUP BY that consumes it. The fix is to choose a collation deliberately at the point where the parts are combined, and then confirm that every downstream operation behaves the way you intend.
No single fix covers every case. SQL Server, MySQL, and PostgreSQL each derive expression collation by their own rules, so the examples below are labeled by engine and version, and none of them should be copied across engines. This article does not assume a particular query generator, merge tool, or SQL dialect. Treat the code as patterns to adapt, and check each one against your target database before deploying.
| # | Preview | Product | Price | |
|---|---|---|---|---|
| 1 |
|
Concepts of Database Management (MindTap Course List) | $69.83 | 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 |
What a concatenated string inherits
A string expression has collation behavior of its own. Its result collation comes from its inputs: a column reference, a literal, a variable, or an explicit COLLATE clause can each contribute. When two inputs disagree, the engine either picks a winner using its own precedence rules or refuses to pick one. The concatenation itself does not fail at that moment in every engine. The failure usually appears when something needs a single collation to make a decision, such as an equality test, a sort, or a grouping.
What’s actually slowing this PC down?
Pick the symptom - the matching free tool is one click away.
This is why a generated query can run cleanly in a test table and fail in production. The test data may use one collation everywhere, while the real tables do not.
#1 Best Overall
Check the generated expression before you merge it
Work through these steps before a generated concatenation is inserted into a larger statement.
-
Capture the concatenation expression as text. Do not inspect only the final query, because the boundary where the parts meet is where the decision happens.
-
List each operand and its source: a column, a literal, a variable, or an expression that already carries
COLLATE.Free tools Windows power users keep installed
One-click scans. No signup required.
Special offer. See more information about Outbyte and uninstall instructions. Please review EULA and Privacy policy. -
Record the collation of every column operand. The commands differ by engine:
Rank #2
- SQL Server:
SELECT name, collation_name FROM sys.columns WHERE object_id = OBJECT_ID('dbo.Customers'); - MySQL:
SHOW FULL COLUMNS FROM customers;and read theCollationcolumn. - PostgreSQL:
d+ customersinpsql, which lists the collation for each column.
- SQL Server:
-
Identify the operation that consumes the result: an equality or
LIKEcomparison, anORDER BY, aGROUP BY, aDISTINCT, or an index lookup. -
Choose one intentional collation for the combined value. Apply it at the expression boundary, not by rewriting every upstream column.
-
Run the merged statement against the target engine and version with data that includes the mismatched collations. Confirm both that it executes and that the ordering and equality results match what you expect.
DriversOutdated Drivers Are Slowing You DownPerformanceWindows Errors? Fix Them Before They SpreadDriversCrashes, No Sound, or Screen Glitches?Special offer. See more information about Outbyte and uninstall instructions. Please review EULA and Privacy policy.
SQL Server
The relevant reference is Microsoft Learn’s Collation Precedence (Transact-SQL). It defines four labels: Explicit, Implicit, Coercible-default, and No-collation. Explicit takes precedence over Implicit, and Implicit takes precedence over Coercible-default. Column references are typically Implicit, while literals and variables are typically Coercible-default.
Rank #3
Two Implicit operands with different collations produce a No-collation result. Combining that result with another non-explicit expression keeps it at No-collation. Concatenation is collation-sensitive, so a No-collation result can cause a compile-time error when a later collation-sensitive operation uses it.
A conflict and an explicit fix
Assume dbo.Orders.ShipName and dbo.Customers.Region are columns with different collations, and a generated query builds a label from them and sorts by it.
-- SQL Server (T-SQL), conflicting implicit collations
SELECT o.ShipName + c.Region AS Label
FROM dbo.Orders AS o
JOIN dbo.Customers AS c ON c.CustomerID = o.CustomerID
ORDER BY Label;The sort can fail with a collation conflict error. Applying an explicit collation to each operand before the + resolves it:
Recommended Free Tools
-- SQL Server (T-SQL), explicit collation at the operand boundary
SELECT (o.ShipName COLLATE Latin1_General_CI_AS) + (c.Region COLLATE Latin1_General_CI_AS) AS Label
FROM dbo.Orders AS o
JOIN dbo.Customers AS c ON c.CustomerID = o.CustomerID
ORDER BY Label;The collation Latin1_General_CI_AS is used here only because it is a common case-insensitive, accent-sensitive choice that suits this illustrative sort. Pick the collation your data and business rules require. Avoid DATABASE_DEFAULT as a general answer, because it hides a dependency on whatever the current database default happens to be.
Rank #4
Concatenation syntax by version
SQL Server offers several ways to concatenate: the + operator, the CONCAT() function, and the || operator. Microsoft’s || (String Concatenation) (Transact-SQL) reference documents || for SQL Server 2025 (17.x) and certain Azure and Fabric services. If your deployment is an earlier SQL Server version, use + or CONCAT() and confirm the syntax against your version before relying on it.
MySQL
MySQL resolves expression collation through coercibility values, documented in the MySQL 8.4 Reference Manual under Collation Coercibility in Expressions. An explicit COLLATE clause has the strongest priority, with coercibility 0. Columns and routine variables have coercibility 2, and literals have 4. Other argument types have their own values. The engine uses the operand with the lowest value.
When two operands have equal coercibility, the outcome depends on character set and collation. The manual documents automatic conversion in some Unicode and non-Unicode combinations. It also documents an error when equal-strength operands in the same character set use different collations, which MySQL reports as an illegal mix of collations.
Do these 3 things before closing this tab:
1Fix the driver behind crashes, sound loss and screen glitches2Clear out junk files and repair common Windows errors3Scan for outdated or missing drivers - takes under a minute-- MySQL 8.4, two columns with equal coercibility and different collations
SELECT CONCAT(a.code, b.code) FROM a JOIN b ON b.id = a.id;
-- MySQL 8.4, explicit COLLATE on one argument (coercibility 0) wins
SELECT CONCAT(a.code, b.code COLLATE utf8mb4_bin) FROM a JOIN b ON b.id = a.id;The second form makes the explicit collation decisive. Check that the chosen collation is the one your comparisons and sorts need, because the other argument is converted to it. Normalizing the inputs earlier in the pipeline is an alternative when a single explicit clause would be hard to maintain.
Independent reader supportYour contribution helps us test, update, and keep practical guides available for everyone.PostgreSQL
PostgreSQL documents collation conflicts and explicit collation specifiers in its Collation Support chapter for version 17. Its collation objects and rules are PostgreSQL-specific. SQL Server’s labels and MySQL’s coercibility numbers do not apply here, so reason from the PostgreSQL manual for your version.
An explicit collation is attached with COLLATE. Parenthesize the concatenation so the clause applies to the whole result:
-- PostgreSQL 17, explicit collation on the concatenated result
SELECT a.code, b.code
FROM a JOIN b ON b.id = a.id
ORDER BY (a.code || b.code) COLLATE "C";Collation names are catalog-specific and vary by operating system and locale provider. List the names available on your server with SELECT collname FROM pg_collation; before choosing one. The "C" collation is shown only because it is a standard byte-order choice for this example.
Comparing the three engines
| Question | SQL Server | MySQL | PostgreSQL |
|---|---|---|---|
| How is the result collation derived? | Four labels (Explicit, Implicit, Coercible-default, No-collation) with a fixed precedence order | Coercibility values: explicit COLLATE 0, column 2, literal 4, others have their own values; lowest wins | Collation objects and rules specific to PostgreSQL; see the PostgreSQL 17 manual |
| What happens when inputs conflict? | Two Implicit operands with different collations give No-collation, which can error in a later collation-sensitive operation | Equal coercibility with different collations can convert in some Unicode cases or error with an illegal mix of collations | A conflict is reported when an operation needs one collation; resolve it with an explicit collation |
| Where can an explicit collation go? | On an operand or on the expression, using COLLATE |
On any argument, using COLLATE |
On the expression, using COLLATE with a quoted collation name |
| Concatenation syntax | +, CONCAT(); || documented for SQL Server 2025 (17.x) and certain Azure and Fabric services |
CONCAT() as shown in the MySQL 8.4 manual |
|| operator; check the PostgreSQL 17 manual for the function form on your version |
What locking collation does and does not solve
Choosing a collation explicitly is a sound design practice when a generated query combines columns whose collations you do not control. It is not a substitute for the checks above. An explicit collation can be correct for one comparison and wrong for another, so verify each consuming operation.
Quick Recap
- It makes the intended collation visible in the generated SQL, which helps reviewers and future debugging.
- It does not fix a wrong collation choice. If the chosen collation is case-sensitive where your data is not, equality tests will change behavior.
- It does not replace checking the source column types. A collation conflict can come from a column that was recently altered.
- It does not transfer between engines. Each engine needs its own test.
Troubleshooting a collation conflict
- The error appears only in an
ORDER BYor comparison. The concatenation is likely producing an unresolved collation. Add an explicit collation at the operand or expression boundary, then re-run the consuming operation. - The error appears only in production. Compare the column collations in the two environments with the commands in the workflow above. Test data often shares one collation.
- The query works, but results differ from what you expected. Check the collation you applied. A case-sensitive or accent-sensitive choice changes equality and sort order.
- The same generator produces different SQL for different engines. Keep one collation decision per engine, and label each generated template with the engine and version it targets.
“
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.
