In the SELECT clause of the following SQL statement, the concatenation operator is used with the SUBSTR scalar function to join the first character of FIRSTNAME with LASTNAME. When you run this query:
SELECT LASTNAME||SUBSTR(FIRSTNAME,1,1) FROM Q.INTERVIEW WHERE MANAGER = 140
QMF produces this report:
+--------------------------------------------------------------------------------+ | COL1 | | ---------- | | MONTEZR | | GASPARDP | +--------------------------------------------------------------------------------+
There is no space between the last name and the initial, because none was provided for when concatenation was done. This is true because the data types for the columns FIRSTNAME and LASTNAME are VARCHAR.
The next example concatenates a substring of the first name with a period and a space, and then with the last name. When you run this SQL statement:
SELECT SUBSTR(FIRSTNAME,1,1)||'. '||LASTNAME FROM Q.INTERVIEW WHERE TEMPID = 400
QMF produces this report:
+--------------------------------------------------------------------------------+ | COL1 | | ----------- | | R. FROMMHERZ | +--------------------------------------------------------------------------------+