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
Astuce
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 | |
---|---|
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 | |
---|---|
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.