The trick to any jigsaw puzzle is to have a clear image of what the finished puzzle will look like. Once you have that, you can work your way backward to find the right pieces for that section. The same goes for our data. If we know what we want it to display or look like, then we know what we are looking for in the available data. And should that format not be available, we know what pieces we have and can put it together.
Let’s take a person’s name, for instance. To facilitate using the first name as part of your greeting, more and more people save the first and last name as different fields. But what if you need to display the whole name? What we need is a way to combine data from multiple fields into one.
Concatenation is the answer. If you are comfortable with Google sheets or Excel, you may have already come across the concatenate function. (If not, check out the tutorials linked above.) This is probably one of the most used functions in my non-numeric spreadsheets. (It’s like text to columns but in reverse). You can use it to string things together from different fields or user input to create a value for your new field. I use it all the time to append something to the front or back of my string.
AMPscript also has a concatenate function. It is called Concat(). It lets you string as many pieces together as you need. So let’s take a look at how it works:
DEFINITION OF PARTS —
1= This is the 1st string.
2= This is the 2nd string.
The strings can be passed as [attribute value], @variable, or in “string format”. There is no limit to how many strings you have within the AMPscript Concat() function.
Concat(‘a’,’b’,’c’) –> abc
Concat(‘a’,’ ‘,’b’) –> a b
Concat(‘a’,’. ‘,’b’,’. ‘,’c’,’. ‘) –> a. b. c.
Advanced Example 1 Full Name – AMPscript Block:
%%[set @firstname = 'Jane' set @lastname = 'Doe'
set @fullname1 = concat(@firstname,@lastname) set @fullname2 = concat(@firstname, ' ', @lastname)
]%%If you just string the two values together you get:
%%=v(@fullname1)=%%If you add a string with a space between them you get:
%%=v(@fullname2)=%%-- RESULT: If you just string the two values together you get: JaneDoe If you add a string with a space between them you get: Jane Doe
Remember, Concat() will take whatever you give it and mash it together. If you want spaces or punctuation, you will need to add that in as a string within your concat statement.
Advanced Example 1 Address – AMPscript Block:
%%[set @street1 = [street1] set @street2 = [street2] set @city = [city] set @state = [state] set @postalcode = [postalcode] set @country = [country] /* Using an if statement to check if there is a street2 value for punctuation*/ if not empty(@street2) then set @fullstreet= concat(@street1,", ", @street2) else then set @fullstreet= @street1 endif /* Using an if statement to check if there is a state value for punctuation*/ if not empty(@state) then set @fullcitystate= concat(@city,", ", @state) else then set @fullstreet= @city endif /* Using an if statement to only display country for non-US addresses*/ if @country=="US" or @country=="USA" or @country=="United States" or empty(@country) then set @fulladdress = concat(@fullstreet,", ", @fullcitystate, " ", @postalcode) else then set @fulladdress = concat(@fullstreet,", ", @fullcitystate, " ", @country, " ", @postalcode) endif