The concatenation operator
Use the concatenation operator (||) to join two values of an expression into a single string. In some non-English, single-byte character sets, the || can display as !! (exclamation marks) or other special characters.
Rules for concatenation
The following rules apply to using the concatenation operator:
- The operands that you concatenate must be either all character strings or all graphic strings.
- The length of the result is the sum of the lengths of the operands.
- The data type of the result is:
- VARCHAR when all operands are CHAR and their combined length is greater than 255, or when one or more operands is VARCHAR.
- VARGRAPHIC when all operands are GRAPHIC and their combined length is greater than 255, or when one or more operands is VARGRAPHIC.
- If either operand is null, the result is a null value. (To avoid null values, use the VALUE scalar function.)
- You cannot use concatenation in a LIKE clause.
- You cannot use concatenation in the SET clause of an UPDATE statement.
Examples that use concatenation
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.
SELECT LASTNAME||SUBSTR(FIRSTNAME,1,1)
FROM Q.INTERVIEW
WHERE MANAGER = 140
When you run this statement, QMF produces the report that is shown in the following figure:
There is no space between the last name and the initial because the data types for the columns FIRSTNAME and LASTNAME are defined as VARCHAR rather than CHAR.
The next example concatenates a substring of the first name with a period and a space, and then with the last name:
SELECT SUBSTR(FIRSTNAME,1,1)||'. '||LASTNAME
FROM Q.INTERVIEW
WHERE TEMPID = 400
When you run this statement, QMF produces the report that is shown in the following figure: