What is the meaning of the sign || '-' || in SQL

Question

I am performing a query in an Oracle database and come across this symbology || '-' ||, in my query. I’d like to know the meaning?

Select
  xf0cdloc || '-' ||  XN4CDEMP AS LOCOMOTIVA,
From
  Trem

Answer 1

In Oracle and Postgres the two Pipes (||) are the concatenation operator.

Mysql by default does not use an operator but a function, Concat() however it is possible to exchange it for the changed Pipes the variable value sql_mode for PIPES_AS_CONCAT

SELECT 'teste' || 'algo'

Returns a column by concatenating the two values.

Documentation - Oracle

Documentation - Postgres

Documentation - Mysql

Answer 2

What your code is doing is selecting two columns from the table Trem, to xf0cdloc and the XN4CDEMP, and is building a virtual column that will only exist in this query. This virtual column calls LOCOMOTIVA and its content is the concatenation of the content of xf0cdloc then a dash ('-') and then already pastes the content of XN4CDEMP.

The || is the concatenation operator. It is defined in the standard SQL specification. But some database systems do not implement it, so this syntax may not work depending on where you use it. Because of this some people recommend using the function CONCAT() when you want the code to be portable to other systems.

In other systems or other languages would be the equivalent of doing this:

Select
  xf0cdloc + '-' + XN4CDEMP AS LOCOMOTIVA,
From
  Trem

I put in the Github for future reference.

Answer 3

Server For Concaternar String, in Oracle it is possible to concaterate string with number, however in SQL SERVER it is necessary to convert number to string