Concatenate
Combine text from the source field with other text by adding at the beginning or the end text from another field, a value or an expression, using an optional Separator string of characters. The Separator string is added only if both values are not empty.
- end - Add specified text, preceded by any text in the Separator box, to the beginning of the source text.
- start - Add specified text, followed by any text in the Separator box, to the beginning of the source text.
The text in the Separator box is often just a single character, such as a space character, but it could be a string of more than one character. It could be a comma character followed by a space, as often is used when concatenating field values together when building a list of field values separated by a comma.
For example, given a Value of The and a Separator that is just the space character, a source text of Netherlands is transformed into The Netherlands. Given a Value of The and a Separator box containing Republic of with space characters at the beginning and end, we transform Ireland to The Republic of Ireland.
Because the separator string is added only when both texts (the original field, and that specified) are not empty, that makes it easy to build sequences of comma separated items. For example, if we concatenate a field to the starting field using a Separator of ', ' (a comma character and a space character), we can safely do the concatenation even if the starting field is empty, because in that case the ', ' will not be added to start the list.
For example, if the starting text is John and we concatenate Paul using a Separator of ', ' (a comma character and a space character), the result will be John, Paul.
If the starting text is empty and we concatenate Paul using a Separator of ', ' (a comma character and a space character), the result will be Paul - it will not be , Paul with an unwanted comma character and space character preceding Paul.
Launch the template by choosing a text field and then double-clicking the Concatenate template. When the template launches we can specify options.
Concatenate : end
Add specified text, preceded by any text in the Separator box, to the beginning of the source text.
We will use an example table with three fields, all of which are nvarchar text fields. We will concatenate the contents of the Name field to the end of the Status field and put the result into the Combined field.
With the focus on the table, in the Transform pane we choose the Status field, and then we double-click the Concatenate template to launch it.
In the Concatenate template we choose end as the Add at option. We choose the Name field as the Value which we will add to the end of the Status field.
For a Separator we enter a hyphen - character, followed by a space character, followed by Name, and then followed by a colon : character followed by a space character. The trailing space character is not visible in the illustration above.
For the Result destination, we choose the Combined field.
Press Transform.
The system immediately fills the Combined field with the results of concatenating the Status field to the start of the Name field, with a separator sequence of a colon : character followed by a space character in between.
Where there is a NULL in the Status field, the Combined field is filled with a NULL as well, because the target field to which something is being concatenated is also NULL.
See the Example: Construct JSON String using Select and Transform topic for an example of Concatenate in action.
Concatenate : start
Add specified text, followed by any text in the Separator box, to the beginning of the source text.
We will use an example table with three fields, all of which are nvarchar text fields. We will concatenate the contents of the Status field to the start of the Name field and put the result into the Combined field.
With the focus on the table, in the Transform pane we choose the Name field, and then we double-click the Concatenate template to launch it.
In the Concatenate template we choose start as the Add at option. We choose the Status field as the Value which we will add to the start of the Name field.
For a Separator we enter a colon : character followed by a space character. The space character is not visible in the illustration above.
For the Result destination, we choose the Combined field.
Press Transform.
The system immediately fills the Combined field with the results of concatenating the Status field to the start of the Name field, with a separator sequence of a colon : character followed by a space character in between.
Where there is a NULL in the Status field, the Combined field is filled with the Name field contents alone, with no separator sequence of characters used. Note how that is different from the analogous situation in the example for the Concatenate : end operation, where if the target field is NULL the result is NULL as well.
See the Example: Construct JSON String using Select and Transform topic for an example of Concatenate in action.