String concatenation operators in SQL

The || operator is the standard to concatenate strings.

The ANSI SQL standards define the double-pipe as string concatenation operator, for example:
SELECT city_id || '/' || city_name FROM customer
Produces the following result:
456/Paris
1234/London

However, some database engine types do not support the standard double-pipe concatenation operator, or need some configuration setting to allow its use.

Table 1. Database servers support of double-pipe concatenation operator
Database Server Type Double pipe operator
IBM® DB2® LUW Yes
IBM Informix® Yes
Microsoft™ SQL Server No, see details
Oracle® MySQL / MariadDB Can be enabled, see details
Oracle Database Server Yes
PostgreSQL Yes
SAP HANA® Yes
SQLite Yes
If needed (typically, with Microsoft SQL Server), ODI drivers will convert the || double-pipe operator to the native concatenation operator. However, for best performances, consider disabling Informix emulation and implement a FUNCTION that returns the DB-engine specific concatenation operator, depending on the target database type:
FUNCTION sql_concat_oper() RETURNS STRING
    IF fgl_db_driver_type() IN ("snc","esm","ftm") THEN
        RETURN "+"
    ELSE
        RETURN "||"
    END IF
END FUNCTION