MERGE Function

Contents:

On January 27, 2021, Google is changing the required permissions for attaching IAM roles to service accounts. If you are using IAM roles for your Google service accounts, please see Changes to User Management.

Contents:

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. You can merge together 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.

See Also:

Send Feedback

Page tree