CONCAT
Concatenates two strings. CONCAT converts all data to text before concatenating the strings. Alternatively, use the || string operator to concatenate strings. Using the || string operator instead of CONCAT improves Data Integration Service performance.
Syntax
CONCAT( first_string, second_string )
The following table describes the arguments for this command:
Argument | Required/ Optional | Description |
|---|
first_string | Required | Any datatype except Binary. The first part of the string you want to concatenate. You can enter any valid transformation expression. |
second_string | Required | Any datatype except Binary. The second part of the string you want to concatenate. You can enter any valid transformation expression. |
Return Value
String.
NULL if both string values are NULL.
Nulls
If one of the strings is NULL, CONCAT ignores it and returns the other string.
If both strings are NULL, CONCAT returns NULL.
Example
The following expression concatenates the names in the FIRST_NAME and LAST_NAME ports:
CONCAT( FIRST_NAME, LAST_NAME )
FIRST_NAME | LAST_NAME | RETURN VALUE |
|---|
John | Baer | JohnBaer |
NULL | Campbell | Campbell |
Bobbi | Apperley | BobbiApperley |
Jason | Wood | JasonWood |
Dan | Covington | DanCovington |
Greg | NULL | Greg |
NULL | NULL | NULL |
100 | 200 | 100200 |
CONCAT does not add spaces to separate strings. If you want to add a space between two strings, you can write an expression with two nested CONCAT functions. For example, the following expression first concatenates a space on the end of the first name and then concatenates the last name:
CONCAT( CONCAT( FIRST_NAME, ' ' ), LAST_NAME )
FIRST_NAME | LAST_NAME | RETURN VALUE |
|---|
John | Baer | John Baer |
NULL | Campbell | Campbell (includes leading blank) |
Bobbi | Apperley | Bobbi Apperley |
Jason | Wood | Jason Wood |
Dan | Covington | Dan Covington |
Greg | NULL | Greg |
NULL | NULL | NULL |
Use the CHR and CONCAT functions to concatenate a single quote onto a string. The single quote is the only character you cannot use inside a string literal. Consider the following example:
CONCAT( 'Joan', CONCAT( CHR(39), 's car' ))
The return value is:
Joan's car