Posted on by Josh Johnson

Parsing Labeled Data in IMT

This post is a follow-up to my previous post, Parsing Delimited Strings in IMT. That post went into detail on how to parse values from a delimited string based on the desired value’s position in the string. This post will explain how to parse values that are labeled using a real-world example brought to me earlier this week.

The Problem

Given the following string, we need to pull the value labeled “Color”.

[T:TOKEN] = 5|Mount=Outside|Width=24|Height=36|Color=SnowPLS360001|Lift=DayNight|Color=SnowPLS320001|ExtensionBracket=ExtensionBracket|HoldDownBrackets=HoldDowns|Remake=No | NA

Note that this label exists twice in the string. I’ll explain how to grab both here, but if you’ve read my previous post, you can likely guess how we’ll get to that second value.

The Solution

Just like with regular delimited strings, the two main functions we are leveraging here are the INSTR and COPY functions. Here is what you need to remember about each of them:

  • INSTR always returns the index of the first occurrence of your substring (or “0” if it wasn’t found).
  • COPY uses 1-based indices (as does every other IMT function, but it is particularly important here).
  • The last parameter of COPY is the length of substring to return. It can be greater than the length of the input string. We generally use “255” if we want to pull “everything” after the start index. While it of course doesn’t pull literally everything – it will only return up to 255 characters – it is such a de facto standard at this point that it makes it clear to the reader what our intent is.

To grab the first color value, we first need to get the index of where “Color=” begins. The following statement will do that:

@INSTR([T:TOKEN],Color=)

Next, we want to pull everything after that label. The following statement is what we need. Note the addition of “6” to the result of the INSTR. This ensures we start reading after the label and equals sign by skipping over those characters:

@COPY([T:TOKEN],@NUM(@INSTR([T:TOKEN],Color=)+6),255)

Finally, we need to find the pipe and get its index so we can strip it and everything after it out. We’ll use another INSTR and COPY to do that. Note that we now need to use the last statement as the input, not [T:TOKEN]. If we use [T:TOKEN] by itself, it will end up finding the first pipe in the ENTIRE input string. We just want the first pipe after the “Color=” value. Note again that we are doing some math on the result. By subtracting one, we ensure the pipe itself isn’t included in the final output:

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

Now we put it all together by using another COPY to start reading the color value and set the length to the output of our previous statement, which found the position of the pipe:

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

The Second Value

The second color value takes a bit more work because, as we learned previously, the INSTR will only find the first occurrence of a substring. Now, while we could of course use “t|Color=” and adjust the math to add “7” instead of “6” to the INSTR result, that relies on a specific piece of data being present. If we can work without that assumption, we eliminate possible issues down the road. The logic here is the same, but we need to find the first “Color=” label, grab everything after it, then use that as the initial input instead of the token by itself:

@COPY(@COPY(@COPY([T:TOKEN],@NUM(@INSTR([T:TOKEN],Color=)+6),255),@INSTR(@COPY([T:TOKEN],@NUM(@INSTR([T:TOKEN],Color=)+6),255),Color=),255),1,@NUM(@INSTR(@COPY(@COPY([T:TOKEN],@NUM(@INSTR([T:TOKEN],Color=)+6),255),@INSTR(@COPY([T:TOKEN],@NUM(@INSTR([T:TOKEN],Color=)+6),255),Color=),255),|)-1))
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)