If we have a table: ACCOUNTS_PAYABLE_TBL

Return VNDR_ID, INVN_ID, AMT_DUE, and then place ‘–> ‘ ahead of the NOTES and ‘ <–’ behind the NOTES column.

          SELECT VNDR_ID,
                 INVN_ID,
                 AMT_DUE,
                 '--> ' || NOTES || ' <--'
            FROM ACCOUNTS_PAYABLE_TBL;

Result Table:

 The trailing constant ‘ <–’ “floated” to the left behind the NOTES value
because the NOTES column is defined as VARCHAR.

If we have a table: VENDOR_TBL

If you concatenate the STATE and the COUNTRY columns, you get the following results:

          SELECT STATE || COUNTRY
            FROM VENDOR_TBL;

Normally-defined character values are padded with spaces to the
length of the column definition.
The concatenation of multiple character columns does not add or
eliminate trailing spaces.
If the column is defined as variable (VARCHAR), then the length
is determined by the inserted value which may or may not
include leading or trailing spaces.

Result Table:

If we have a table: VENDOR_TBL

The boss wants a list of vendor IDs (VNDR_ID) and
VENDOR_NAMEs from the VENDOR_TBL. But instead of
V001, V002, … he wants VENDOR001, VENDOR002, etc.

SELECT ‘VENDOR’
|| SUBSTRING(VNDR_ID, 2, 3),
VENDOR_NAME
FROM VENDOR_TBL;

The SUBSTRING function converts V001, V002, … to 001, 002, …
(substring from position 2 for a length of 3). Then the || (concatenation) adds ‘VENDOR’ to the value making ‘VENDOR001′, ‘VENDOR002′, etc. See SUBSTRING.

Result Table: