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.

In this section: