CONCAT function

This topic describes the formula syntax and usage of the CONCAT function in the Data Quality Execute Formula transform.

Description

Joins several text items into one text item.

Syntax

CONCAT(text1,text2,...)

The CONCAT function syntax has the following arguments:

text1 Required. A field identifier, literal string number, or formula. Numeric values are converted to text.
text2,... Optional. A field identifier, literal string number, or formula.

Notes

Enclose literal strings between quotation marks. Numbers do not require quotation marks
To insert a space in a string, use double quotation marks with a space between them (" ").
Use "" to insert a double quotation mark within a literal string.
This function does not ignore empty elements.

Example

Use the following formula to assemble the full name from its parts:

CONCAT([First]," ",[Middle]," ",[Last1]," ",[Last2])


First	Middle	Last1	Last2	Result
`Victoria`	`Luisa`	`Pineda`	`Romero`	`Victoria Luisa Pineda Romero`
`Marco`	`Antonio`	`Manzanares`	`Solis`	`Marco Antonio Manzanares Solis`
`Maria`	`Lourdes`	`Seren`	`Vasquez`	`Maria Lourdes Seren Vasquez`

Typically, the middle name is optional and the second last name is either omitted or not used in a region, so we can use a formula to omit the space before missing values:

=CONCAT([First],IF(ISBLANK([Middle]),," "),[Middle]," ",[Last1],IF(ISBLANK([Last2]),," "),[Last2])

Table 1. Data
First	Middle	Last1	Last1	Result
`Victoria`	`Luisa`	`Pineda`		`Victoria Luisa Pineda`
`Marco`	`Antonio`	`Manzanares`	`Solis`	`Marco Antonio Manzanares Solis`
`Maria`		`Seren`	`Vasquez`	`Maria Seren Vasquez`