This is an old revision of the document!
The concatenation operator - || (two vertical bars) - returns a single concatenated character string made up of two or more character expressions. A concatenation operator on two character strings (CHARACTER_DATATYPE_COLUMN1 || CHARACTER_DATATYPE_COLUMN2) will return a CHARACTER string; everything else will return a C STRING.
All blanks are preserved during concatenation. Use the TRIM function to eliminate blanks if desired.
character_expression || character_expression [ || character_expression ...]
> SELECT 'Individual: ' || individual || ' Name: ' || name first_line FROM individuals; FIRST_LINE ----------------------------------------------------------------------------- Individual: 000900011022 Name: MS. MARY LEWIS Individual: 000900047761 Name: MRS. ALICE R TOLLIVER Individual: 000900047762 Name: MR. SIDNEY K TOLLIVER Individual: 000900067115 Name: MRS. JENNIFER J HOPKINS Individual: 000900067114 Name: MR. CHARLIE HOPKINS Individual: 000900215567 Name: MS. KIMBERLY BLUE Individual: 000900240828 Name: KATHLEEN TUCKER
SELECT PRODUCTS.PRODUCT_NO || ' is the product number for ' || PRODUCTS.DESCRIPTION FROM products
The following complex example creates a string containing the name and an optional email.
> SELECT (((TRIM($proper(NAME)) || >> CASE >> WHEN EMAIL > ' ' THEN ' (' >> ELSE '' >> END) || >> TRIM(EMAIL)) || >> CASE >> WHEN EMAIL > ' ' THEN ')' >> ELSE '' >> END) name_and_email >> FROM INDIVIDUALS; NAME_AND_EMAIL ----------------------------------------------------------------------------- Ms. Mary Lewis Mrs. Alice R Tolliver (alicetolliver@securitasinc.com) Mr. Sidney K Tolliver (sidneytolliver@deere.com) Mrs. Jennifer J Hopkins Mr. Charlie Hopkins Ms. Kimberly Blue Kathleen Tucker