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
Function to Retrieve alphanumeric values
Moderators: chulett, rschirm, roy
Function to Retrieve alphanumeric values
from SPA_BI
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.
In any case this needs to be done in more than one line, so a routine is the best way to do this.
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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.
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.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.