Data cleanse - 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)

Data cleanse

Author

Topic

cipriani1984
Constraint Violating Yak Guru

304 Posts

Posted - 2009-11-19 : 11:14:58

Hi,

Just wanted advice on how to tackle this problem. If I have a table that gets updated from a system that is by manual entry and sometimes there will be mistakes where fields are in the wrong location.

Forename, Surname, Address1, Address2, Postcode
Krusty, Clown, W2 4lp, 11 Springfield, America

Id like the data to be like this instead:

Forename, Surname, Address1, Address2, Postcode
Krusty, Clown, 11 Springfield, America, W2 4lp

How would you write a process/update statement that would know the address is in the wrong fields etc or intelligence?

Thanks

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2009-11-19 : 11:48:28

The way I have approached similiar problems is by using a REGULAR EXPRESSION CLR ASSEMBLY.

It is up to you to determine the logic you want then to write the REGEX Expression to accomidate.

A simple logic is to swap the fields if there is a word with 4 alpha charectors and no numeric in the Address 2 field, and in the Address1 field there is no word with 4 alpha charectors and no numeric in it, you can assume fairly safely do the swap then. You would probably want to make sure you factor in words like "Suite" "Appartment", etc.

Regular expressions give a ton of flexibility when serching for things, so you can fine tune it a lot easier than you can with a regular query, but it's still possible to do what you want using the PATINDEX built in function as well and not use a CLR Assembly.

I just find Regular expressions far more flexible and easier to use.

Success is 10% Intelligence, 70% Determination, and 22% Stupidity.
\_/ _/ _/\_/ _/\_/ _/ _/- 881