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® 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
Dameng®	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