Help with cleaning up data - SQL Server Forums

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.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Help with cleaning up data

Author

Topic

JBelthoff
Posting Yak Master

173 Posts

Posted - 2011-07-11 : 14:59:21

I have a table that had a manual entry field which I am now trying to clean up and create a lookup table for. My problem is that there are multiple entries all of which are incredibly similar to each other but not exact. This is causing me problems because there are over 1000 vendors in my list and about 1/3 of them are very similar but are not duplicates.

Below is a sample of my query and data output. What I would like is one query which aggregates all of the similar items and places them into a single row with an ID field to create a cleaned lookup table so that there is 1 row per name.

My Question…

Is there any way to do this without writing 500 case statements in the query?

Here is what I have now


select distinct top 7 [Vendor Name]
from dbo.[Part Numbers]
where [Vendor Name] is not null
order by [Vendor Name]

Results


Vendor Name
--------------------------------------------------
 K&L MICROWAVE INC
ABLE ELECTRONICS
ACCESS FLOORING SUPPLIES
ACCURATE SCREW
ACCURATE SCREW MACHINE
ACCURATE SCREW MACHINE CO
ACCURATE SCREW MACHINE CO.

(7 row(s) affected)

Here is what I would like the data to become.


ID    Vendor Name
--------------------------------------------------
1     ABLE ELECTRONICS
2     ACCESS FLOORING SUPPLIES
3     ACCURATE SCREW MACHINE CO.
etc...

JBelthoff
› As far as myself... I do this for fun!

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2011-07-11 : 16:08:59

You might try something like this algorithm to compare Vendor Names: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=51540

It will probably take a couple of steps to filter down through these though...

Corey

I Has Returned!!

JBelthoff
Posting Yak Master

173 Posts

Posted - 2011-07-11 : 16:25:19

Thanks Corey,

I think at this point we will have to just script his out replacing the similar with a unique value and tying it back some how.

It was a shot in the dark...

JBelthoff
› As far as myself... I do this for fun!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources