This article explains how to use delimited strings to store more data within a single Data Extension record with AMPscript in Salesforce Marketing Cloud.

Not a fan of reading? Jump to the code snippet.

What’s a delimited string?

A delimited string is a chain of characters where different pieces of information are separated by one special character.

This character can be anything, like a comma or a dot, but it’s always better to use a unique character that doesn’t occur in a regular piece of text, such as the pipe character “|”.

Lorem ipsum dolor sit amet|Consectetur adipiscing elit|Sed hendrerit 
tempor arcu e t pharetra|Suspendisse potenti.

To simplify the following examples, let’s assume this string has already been retrieved from a record in a Data Extension and contains 4 different pieces of data.

FirstName|LastName|EmailAddress|Country

Why should we use delimited strings?

When it comes to Marketing Cloud, there are several reasons to use the delimited strings method:

1. When the data model fits our needs: HTML Form that gathers the data has a multiple select field or a range of checkboxes that populate same the field.
2. Better performance: fewer fields in a Data Extension means better read and write speeds.
3. External systems integration: sending a single piece of data from an external endpoint is very convenient.

Loop through delimited strings

Storing the data in a record is easy, but how can we possibly display each piece of data separately?

Simple delimited string

When storing a single delimited string in a record, retrieving each piece of data is a breeze, thanks to the BuildRowsetFromString function.

When used, it breaks the string into an Array, that we can loop through with a simple For loop as if it was a regular record set retrieved with the Lookup function.

<table>
    <tr>
        %%[

        SET @String = "John|Malkovitch|john.malkovitch@mail.com|USA"
        SET @StringRows = BuildRowsetFromString(@String,'|')

        FOR @i = 1 to ROWCOUNT(@StringRows) DO

            SET @Record = Field(ROW(@StringRows, @i),1)

            ]%%<td>%%=v(@Record)=%%</td>%%[

        NEXT @i

        ]%%
    </tr>
</table>

Complex delimited string

Now, let’s imagine that there are 2 rows of data stored in a delimited string and each row has 4 pieces of data.

John|Malkovitch|john.malkovitch@mail.com|USA|Jacques|Cousteau|jacques.cousteau@free.fr
|France

This means that we should first break the string at every 4th occurrence of the “|” character and only then display each piece individually.

The method below showcases how to populate a table with the data coming from a delimited string with multiple data rows.

<table>
%%[
SET @String = "John|Malkovitch|john.malkovitch@mail.com|USA|Jacques|Cousteau|jacques.cousteau@free.fr|France"
SET @Inc = 0
SET @MaxLength = 4

SET @StringRows = BuildRowsetFromString(@String,'|')

FOR @i = 1 to ROWCOUNT(@StringRows) DO

    SET @StringSet = CONCAT(@StringSet,Field(ROW(@StringRows, @i),1),"|")
    SET @Inc = ADD(@Inc,1)

    IF @Inc == @MaxLength THEN 

        SET @StringSetRows = BuildRowsetFromString(@StringSet,'|')

        ]%%<tr>%%[

            FOR @j = 1 to SUBTRACT(ROWCOUNT(@StringSetRows),1) DO

                SET @Record = Field(ROW(@StringSetRows, @j),1))

                ]%%<td>%%=v(@Record)=%%</td>%%[

            NEXT @j

        ]%%</tr>%%[

        SET @StringSet = ""
        SET @Inc = 0

    ENDIF
  
NEXT @i
]%%
</table>

Try it!

Copy/paste the code in a Cloud page and try it out!

Have I missed anything?

Please poke me with a sharp comment below or use the contact form.