Using the ExtractString function

Friday 24 May 2019 @ 7:49 pm

I recently found a function in a Crystal Report that I hadn’t noticed before. Technically it is an additional function (UFL) but I am pretty sure it has been installed automatically with Crystal Reports for a long time. The file is dated 11/8/2000 so it might have been introduced with Crystal Reports V8.

The function is called ExtractString (). It is designed to locate two character strings within a longer string and return all the characters in between those two strings. I have done something similar using the InStr() function but it is much more complicated. A good example of a use for this is when you have XML tags in the middle of a long memo field and you want to extract the value between two specific tags. Say it looks something like this:

“blah blah blah <price>19.99</price> blah blah blah <price2>29.99</price2> blah blah blah “

To extract the value for price2 you would use the following formula:

ExtractString ( {table.xmlField}, '<price2>', '</price2>' )

You give the function three arguments:

  • The field you are searching
  • The string that marks the start
  • The string that marks the end

If it doesn’t find the start string it returns a blank (even if the end string is there).
If it finds the start string but no end string it returns everything after the start string.
if it finds both the start and the end it returns all the characters between them.
It skips over any end strings that occur before the start string.
If it finds multiple starts or ends it uses the first.

Next time I have to parse XML or do something similar I will use this function and save myself a few steps.

Update 7/4/2021:

I just found out today that this function can return a maximum of 510 characters.  If you think you might need to return more than this you can use this alternative approach.

(For examples of my most popular formulas, please visit the FORMULAS page on my website.)







Leave a Reply

Recrystallize Pro