But, what you can do is select a substring out of the column, which is hokey because of where the break in the name occurs, OR use PL/SQL to select each row into a cursor, then compare each character in the name string to an array - once you hit a space, take what's been selected out into a concatenated name string and do an insert into a new, properly formed table. From there, take the remaining characters in the array, minus the space, concatenate those together, and do the second insert. Sounds like a pain in the butt? Yes. Should have created the table correctly in the first place, but all may not be lost. How was the data put in the table? One line at a time by hand? Or with a script? If with a script, modify it accordingly. If by hand, live and learn.
Okay, another way: select * from bad_table into a flat file - save it out to an external file that is tab delimited. Edit the file as necessary, then use SQL Loader to read in the flat file. How's that work for you?
I'm impressed with the effort jmodic put into this. However, since you claim it is not working for you, I suggest one of two courses of action:
1) Suck up to jmodic and ask him to further refine what he did.
2) Do the string array routine. That is guaranteed to work. Everytime you hit a space, an insert is made, and the rest of the array is processed. End of the array --> go to the next string in your cursor. Takes a lot more processing, but hey, what do you care? That's what computers are for.