Pulling specific text out of a long text field 1

[ddrake]

Hi there,

Running CR9, SQL2K, ARS 5.1.2 and I've got a puzzler. I have a field that I need to pull lines of text from. This is a sample of part of this field's value:

8/14/2003 5:12:27 PM ddrake
Asset categorization information has changed from 'Monitor, Compaq Computer and S920' to 'Monitor', 'Compaq Computer' and 'S910'.

6/20/2003 9:36:00 PM ddrake
Asset location information has changed from 'Kansas City, Grand and Non Tobacco.' to 'Kansas City', 'Pershing Road Facility' and 'Non Tobacco'.

6/3/2003 9:21:16 AM ddrake
Asset location information has changed from 'Kansas City, Pershing Road Facility and Non Tobacco.' to 'Kansas City', 'Grand' and 'Non Tobacco'.


Some of them are pretty long. What I need to do is, for each record, in this field, whenever it says "Asset location information has changed..." I need to have it return the "From" and "To" values. Even if there are many of these per record.

Any notions would be welcome. Thank you!

 

[vidru]

This might get you started. Two formulas:

//@FromStr
if instr({Table.StringField}, "Asset location information has changed") = 1 then
extractstring({Table.StringField}, "from"," to ")
else
"";

//@ToStr
if instr({Table.StringField}, "Asset location information has changed") = 1 then
extractstring({Table.StringField}, " to ",".")
else
"";

Potential problem would be if a value in the 'To' portion has a '.' before the end of the string. That could be solved by using Split:

Split({Table.Field}, " to ")[2]

-dave

 

[ddrake]

These two formulae work great! The only issue is that if I run another with just instr({Table.StringField}, "Asset location information has changed") put in it to see what the numerical value is that it produces, I get some pretty odd numbers (967, 1002, etc). As a result, the conditional is never true and no extraction takes place.