Overview
In this how-to article, we are describing how to use DataBridge Transformation to split an input column into multiple output columns.
...
Create a new profile or edit an existing profile by clicking Manage profile. On the Transform page, select the Transformation you want, in this case, Split and click Add
In the Split transformation the settings to choose are:
...
Output field name: The name of the output field
SeperatorSeparator: The separator consists of any character or sequence of characters. It can also contain special characters, but since all of them cannot be typed into a textbox, you can use the following syntax to include them:
...
You can also select segments starting from the end. In that case, the last segment has index -1, the segment before that has index -2, etc.
Example:
Gabriel | José | de | la | Concordia | García | Márquez |
|---|---|---|---|---|---|---|
0 | 1 | 2 | 3 | 4 | 5 | 6 |
-7 | -6 | -5 | -4 | -3 | -2 | -1 |
First segment index | Last segment index | ||
|---|---|---|---|
First word | 0 | 0 | |
First three words | 0 | 2 | |
Last three words | -3 | -1 | |
First word to next to last word | 0 | -2 | |
Second word to last word | 1 | -1 | |
Last word | -1 | -1 |
In practice, if you need to split a full name into first name and last name, you have to think about the fact that many people have compound surnames, which means that the field will be split into more than two segments. You can decide that the first word will always be the first name, and all the rest shall be placed in the last name output field.
To do so, you have to create two split transformations. In both transformations, you set the separator as [:space:]. In the “first name” transformation, select segments from First to First and in the “last name” transformation, select segments from Second to Last. Voilà! All your contacts with a compound surname can now have their family name correctly registered in your system. In practice, if you need to split a full name into first name and last name, you have to think about the fact that many people have compound surnames, which means that the field will be split into more than two segments. You can decide that the first word will always be the first name, and all the rest shall be placed in the last name output field.
Examples for splitting person full name
Enter the values below to achieve the needed split functionality.
Split field transformation sample: Split person name in three separate SuperOffice fields, applicable when the middle field function is used in SuperOffice. You need to create three split transformations with the same separator but different selected segments.
Value in import or export column | Output Field Name |
|---|
Separator | Segments from | To | Ignore field if not splittable | Remark | ||
|---|---|---|---|---|---|---|
Pierre van Mever | First name | [:space:] | first (index 0) | first (index 0) | see details below | This will only put the first name in your output field |
Pierre van Mever | Middle name | [:space:] | second (index 1) | second (index 1) | see details below | This will only put the middle name in your output field |
Pierre van Mever | Last name | [:space:] | last (index -1) | last (index -1) | see details below | This will only put the last name in your output field |
Split field transformation sample: Split person name in two separate SuperOffice fields. You need to create two split transformations with the same separator but different selected segments.
Value in import or export column | Output Field Name |
|---|
Separator | Segments from | To | Ignore field if not splittable | Remark | ||
|---|---|---|---|---|---|---|
Pierre van Mever | First name | [:space:] | first (index 0) | first (index 0) | see details below | This will only put the first name in your output field |
Pierre van Mever | Last name | [:space:] | second (index 1) | last (index -1) | see details below | This will put the middle name and last name in your output field |
Example for splitting street address
Split field transformation sample: Split Street address in separate street name and house number. You need to create two split transformations with the same separator but different selected segments
Value in import or export column | Output Field Name |
|---|
Separator | Segments from | To | Ignore field if not splittable | Remark | ||
|---|---|---|---|---|---|---|
Jan de Groot laan 4 | Street name | [:space:] | first (index 0) | next to last (index -2) | see details below | The entire street name except the number will be in your output field |
Jan de Groot laan 4 | House number | [:space:] | last (index -1) | last (index -1) | see details below | This will only put the number in your output field |
Example for splitting date field
Split field transformation sample: Split date field in separate date and time field. In this case, a consultant did not want the time from the updatedDate field exported next to the date. Note: Although we now have a seperate separate option to select the date/time format of your choice when exporting (more info), we did want to show you this example.
...
Note that the date format is changed, this is because we have chosen a different format in the Destination Settingsof Settings of the profile. You can select the format that is suitable for you your system.
In your external system, you can use the new data fields without time.
...