Concatenate

Concatenation allows you to combine two or more strings (text fields or user-defined text) into a single value. This is useful for generating new fields, e.g. Full Name from First Name & Last Name, or creating combined values to act as a unique lookup key.

Some tools like Excel, OrgVue and Python have two types of concatenation: a + b style syntax and concat(a,b) style syntax. Both options are listed below.

NB. Using the join() method in OrgVue you can specify a delimiter to use between each string to be concatenated. In all other methods, if you want to have a delimiter between combined strings you need to add it separately as many times as needed.

Excel

In Excel you have two options for combining strings: Option 1: & is the quickest if you only have 2 values to combine =‹value1›&‹value2›

=A1&A2

Option 2: CONCATENATE() is better for long chains =~CONCATENATE!~(‹value1›, ‹value2›, ‹value›)

=CONCATENATE(A1," ",C1)

Tableau

‹string1› + ‹string2›

[First Name] + [Last Name]

NB. Both arguments must be strings. To convert a different data type to a string, use ~STR!~(‹field›). Go to General > Convert Types for more information.

Alteryx

‹string1› + ‹string2›

[First Name] + [Last Name]

NB. Both arguments must be strings. To convert a different data type to a string, use ~TOSTRING!~(‹field›) or the Select tool (in the Preparation palette). Go to General > Convert Types for more information.

OrgVue

In OrgVue you have three options for combining strings:

Option 1: format() is the simplest and most efficient node.~format!~('{‹string1›}«delimiter»{‹string2›}')

node.format('{firstname} {lastname}')

NB. Fields in the dataset (firstname and lastname in this example) are referenced inside curly braces {}. If you want to join a value of the node with user-specified text, don't surround that string with the curly braces, e.g. node.format('My name is {firstname}').

Option 2: join() is more flexible because it allows you to include conditional statements and references to a node's relatives [‹string1›,‹string2›].~join!~("«delimiter»")

[node.role, node.grade > 4 ? " (Senior Mgr)" : ""].join("")

Option 3: concat() is the most long-winded ‹string1›.value.~concat!~(«delimiter»).~concat!~(‹string2›)

node.firstname.value.concat(" ").concat(node.lastname)

tSQL

In SQL you have two main options for combining strings:

Option 1: + is the quickest if you only have a few values to combine ‹field1› +' '+ ‹field2› ‹field1› +' '+ ‹string1›

SELECT FirstName+' '+LastName AS FullName
FROM EmployeesData
-- OR
'OLD'+RecordID

Option 2: CONCAT() is better for long chains ~CONCAT!~(‹string1›, ‹string2›«, ‹stringN»)

SELECT CONCAT(Address, City, PostCode) AS Location 
FROM EmployeesData

NB.:

• CONCAT is only available with SQL2012 onwards

• When concatenating, both ‹fields› (or ‹field› and ‹string›) must be of the same data type.

Python

In Python you have two main options for combining strings: Option 1: + is the quickest if you only have a few values to combine ‹string1› + ‹string2›

firstName + lastName

Option 2: join() "".~join!~(‹string1›, ‹string2›)

"".join([firstName,  lastName])
# OR
"".join(["John", " Cleese"])
# Returns "John Cleese"

NB.:

• The above example assumes variables have been declared for firstName and lastName. For more information, go to General > Variables.
• To join the same string together multiple times, you can use the mathematical operator *, e.g. ‹string1›*3 returns ‹string1string1string1›:

location = "Camelot"

location*3 
# Returns "CamelotCamelotCamelot"