Remove Postcode from a string

[danrobson]

I have a string that contains the last line of an address and also the postcode, how can I remove the postcode and place in a seperate field?

Thanks,

Dan

 

[JoanneM]

Morning Dan

Is there a space, comma, or tab between the end of the address and the beginning of the postcode?


regards
jo

 

[danrobson]

Jo,

I wish! The postcode is located somewhere in the field, not even always at the end.

Any ideas?
Dan

 

[philcon]

Hi Dan,

As there are about 16M postcodes , they would be fairly difficult to identify by lookup.

Is this a one off exercise or something which you wish an application to do on an ongoing transactional basis.

Also are the postcodes standard formatted i.e. 8 characters long AND CAPS used appropriately

If so you could try and search the string for a postcode area (2 characters long which you coudl have a lookup table for)and pull the following 6 characters to a table where you could check for an appropriate postcode format. Obviously if the original format is not quite right, this would be problematic.

You might be better of using address management software (eg. QAS) to search for the postcodes as they are better equipped to do this.

Hope thats been of some help (not much I know)

Regards


Phil.

 

[danrobson]

Thanks for the pointers Phil. It's something that we would want to do on a semi regular basis as our raw data is in a mess and whenever we pull the data out for things such as a marketing mail merge, we want to format the data correctly. Unfortunately, the raw data doesn't have a field for postcode!

I was hoping maybe someone would have a little module already written to this, I've not given up hope just yet!

Thanks,
Dan

 

[philcon]

Dan,

not normally one to put my sales hat on in this environment, but I am actually a database marketing consultant, and something that strikes me as a little odd is that the address cleanse is only carried out when the data is required.

Are there steps which you could take to improve the quality of data on entry, thereby avoiding this occasional problem.

It does seem like an inordinate anount of work to try and do when deadlines loom (ie. mailings need to go out).

If you were able to send me a sample of the data, I will see if I can come up with a routine for you (gratis) but I really would emphasise the benefits of getting the data right on entry, because any routine you were to produce will undoubtedly come up with more than a few "Manual check required" flags.

Regards



Phil.