Preface
The Transformation Language
Constants
Operators
Variables
Dates
Functions
- Function Categories
  - Aggregate Functions
  - Aggregate Functions and Nulls
    - Filter Conditions
  - Binary Functions
  - Character Functions
  - Conversion Functions
  - Data Cleansing Functions
  - Date Functions
  - Encoding Functions
  - Financial Functions
  - Numeric Functions
  - Scientific Functions
  - Special Functions
  - String Functions
  - Test Functions
  - Variable Functions
  - Window Functions
- ABORT
- ABS
- ADD_TO_DATE
- AES_DECRYPT
- AES_ENCRYPT
- ASCII
- AVG
- BINARY_COMPARE
- BINARY_CONCAT
- BINARY_LENGTH
- BINARY_SECTION
- CEIL
- CHOOSE
- CHR
- CHRCODE
- COMPRESS
- CONCAT
- CONVERT_BASE
- COS
- COSH
- COUNT
- CRC32
- CUME
- DATE_COMPARE
- DATE_DIFF
- DEC_BASE64
- DEC_HEX
- DECODE
- DECOMPRESS
- EBCDIC_ISO88591
- ENC_BASE64
- ENC_HEX
- ERROR
- EXP
- FIRST
- FLOOR
- FV
- GET_DATE_PART
- GREATEST
- IIF
- IN
- INDEXOF
- INITCAP
- INSTR
- ISNULL
- IS_DATE
- IS_NUMBER
- IS_SPACES
- LAG
- LAST
- LAST_DAY
- LEAD
- LEAST
- LENGTH
- LN
- LOG
- LOOKUP
- LOWER
- LPAD
- LTRIM
- MAKE_DATE_TIME
- MAX (Dates)
- MAX (Numbers)
- MAX (String)
- MD5
- MEDIAN
- METAPHONE
- MIN (Dates)
- MIN (Numbers)
- MIN (String)
- MOD
- MOVINGAVG
- MOVINGSUM
- NPER
- PERCENTILE
- PMT
- POWER
- PV
- RAND
- RATE
- REG_EXTRACT
- REG_MATCH
- REG_REPLACE
- REPLACECHR
- REPLACESTR
- REVERSE
- ROUND (Dates)
- ROUND (Numbers)
- RPAD
- RTRIM
- SETCOUNTVARIABLE
- SET_DATE_PART
- SETMAXVARIABLE
- SETMINVARIABLE
- SETVARIABLE
- SHA256
- SIGN
- SIN
- SINH
- SOUNDEX
- SQRT
- STDDEV
- SUBSTR
- SUM
- SYSTIMESTAMP
- TAN
- TANH
- TIME_RANGE
- TO_BIGINT
- TO_CHAR (Dates)
- TO_CHAR (Numbers)
- TO_DATE
- TO_DECIMAL
- TO_FLOAT
- TO_INTEGER
- TRUNC (Dates)
- TRUNC (Numbers)
- UPPER
- VARIANCE
Creating Custom Functions
Custom Function API Reference

Transformation Language Reference

10.5.1
- 10.5.6
- 10.5
- 10.4.0

Back Next

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

Rename Saved Search

Table of Contents

Transformation Language Reference

Transformation Language Reference

CONCAT

CONCAT

Syntax

Return Value

Nulls

Example