Text Cleanup

Clarifications

Firstly, the data collection application should have a few rules determining how data is entered and stored.
But, its a bit late for that now..

Two ways to do it:
1. Pay a company to take your data and clean it for you. If you or your company do not have the nessasary skills, this may be the way to go.

2. Do it yourself. If you or other staff have programming skills in the database platform you use its not that hard to do. You must analyse your data first. Find out:
-What fields need to be cleaned
-What percentage of data is deemed to be dirty
-What steps do you need to take to make it clean.
-What dirty data is "common".

For example:Samuel/Liam Mr or Samuel Liam Mr or SamuelLiam Mr or Samuel L Mr

My approach is to write a peice of code that looks for ' Mr ' and where it finds it, removes it from the string and either puts it into a new column on the table, or moves it to the beggining of the string.
Then look for other common errors and code as much as you can to automate the process.

Lastly, there will always be an element of manual work if you really want it squeaky clean. Which means taking the table, ordering by name, and quickly running an eye over each row, fixing anything that needs to be, then ordering by address or whatever fields you have and so on. If you fix something more than once, write a peice of code for it, because if its there twice in the first 100 rows, it will be there again in the next 100 rows. :)

It can seem a tedious job, but its actually not that bad if you've the skills. I used to process about 300,000 - 500,000 rows of data a day using both stored procedures and manually checking.

If you take this route..please, i cannot stress enough..BACK UP your database..and each time you do a major peice of work, back it up again..and keep ALL your backups untill the job is done..the agony of forgetting to back up and running a procedure that goes wrong is not nice :)

ANother thing, if you do decide to do this, save all your code!!! Because in a years time, your going to have to do it all over again.