Posted on by Josh Johnson

Parsing Delimited Strings in IMT

Delimited text is very common in the enterprise space. In fact, EDI – the “lingua franca” at TrueCommerce – is just delimited text at its core. I get asked often how to parse values out of a delimited string using IMT. In this post, I’ll break down how to build these statements.

Before you read any further, you should be familiar with the basics of BSP mapping.

The First Value

Let’s start with a simple pipe-delimited string:

[T:TOKEN] = Large|Blue|Style 2

To grab the first value, we need to copy characters starting from the beginning of the string and ending at the first pipe. We’ll use the COPY and INSTR functions to do just that:

@COPY([T:TOKEN],1,@NUM(@INSTR([T:TOKEN],|)-1))

Note that we are subtracting 1 from the result of our INSTR so the last character we copy is the character just before the pipe (and not the pipe itself).

The Second Value

Next, let’s take a look at that second value. We know we need to copy characters starting from the first pipe and stop once we’ve reached the second pipe. We can tweak our “first value” statement just a bit and use the INSTR to get our startIndex parameter, adding 1 to the result instead of subtracting 1 so we start copying one character after the first pipe:

@COPY([T:TOKEN],@NUM(@INSTR([T:TOKEN],|)+1),4)

This will work for our specific sample string, but we can’t expect that second value to always be 4 characters long. How can we write a statement that stops copying characters once we’ve reached the second pipe? If we tweak this statement a bit more, we can copy everything after the first pipe instead of just 4 characters. Then, we can use that result as the input to another COPY/INSTR statement. Once we’ve gotten rid of the first value and delimiter, the outer COPY/INSTR logic is the same as it was for the first value. Here is the full statement:

@COPY(@COPY([T:TOKEN],@NUM(@INSTR([T:TOKEN],|)+1),255),1,@NUM(@INSTR(@COPY([T:TOKEN],@NUM(@INSTR([T:TOKEN],|)+1),255),|)-1))

Notice that this statement looks exactly like our “first value” statement, but everywhere we used [T:TOKEN] in that “first value” statement, we used our initial “second value” statement – with one small change. Instead of copying just 4 characters, we’re copying 255 characters. There is no functional reason to use the number 255, but it is typically larger than any substring we are likely to encounter and it has become the de facto standard value for “copy all” statements (since the length parameter is required), making it clear to the reader what our intentions are.

Ultimately, all we are doing here is stripping away the value(s) and delimiter(s) in front of the value we want.

Great! So I Can Parse All of The Things?

Yes, one can technically parse values out of strings with any number of delimiters. The only real limitation is the size limit of the IMT statement text box. With each delimiter, the statement grows exponentially larger as we strip away the first value, second value, etc to reach the one we are looking for. But with massive statements comes massive headaches when it comes time to make changes. So when you can, avoid parsing values from delimited strings in IMT and first leverage any other tools you have available (such as user-defined fields in the target business system) to make your life a bit easier.

Josh Johnson

Josh Johnson

Solutions Architect at TrueCommerce
Josh has been writing software for 7 years. He currently works on the .NET stack using .NET Core and Angular to build highly-customized solutions for the Professional Services team at TrueCommerce.
Josh Johnson

Latest posts by Josh Johnson (see all)