21

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.

Arun P Johny
  • 384,651
  • 66
  • 527
  • 531
  • why do all database have to have the same syntax? if they were all the same, there would only be one. application languages all have different syntax? – KM. Sep 03 '09 at 13:16
  • 10
    All C compilers parse the same syntax, why shouldn't SQL parsers do the same? – ijw Sep 03 '09 at 13:23
  • 1
    SQL Server and Sybase use the TSQL language and Oracle uses the PL/SQL language. TSQL is different than PL/SQL. – KM. Sep 03 '09 at 13:39
  • 3
    Yes, but concatenation is part of boring old SQL, not the procedural language that goes with it. It's even defined in ANSI SQL (as ||). T-SQL and PL/SQL (and the others) have SQL DML as a subset, but both of them deviate from the ANSI standard. – ijw Sep 03 '09 at 14:21
  • 2
    @KM: Oracle uses PL/SQL **only** for stored procedures. Everything else is "SQL". Unlike SQL Server, Oracle makes a clear distinction between the query language (SQL) and the procedural language (PL/SQL) –  Jan 20 '15 at 16:59

3 Answers3

36

|| 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.

Jeff Davis
  • 1,150
  • 8
  • 6
  • 5
    So, Microsoft don't support the same standard as everyone else, and he should complain to MS and tell them to change their ways. Yeah, that'll work... ;-) – ijw Sep 04 '09 at 14:26
  • 9
    @ijw, Or simply "upgrade" to a better database :) – Cerin Aug 22 '14 at 18:01
  • Complain here: https://connect.microsoft.com/SQLServer/feedback/details/259291/support-iso-9075-standard-string-concatenation-double-pipe – codekaizen Dec 21 '17 at 20:55
24

You can't overload operators in Oracle. the "+" overload wouldn't work anyway, since Oracle does automatic type conversions ('1'+'1'=2).

The concatenation operator used by Oracle is ||, which is also ANSI-compliant.

There is also the CONCAT function which (as of postgres 9.0 and SQL Server 2012) is supported by all three RDBMSs you need it for.

Note that the Oracle version of CONCAT is not variadic like the other two. If you need to concatenate three or more strings you will need to nest:

CONCAT(s1,CONCAT(s2,s3))
Vincent Malgrat
  • 66,725
  • 9
  • 119
  • 171
  • 1
    The problem is my current project uses '+' as the string concatenation operator in most of the places. It is works with both postgres ans sql server. Now we want the project to work with oracle also. That is why I need '+' to work in Oracle. If it is not working then we have to change most of our code where we uses '+', to a common operator '||' if it works in all the places. I need a solution which will work in the 3 databases without much changes in the existing system – Arun P Johny Sep 04 '09 at 01:24
  • 1
    This function is also available in PostgreSQL 9 : http://www.postgresql.org/docs/9.1/static/functions-string.html – FGM Nov 22 '13 at 16:10
  • 6
    @APC [`CONCAT()` was added in SQL Server 2012](http://msdn.microsoft.com/en-us/library/hh231515(v=sql.110).aspx). – Aaron Bertrand Jan 20 '15 at 16:06
9

|| certainly works in Oracle, though not apparently SQL Server. (For those who come after us, here's a rosetta stone for SQL: SQL Dialects Reference)

If you're fixing up SQL scripts, I would consider the following solution:

BEFORE:

sql-shell-command < sql-file.sql

(sql-file contains '+' operators)

AFTER:

ansi-sql-shell-command < sql-file.sql


sed -e 's/||/\+/' < sql-file.sql | ms-sql-shell-command

(sql-file contains || operators, you'd have to convert your files)

The idea is that you start with SQL in one format, and for the special case, you run a filter over it to transform it to the other format. Theoretically, you could turn all +es into ||s, but since a good proportion of those might be numeric-add rather than string-concatenation, that's unlikely to work as well.

The complexity of your filter depends on what you're doing. If you have arbitrary data in your SQL, then you'd have to get it to avoid substituting in strings. But if you're setting up views it'll probably be fine.

You could use the same technique in programs where the SQL is in strings - write a function in the program to turn it from one form to the other.

ijw
  • 4,376
  • 3
  • 25
  • 29