I'm trying to isolate some characters from a field (in the load editor) that can be stated various ways. There could be text in front, back, or both. Examples below
FIELD_X:
ABC ?,
ABC ?(
ABC ??,
ABC ??(
... ABC ?,
ABC ??? ( ........
TRYING TO ISOLATE (?)
My attempt:
if (OTHER_FIELD = 'x', TextBetween(FIELD_X, 'ABC', ','),
if (OTHER_FIELD = 'x', TextBetween(FIELD-X, 'ABC', '(') )) as desired_new_field,
This sort of works, but it only works for first statement and not for the second?? How can I write this for the mutliple formats?
A way to do it if you need to have a flexible wildcard pattern-list that should be matched upon:
LOAD
.....
Pick(
// First determine the match pattern #
WildMatch(
FIELD_X
,'ABC ?,*'
,'ABC ?(*'
,'ABC ??,*'
,'ABC ??(*'
,'* ABC ?*' // ! Must be in synch with line #20
,'ABC ??? (*'
)+1 // Add one so we have something to pick as #1 if no match
,'' // NO MATCH as an empty string or replace with Null() if that's better
// Pick the right character(s) based on the pattern # determine above
,Mid(FIELD_X,5,1)
,Mid(FIELD_X,5,1)
,Mid(FIELD_X,5,2)
,Mid(FIELD_X,5,2)
,Mid(FIELD_X,Index(FIELD_X,' ABC ')+5,1) // ! must be in sync with line #11
,Mid(FIELD_X,5,3)
) AS desired_new_field,
....
If I am wrong in assuming that you want arbitrary text to come after all the patterns then just remove the star at the end of the patterns from line 5 to 10.
I assumed that you needed to pick a single character exactly, or two characters exactly or three characters exactly - not just any number of characters so the ? mark in the patterns are exactly that - match for one and only one character for each ?.
And if you need to have just a match on alphanumeric characters you should wrap the Pick function in a KeepChar function:
KeepChar( Pick(.......
....
....) , 'ABCDEFGHIJKLMNOPQRSTUVWXYZ01234567890') AS desired_new_field,
