Skip to main content

CONCAT

CONCAT(string1, string2, ...) combines two or more strings into a single string.

Syntax and Parameters​

CONCAT(string1, string2[, string3, ...])

  • Parameters - String expressions (at least two required):
    • Can be literal strings, column references, or expressions that result in strings
    • NULL values are treated as empty strings
    • Numbers are automatically converted to strings
    • No limit to number of parameters

Basic Usage​

Simple string combination examples:

CONCAT('Hello', ' ', 'World') -- Returns "Hello World"
CONCAT('ID-', '12345') -- Returns "ID-12345"
CONCAT('Price: $', 19.99) -- Returns "Price: $19.99"

Common Use Cases​

Creating supplier identifiers:

CONCAT('SUP_', "S_SUPPKEY", '_', "S_NATIONKEY")

Formatting contact information:

CONCAT("SUPPLIER".S_NAME, ' (', "SUPPLIER".S_PHONE, ')')

Building full supplier details:

CONCAT(
    "SUPPLIER".S_NAME, ' - ',
    "SUPPLIER".S_ADDRESS, ' - ',
    'Account Balance: ', "SUPPLIER".S_ACCTBAL
)

Using CONCAT in Coalesce​

When using CONCAT in transforms and bulk editing, you can use Helper Tokens:

CONCAT({{TGT}}, ' - Modified')
CONCAT('New - ', {{SRC_COL}})

The Helper Tokens automatically get replaced:

  • {{SRC}} becomes the fully qualified source Node and column name
  • {{SRC_COL}} becomes just the source column name

Examples using Helper Tokens:

-- Adding a prefix
CONCAT('ID-', {{SRC}})

-- Combining multiple columns
CONCAT(
    {{SRC}}, ' (',
    {{ ref('WORK', 'CUSTOMER') }}.email, ')'
)