Skip to main content

MERGE Function

Merges two or more columns of String type to generate output of String type. Optionally, you can insert a delimiter between the merged values.

Note

This function behaves exactly like the merge transform, although the syntax is different. See Merge Transform.

Wrangle vs. SQL: This function is part of Wrangle, a proprietary data transformation language. Wrangle is not SQL. For more information, see Wrangle Language.

Basic Usage

String literal reference example:

merge(['Hello,','World'],' ')

Output: Returnsthe value Hello, World.

Column reference example:

merge([string1,string2])

Output: Returns a single String value that is the merge of string1 and string2 values.

Syntax and Arguments

merge([string_ref1,string_ref2],'string_delim')

Argument

Required?

Data Type

Description

string_ref1

Y

string

Name of first column or first string literal to apply to the function

string_ref2

Y

string

Name of second column or second string literal to apply to the function

string_delim

N

string

Optional delimiter string to insert between column or literal values

For more information on syntax standards, see Language Documentation Syntax Notes.

string_ref1, string_ref2

String literal or name of the string column whose elements you want to merge together. Youcan mergetogether two or more strings.

Usage Notes:

Required?

Data Type

Example Value

Yes

String literal or column reference

myString1, myString2

string_delim

Optional string literal to insert between each string that is being merged.

Usage Notes:

Required?

Data Type

Example Value

No

String literal

'-'

Examples

Tip

For additional examples, see Common Tasks.

Example - Simple merge example

The following example contains the names of a set of American authors. You need to bring together these column values into a new column, called FullName.

Source:

FirstName

LastName

MiddleInitial

Jack

Kerouac

L

Paul

Theroux

E

J.D.

Salinger

Philip

Dick

K

Transformation:

Transformation Name

New formula

Parameter: Formula type

Single row formula

Parameter: Formula

merge([FirstName,MiddleInitial,LastName],' ')

Parameter: New column name

'FullName'

Since the entry for J.D. Salinger has no middle name, you might want to add the following transformation:

Transformation Name

Replace text or pattern

Parameter: Column

FullName

Parameter: Find

' '

Parameter: Replace with

''

Results:

FirstName

LastName

MiddleInitial

FullName

Jack

Kerouac

L

Jack L Kerouac

Paul

Theroux

E

Paul E Theroux

J.D.

Salinger

J.D. Salinger

Philip

Dick

K

Philip K DIck

Other Examples

While the syntax may be different, the MERGE function behaves exactly like the merge transform. For more examples, see Merge Transform.