whats the best way to 'clean' my data?

[MattRichardsUK]

I have a table that stores customer information, at present I do not store the customer title, initials and forenames as separate fields - they are stored as a single field, for example, “Mr”, “Mr & Mrs”, “Mr J” and “Mrs AD” are stored as a single field. I would like to split out this field into 3 separate fields, Title, Initial and Forename. What would be the best way to do something like this. I am using Oracle 9i and there are around 600,000 rows in my table.
thanks in advance

 

[BJCooperIT]

1. Add the new columns to the table
2. Write a script that processes each row in a cursor:
A. Extract the three fields
B. Value the new columns accordingly
C. Strip the values off the original field
D. Update the row

The tough part is the extract code. Are there always a Title and at least one Initial? There must be millions of possible combinations. Is this the code you are looking for?

[sup]Beware of false knowledge; it is more dangerous than ignorance.[/sup][sup] ~George Bernard Shaw[/sup]
Consultant Developer/Analyst Oracle, Forms, Reports & PL/SQL (Windows)
My website: www.EmuProductsPlus.com

 

[MattRichardsUK]

yes there will always be a title and an initial. yes i think that is what i am looking for.