B.3.3 Concatenating a String Value with a NULL value

Oracle database

SELECT SUBSTR( col1 || col2,
               2,
               5)
  FROM t1;

Note: col1 and col2 are assumed to be character string type, and col2 may contain NULL

Fujitsu Enterprise Postgres

SELECT SUBSTR( col1 || NVL(col2, '')
               2,
               5)
  FROM t1;

Note: col1 and col2 are assumed to be character string type, and col2 may contain NULL

Feature differences

Oracle database: NULL is handled as an empty string, and strings are joined.

Fujitsu Enterprise Postgres: NULL is not handled as an empty string, and the result of joining the strings becomes NULL.

Conversion procedure

Convert using the following procedure:

Locate the places where the keyword "||" is used.
Check if any of the value expressions can contain NULL - if they can, then execute step 3.
Modify to NVL(valExpr,'').