Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
waxdart23
Starting Member
33 Posts |
 Posted - 2005-04-02 : 17:53:11
|
I am writing a SP to analyse UK address data by postcode and while my new system has data controls to ensure correct UK postcode data , I have converted some bad data .Is there a way of removing data that doesnt conform to UK postcode formatting? For example:SW7 2APWC1W 3DEL23 3BPSo that if a postcode field had 'Hertforshire' in it (for example), it would be set to null or blank.ThanksP |
|
|
derrickleggett
Pointy Haired Yak DBA
4184 Posts |
Posted - 2005-04-02 : 18:36:50
|
| ?? Do you have some kind of a function to identify the bad data, or a recordset of all the good data? If so, just use and UPDATE statement with a LEFT JOIN and UPDATE the bad values to NULL.MeanOldDBAderrickleggett@hotmail.comWhen life gives you a lemon, fire the DBA. |
 |
|
|
AndyB13
Aged Yak Warrior
583 Posts |
Posted - 2005-04-04 : 18:02:21
|
Just to add to Derricks post you can get a csv file from the Royal Mail (FOC), its called mailsort and contains all UK postcode prefixes. That should get you started[url]http://www.mailsorttechnical.com/[/url]HTHAndyBeauty is in the eyes of the beerholder  |
|
|
|
|
|
|
removing bad data