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:

Figure 1. Result of a query that uses the concatenation operator to show the last name and first initial of employees
   COL1
   ----------
   MONTEZR
   GASPARDP

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:

Figure 2. Result of a query that uses the concatenation operator to show the first initial and last name in a more readable format
   COL1
   -----------
   R. FROMMHERZ