Function to Retrieve alphanumeric values

[SPA_BI]

Hello Board,
I have a field that I am trying to separate alpha from non-alpha data from.

Below is the source and how it should separate. What makes this tricky is the different rules governing the extraction because of the inconsistency of the source field.
1)The number is either at the beginning,
2)or at the end enclosed in brackets,
3)or at the end of the field and not in brackets

Note: There may even be a multiple set of brackets in the field.

Field 1 Resultant Field 1 Resultant Field 2
'WELLINGTON (2126321100) 1' 'WELLINGTON' '2126321100'
'26208200 TOLMIE (MWL)' 'TOLMIE (MWL)' '26208200'
MICHIES ESTATE RD, KERNOT 'MICHIES ESTATE RD, KERNOT'
'45024600 OVENS RIVER 6' 'OVENS RIVER 6' '45024600'
'SWCHL/INT BW413' 'SWCHL/INT ' 'BW413'

Is there an advanced function that may help extract/separate the data I need?

thanks

[ArndW]

There are many string and numeric functions that you can use in order to effect this. The best is to use pattern matching to extract portions of the string based on your rules (look into the BASIC manual under MATCHES). Your pattern for the number could be "0X'('0N')'". Then you seem to be using a space " " as your token for parsing the line elements.

In any case this needs to be done in more than one line, so a routine is the best way to do this.

[kumar_s]

You can also try with combination of "MCN" and "MC/N" in Iconv /Oconv.

[ray.wurlod]

It could be done in an expression (or stage variables) without needing to go to a routine. Some combination of If..Then..Else (with MATCH operator), Field() and Oconv() functions (the latter with "MCA", "MC/A", "MCN" or "MC/N" conversions) should do it for you.

The "magic bullet" function that you seek is called MatchField(). But you'll still need the three-way If..Then..Else to differentiate the patterns.