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.

Basic Usage

String literal reference example:

derive type:single value:MERGE(['Hello,','World'],' ') as:'Hi'

Output: Generates a new column called Hi with the value Hello, World.

Column reference example:

derive type:single value:MERGE([string1,string2]) as:'merged_strings'

Output: Generates a new merged_strings column containing a single String value that is the merge of string1 and string2 values.

Syntax

derive type:single value:MERGE([string_ref1,string_ref2],'string_delim')

ArgumentRequired?Data TypeDescription
string_ref1YstringName of first column or first string literal to apply to the function
string_ref2YstringName of second column or second string literal to apply to the function
string_delimNstringOptional 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 TypeExample Value
YesString literal or column referencemyString1, myString2

string_delim

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

Usage Notes:

Required?Data TypeExample Value
NoString literal'-'

Examples

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:

FirstNameLastNameMiddleInitial
JackKerouacL
PaulTherouxE
J.D.Salinger
PhilipDickK

Transform:

derive type:single value:MERGE([FirstName,MiddleInitial,LastName],' ') as:'FullName'

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

replace col:FullName with:' ' on:' '

Results:

FirstNameLastNameMiddleInitialFullName
JackKerouacLJack L Kerouac
PaulTherouxEPaul E Theroux
J.D.Salinger J.D. Salinger
PhilipDickKPhilip 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.

Was this page helpful? Let us know how we did:

Send feedback about...

Google Cloud Dataprep Documentation