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. 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 C 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: