| Author |
Topic |
|
yaditzal
Starting Member
22 Posts |
 Posted - 2010-04-08 : 09:12:27
|
| hello.any help with this.I have a table called client_addr like this.addr_id addr1 1 1 SW 129 AVE 2 1 SW 129 AVENUE 3 1 SW 129 AVENUE,STE 109 4 10 NW 42 AVE 5 10 NW 42 AVENUE,STE 300I need to clean the data as much as posible because I have the same addr write in diferents ways, but at the end is it the same.Is there any way to compare the rcords like I dont know maybe using just the numbers in addr1 to select the must acure and also get the STE number and put it in addr2.like this.addr_id addr1 addr2 1 1 SW 129 AVENUE STE 109 2 10 NW 42 AVE STE 300pd: there is always a comma before the ste like this 10 NW 42 AVENUE, STE 300thanks |
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2010-04-08 : 09:31:26
|
| One simple select would beselect addr_id, addr1 from tablewhere addr1 like '%,%'MadhivananFailing to plan is Planning to fail |
 |
|
|
yaditzal
Starting Member
22 Posts |
Posted - 2010-04-08 : 09:44:12
|
| that select return oly the addr with ',' what I need is to clean as much as posible the similar addr like these. 1 SW 129 AVE 1 SW 129 AVENUE 1 SW 129 AVENUE,STE 109I need to compare in some way the addr and get just one record, those addr at the end are the same but write in deferents ways, waht i need is to reduce the repeated addr, in those 3 records i want just one (the best)1 SW 129 AVENUEand I also need to get the addr that has ste number separated by comma and put that ste number in addr2. |
|
|
|
DBA in the making
Aged Yak Warrior
638 Posts |
Posted - 2010-04-08 : 10:20:44
|
quote:
Originally posted by madhivanan
One simple select would beselect addr_id, addr1 from tablewhere addr1 like '%,%'
This is a lot more complex than that. How many records are we talking about?There are 10 types of people in the world, those that understand binary, and those that don't. |
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2010-04-08 : 10:25:34
|
quote:
Originally posted by DBA in the making
quote:
Originally posted by madhivanan
One simple select would beselect addr_id, addr1 from tablewhere addr1 like '%,%'
This is a lot more complex than that. How many records are we talking about?There are 10 types of people in the world, those that understand binary, and those that don't.
Yes. We need to see more sample data from OPMadhivananFailing to plan is Planning to fail |
|
|
|
yaditzal
Starting Member
22 Posts |
Posted - 2010-04-08 : 10:47:29
|
| HERE IT IS THE SAMPLE1 SAMPLE ROAD 1 SHIRCLIFF WAY 1 SW 129 AVE 1 SW 129 AVENUE 1 SW 129 AVENUE, STE 109 1 SW 129 AVENUE, STE 401 1 SW 129 TH AVE 1 SW 129TH AVE 1 SW 29 AVENUE 1 WEST SAMPLER RD 10 NW 42 AVE 10 NW 42 AVENUE, STE 300 100 AIRPORT RD 100 E COMMERCIAL BLV 100 E COMMERCIAL BLVD 100 E. COMMERICAL BL 100 E.COMMERCIAL BLV 100 N STATE ROAD 7 100 N STATE ROAD 7, STE 204 100 NE 15 STREET, STEMy table has 3000 records with addrs but they are duplicated because the data entry wrote it wrong, now I need to clean it the best I can.any help please. |
|
|
|
DBA in the making
Aged Yak Warrior
638 Posts |
Posted - 2010-04-08 : 10:59:13
|
quote:
Originally posted by yaditzal100 E COMMERCIAL BLV 100 E COMMERCIAL BLVD100 E. COMMERICAL BL100 E.COMMERCIAL BLV
Using these 4 records as an example. That's a pretty nifty query to identify that they're all the same address. By the time you'd written and debugged it, you could fix them manually. I'd put more time and effort into modifying the data entry process to prevent it happening again.There are 10 types of people in the world, those that understand binary, and those that don't. |
|
|
|
yaditzal
Starting Member
22 Posts |
Posted - 2010-04-08 : 13:03:19
|
| I resolved more or less the problem, what I did was, firth I took out the '.' then update where ave then AVENUE and like that with the ST, TR,CT,BLVD. and I was able to convert some of them in the same addr then I did a select distinct.I had 3589 records, now I have 2085. that's better than before.Thanks any way. |
|
|
|
|
deleting similar rows.