chlorineKid

[chlorineKid]

Hi all, I'm looking for ideas on how to approach the following:

I've got a table in a SQL server database which has a whole lot of person information in it. This table needs to be "cleaned" so that the suburb and city fields are in a very specific format. There is a file with the fields in the correct format. What would be the best way to do this using SQL? I'd like to without using cursors if possible.

TIA,
CK [sig][/sig]

 

[jnicho02]

Is the 'file with fields in the correct format' a list of suburbs and cities?

Is the address currently held in a single column, or is it in (sometimes incorrect) seperate columns...e.g. address1, address2, suburb, city, zip code...?

Looks like a charindex problem to me.

Give us a couple of examples of data. [sig]<p> <br><a href=mailto: > </a><br><a href=

http://www.geocities.com/jnicho02/>my

home</a><br> [/sig]

 

[chlorineKid]

Example data:

bad suburb column: Correct suburb file is as below
new market Newmarket
Sandiriham Sandringham

So we've got mispellings, etc.
The address columns are in incorrect separate columns.

CK [sig][/sig]

 

[jnicho02]

You shouldn't have too much trouble detecting correctly spelt suburbs/towns. You can move them to another column if they are in the wrong one. You could then flag the address as 'probably correct'.

More difficult with the rest of the addresses. Normal approach is to work backwards from the postcode, generate the suburb/town and try to match that with the address lines.

The industrial strength solution is to get something like QuickAddress and call it via xp_cmdshell for each record you want to check. It will clean the address for you.

If it is a one-off you might want to see how many suspect addresses there are and clean them manually in a simple screen. [sig]<p> <br><a href=mailto: > </a><br><a href=

http://www.geocities.com/jnicho02/>my

home</a><br> [/sig]

 

[chlorineKid]

Thanks for your reply,

CK [sig][/sig]