What is the difference between "||" operator and concat function in Oracle?

Question

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?

Blumenthal

Answer 1

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.

Nasion

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

Oh come on SQL server has an equivalent operator + which is just like oracle's partial support of ANSI standard functions CHARACTER_LENGTH, OCTET_LENGTH , SUBSTRING or POSITION functions. – Lheureux

@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. – Nasion

Not to mention NULL being equivalent to the empty string in Oracle. – Compassionate

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! – Punner

Answer 2

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

Sulphonate

On Oracle, this is true. – Dust