Table of Contents

Search

  1. Preface
  2. The Transformation Language
  3. Constants
  4. Operators
  5. Dates
  6. Functions
  7. Creating Custom Functions
  8. Custom Function API Reference
  9. POWERCENTERHELP

Transformation Language Reference

Transformation Language Reference

CONCAT

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
PowerCenter 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