1 Reply Latest reply on Jun 30, 2011 9:09 AM by philmodjunk

# Extracting a text string on pattern match

### Title

Extracting a text string on pattern match

### Post

I have a comments field with a PO number occassionally entered within the data.  The PO Pattern is always ##-####.  I want to extract just the PO number from the comment and put it in it's own field.

• ###### 1. Re: Extracting a text string on pattern match

If you could guaranteee that the comment field never had any other numeric digits, this would be easy to extract:

Filter ( CommentsField ; -1234567890 )

Would likely filter out all but that value. (though you might also need to remove leading and trailing - ) Since you might have additional numeric digits in your comment field, however, you'll need a more sophisticated approach to find this pattern.

Set Variable [\$Length ; value: Length ( yourTable::Comment ) ]
Set Variable [\$Comment ; Value : YourTable::comment ]
Loop
Set Variable [\$I ; Value: \$I + 1]
Exit Loop If [\$I > \$Length ]
Exit Loop If [ Let ( [ \$PO1 = Middle ( \$Comment ; \$I ; 2 ) ; \$PO2 = Middle ( \$Comment ; \$I + 3 ; 4 ) ]  ;
( Filter ( \$PO1 ; 1234567890 ) = \$PO1 ) AND ( Middle ( \$Comment ; \$I + 2 ) = "-" ) AND
( Filter ( \$PO2 ; 1234567890 ) = \$PO2 ) And ( Length ( \$PO2 ) = 2 ) ) ]
End Loop
If [\$I < \$Length // PO exists and was found ]
Set Field [YourTable::PO ; \$PO1 & "-" & \$PO2 ]
End If

This script can be performed by an OnObjectSave script trigger set up on the comments field.

It's also possible to convert the above algorithm into a recursive custom function to eliminate script and trigger, defining PO as either a calculation field or a text field with an auto-enter calculation that uses this custom function instead.