split address fields 1

[hlmnsbuggy]

CR11, Oracle

I have to split address fields such as 101 maple street #211 or 202 wall ave. Apt 209...into street number, street name and apt number separately, How can I do that?

thank you for your help!!

Ann

 

[fsreport]

hi
would help if you post version and datasourde and how the data is displayed in your table



fsreport

 

[fsreport]

hi
if your field are separed
do like this
{Street_number}+', '+ {Street_Name}+' ' +{Apt_Number}

fsreport

 

[hlmnsbuggy]

Thank you fsreport for your reply,
The address type : string 255
currently address 101 maple street, #211 is in one filed. I need seperate it into 3 fields into
street number
street name
appartment number

How can I do that?

Thankss

 

[Madawc]

Treat the elements as separate fields, Split({address1}, " ")[1] and so on.
For an address like 10 NEW ROAD, "10" would be element [1], "NEW" would be element [2], "ROAD" would be element [3] and element [4] would be null. Put them in formula fields and adjust them there. Remember that formulas stop when they hit a null, unless ISNULL is used to test.
You can also use UBOUND to find the number of table elements.

//for {@element2}:
if ubound(split({your.field},":")) >= 2 then
split({your.field},":")[2]

//for {@element3}:
if ubound(split({your.field},":")) >= 3 then
split({your.field},":")[3]

In your case, the appartment and its variable indicator makes for extra problems. If it is always the last element, you could get it using unbound to find which is the last. If number is always the first and street name is in the middle, you could clear the extras from 'Street Name' by taking the middle elements, putting them into a single formula field and then using Replace to get rid of things like #, comma, full stop and "Apt".

Madawc Williams (East Anglia, UK). Using Windows XP & Crystal 10