12

Would it be possible to explain the difference between the concat() function and the || operator in Oracle?

Which one is better in terms of performance?

Ollie
  • 17,058
  • 7
  • 48
  • 59
Java SE
  • 2,073
  • 4
  • 19
  • 25

2 Answers2

21

There is no functional difference.

|| is the ANSI standard string concatenation operator (though, unfortunately, not every database <cough>SQL Server</cough> chooses to support the standard). Many databases support a CONCAT function so it may be easier to port code using CONCAT to different databases.

Justin Cave
  • 227,342
  • 24
  • 367
  • 384
  • I know only two DBMS that don't comply with the SQL standard: SQL Server and MySQL. And MySQL can at least be configured to accept `||` as the concatenation operator. –  Jul 09 '12 at 22:27
  • 1
    Oh come on SQL server has an equivalent operator `+` which is just like [oracle's partial support of ANSI standard functions](http://docs.oracle.com/cd/B19306_01/server.102/b14200/ap_standard_sql003.htm#sthref10080) CHARACTER_LENGTH, OCTET_LENGTH , SUBSTRING or POSITION functions. – Conrad Frix Jul 09 '12 at 22:57
  • 3
    @ConradFrix - True, and I certainly don't mean to imply that any database implements the entire SQL standard set of functions (someone will have to explain why it isn't easier to implement `POSITION` and `SUBSTRING` rather than documenting that they don't exist, for example, and if that was the question, I'd happily call out Oracle). In this particular case, it's annoying that SQL Server doesn't conform to the standard because virtually every other database does and SQL Server being the lone holdout means that you can't concatenate strings consistently across databases. – Justin Cave Jul 09 '12 at 23:04
  • Not to mention NULL being equivalent to the empty string in Oracle. – DCookie Jul 10 '12 at 01:15
  • As much as I like MySQL, you'll have to cough at it as well: the `||` operator in MySQL performs a logical `OR` on the two operands. `'foo' || 'bar'` yields '0'. Surprise! – Christopher Schultz Mar 19 '15 at 20:11
3

'concat' function can be operated only on 2 variables or columns, while 'concat' operation can be done for any number of variables or columns.

rahul
  • 47
  • 1