Hi
Can anyone show me how to remove the end characters from a string?
I made a big mistake in my earlier version of this database by concocting the address as a single string (OK I had my reasons but I can now see the error of my ways, and that proper normalisation is the true path to richeousness!)
I now recognise I would be better splitting it into its constituent parts, namely house number, street/town/postcode area, and then postcode sector.
I need, however, to convert existing data into my new three field structure.
I start off with something like
33-2 CLOVENSTONE PARK, ANYTOWN ZZ14 8PQ
I have managed to parse off the house number and also the last three characters which make up the postcode sector (although this still needs some manual editing since if there never was a sector there I end up with spurious data). I have also managed to cleave off the house number but I am left with
CLOVENSTONE PARK, ANYTOWN ZZ14 8PQ
I now need to remove the end blocks but cannot work out how. Trouble is sometimes there isnt a known postcode sector so my next record might be
CLOVENSTONE PARK, ANYTOWN ZZ14
What I need is to end up with all the records looking like
CLOVENSTONE PARK, ANYTOWN ZZ14
regardless of whether there was a postcode sector or not. I can then compare this to my street names table and allocate a numerical code for it and will hopefully end up with something like "33-2" "186" "8PQ" which will uniquely define the address.
Any ideas would be most helpfully received since the idea of manually doing this over several thousand records doesn't bear thinking about!!!
Thanks in advance
Best wishes
Malcy
Can anyone show me how to remove the end characters from a string?
I made a big mistake in my earlier version of this database by concocting the address as a single string (OK I had my reasons but I can now see the error of my ways, and that proper normalisation is the true path to richeousness!)
I now recognise I would be better splitting it into its constituent parts, namely house number, street/town/postcode area, and then postcode sector.
I need, however, to convert existing data into my new three field structure.
I start off with something like
33-2 CLOVENSTONE PARK, ANYTOWN ZZ14 8PQ
I have managed to parse off the house number and also the last three characters which make up the postcode sector (although this still needs some manual editing since if there never was a sector there I end up with spurious data). I have also managed to cleave off the house number but I am left with
CLOVENSTONE PARK, ANYTOWN ZZ14 8PQ
I now need to remove the end blocks but cannot work out how. Trouble is sometimes there isnt a known postcode sector so my next record might be
CLOVENSTONE PARK, ANYTOWN ZZ14
What I need is to end up with all the records looking like
CLOVENSTONE PARK, ANYTOWN ZZ14
regardless of whether there was a postcode sector or not. I can then compare this to my street names table and allocate a numerical code for it and will hopefully end up with something like "33-2" "186" "8PQ" which will uniquely define the address.
Any ideas would be most helpfully received since the idea of manually doing this over several thousand records doesn't bear thinking about!!!
Thanks in advance
Best wishes
Malcy