Split unknown input field. Code Review & suggestions ple

[larryoceanview]

I need to split an address coming in in one field into multiple fields for correcting.

I came up with a function that can work but is ugly!

And since I will probably have to do this over & over I would like to come up with something that is re-usable.

Any 'S .

Code: Select all

Var1= " "               
field1= " "
field1= " "
field3= " "
field4= " "
field5= " "
changedfield = " "
corrected = " "

Begin Case
  Case  Arg1 MATCHES  "...'1313 ROCKINGBIRD LANE'..." 
     Var1 = Ereplace(Arg1, " ", "~")
     field1= Field(Var1,"~",1)
     field2= Field(Var1,"~",2) 
     field3= Field(Var1,"~",3)
     field4= Field(Var1,"~",4) 
     field5= Field(Var1,"~",5)
End Case
Begin Case
     Case field1 = "ROCKINGBIRD"
          changedfield = "MOCKINGBIRD"
          corrected = changedfield : field2 : field3 : field4 : field5
     Case field2 = "ROCKINGBIRD"
          changedfield = "MOCKINGBIRD"
          corrected = field1: "|" : changedfield : "|" : field3: "|" : field4: "|" : field5
         
     Case field3 =  "ROCKINGBIRD"
          changedfield = "MOCKINGBIRD"
          corrected = field1 :  field2: changedfield : field4 : field5
     Case field4 = "ROCKINGBIRD"
          changedfield = "MOCKINGBIRD"
          corrected = field1 : field2 : field3 changedfield : field5
     Case field5 =  "ROCKINGBIRD"
          changedfield = "MOCKINGBIRD"
          corrected = field1 :  field2: field3 : field4 : changedfield
     Case @True                   ;* all other values
          corrected = Arg1
End Case
 
Ans = corrected   

[vmcburney]

Let's start off with a couple address standardisation basics:
- QualityStage is the best tool for this type of work. You can spend weeks and months writing and testing address standardisation routines in DataStage or you could get better quality out of QualityStage in under a week.
- You should not correct the spelling of street names unless you have access to an address matching product certified by your postal authority, or you are using QualityStage address matching jobs.

You've provided a sample address which you standardise in a routine. We could certainly give you code that will split up these type of addresses if every address was in the same format: street number followed by street name followed by street type. The problem is with the hundreds of other address formats including unit numbers, floor numbers, po boxes, shopping centers, property titles, street number ranges, data entry errors etc etc.

If you cannot get QualityStage into your workplace then have a look at all your addresses and take note of the different formats, then design your standardisation routines as a SDLC with time spent on analysis and design first. Try not to write any code until you have worked out how to tackle all the combinations.

[kduke]

Code: Select all

Var1 = Arg1
Begin Case 
  Case  Arg1 MATCHES  "...'1313 ROCKINGBIRD LANE'..." 
     Var1 = Ereplace(Arg1, "ROCKINGBIRD", "MOCKINGBIRD") 
End Case 
Ans = Var1

[ray.wurlod]

I'd just like to reinforce as strongly as possible everything Vincent posted. He was even nice enough to use US spelling for you!
In particular, if you are going to "roll your own" do not stint on planning and design time.
US Postal certification is available to plug in directly to Quality Stage (which is the product formerly known as INTEGRITY from Vality, which was acquired by Ascential). It really will return the investment quickly, compared to a do-it-yourself approach, and won't miss any of the weird scenarios that might be encountered.
And it will handle it when you need to process Cuban and Haitian address formats!

[kcbland]

For a walk down memory lane....

viewtopic.php?t=85855&postdays=0&postor ... ss&start=0

[larryoceanview]

I agree!

It seems like the attempted clean-up was a waste and did more harm then good besides waste valuable time!

I was trying to emulate existing code for migration, You didn't think this was my brainstorm?

There is an address process that is run down the line that should handle this.

I already wasted a day on this ( was a lot of other attempted fixes like adding a dash to a non-numeric street number, How would you know? without having the tools you mentioned) and was given the command to move out. :D
But who knows I might wind up back here later.


Thanks for all your replies.