ANSI String Concatenation in SQL Server 2025 and Azure SQL Database
SQL Server 2025 and Azure SQL Database bring the ANSI-standard || operator to T-SQL, giving developers a third option next to + and CONCAT(). It converts types implicitly like CONCAT() but propagates NULL like +.
Key points
- The || operator is new in SQL Server 2025 and Azure SQL Database; T-SQL previously had only + and CONCAT().
- It matches SQLite, PostgreSQL and Oracle syntax, so queries port between engines more easily.
- Implicit conversion works like CONCAT(): integers and other non-string types need no CAST().
- NULL handling works like +: any NULL operand makes the entire concatenated value NULL.
- + is the only one of the three that is also numeric addition and the only one without implicit casting.
- On older SQL Server versions you must stay with + or CONCAT().
SQL Server 2025 and Azure SQL Database add a third way to join strings in T-SQL: the ANSI-standard || operator, which PostgreSQL, Oracle and SQLite developers have used for years. It sits alongside the long-standing + operator and CONCAT(), introduced back in SQL Server 2012.
The new operator borrows one behaviour from each. Like CONCAT(), it converts numbers and other non-string values automatically, so no CAST() or CONVERT() is needed. Like +, it propagates NULL: if any operand is NULL the whole result is NULL, whereas CONCAT() quietly treats NULL as an empty string. The + operator remains the only one of the three that doubles as numeric addition, and the only one that refuses implicit conversion. On older servers you will still need + or CONCAT().
Real-world use
- Building display labels in a SELECT that combines a prefix with a nullable user id, where you want the row to be NULL rather than a partial string.
- Porting queries from PostgreSQL or SQLite that already rely on || so they run unchanged on Azure SQL Database.
- Generating report strings from mixed numeric and text columns without wrapping every value in CAST().
- Comparing legacy scripts that depend on CONCAT() turning NULL into an empty string before a migration.
Why it matters
Teams that move between SQL Server and other relational engines can now write the same concatenation syntax everywhere, but must consciously pick between NULL propagation and NULL-as-empty behaviour.