String concatenation operator in Oracle, Postgres and SQL Server
oracle, postgresql, sql, sql-server, string-concatenation
Solution
`||` is the SQL Standard concatenation operator (see SQL 2008: 5.2). Use that, and complain if it doesn't work in the system you're using ;-)
Seriously though, you should make other systems use `||`, not `+`. Not only is it more standard, but it's easier to accidentally cause confusion if you use `+`, especially if any types have to be inferred or and implicit casts are happening.
Consider: `'5' + 2`
If the system you're using doesn't throw an error on that one, and `+` means both plus and concatenation, you might be in for some confusing results.
Problem
Is there a way to have a common operator for concatenation in Oracle, Postgres and SQL Server. In Oracle and Postgres we use `||` and SQL Server uses `+`. I've solved the problem in Postgres by adding the custom operator `+` to support string concatenation. Is there a way to add the same operator in Oracle to support string concatenation using the `+` operator.