The concatenation operator - || (two vertical bars) - returns a single concatenated character string made up of two or more character expressions. All blanks are preserved during concatenation. Use the TRIM function to eliminate blanks if desired.
A concatenation operator on two character strings (CHARACTER_DATATYPE_COLUMN1 || CHARACTER_DATATYPE_COLUMN2) will return a CHARACTER string; everything else will return a STRING.
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
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
See also: