Split Address subscript problem 1

[jcl5]

Hi guys

Using Oracle 8 and Crystal 9

I am trying to split a one line comma delimited address field into a normal address format using following:

@Add1 split({099.HOME_address_name},",")[1]
@Add2 split({099.HOME_address_name},",")[2]
@Add3 split({099.HOME_address_name},",")[3]
@Add4 split({099.HOME_address_name},",")[4]

The number of delimiters can be different each time and am getting the following error message if e.g. @Add4 is not found;

"a subscript must be between 1 and the size of the array"

Is there a formula I can use to check if this is null before printing records?

Thanks

jcl5

 

[Madawc]

I'd do each line as

if not isnull(split({099.HOME_address_name},",")[1])
then split({099.HOME_address_name},",")[1]
else ""

String together the four in another formula. You could also put the tests all together, by a set of if - then - else statements, but it would get very long-winded. Something like

if not isnull(split({099.HOME_address_name},",")[4])
then 
split({099.HOME_address_name},",")[1] & " " &
split({099.HOME_address_name},",")[2] & " " &
split({099.HOME_address_name},",")[3] & " " &
split({099.HOME_address_name},",")[4]
else
if not isnull(split({099.HOME_address_name},",")[3])
then 
split({099.HOME_address_name},",")[1] & " " &
split({099.HOME_address_name},",")[2] & " " &
split({099.HOME_address_name},",")[3]
else 
if not isnull(split({099.HOME_address_name},",")[2])
then 
split({099.HOME_address_name},",")[1] & " " &
split({099.HOME_address_name},",")[2]
else 
split({099.HOME_address_name},",")[1]

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

 

[lbass]

For each formula, use a leadin clause like the following example:

//for the third element:
if ubound(split({099.HOME_address_name},",")) >= 3 then
split({099.HOME_address_name},",")[3]

-LB