For what it is worth, I've personally attacked this problem more times than I know how to count any more. To make the long story very short, there is no single solution that works every time.
Just when you think you have a really good list of salutations, along comes someone whose first name is Doctor. (No, not kidding. Ran into that one 'bout twelve years ago. Left me with the question of what to call him if he made it through grad school. Doctor Doctor?
Then, in the Navy databases - and this extends to all military as I understand it, you run into something like JOHN NMN JONES. No, this isn't John Nathan Michael Norman Jones. It is "John (no middle name) Jones." Only with military, just like they have documents that would be blank except for the imprint, "This page intentionally left blank" (but then of course it isn't blank, is it?), you have to explicitly say "no middle name."
But Rural Guy has put his finger on the pulse of the problem. Therefore, Saron, I'm going to reinforce his suggestion. If you wanted the name parts separate, they should have been stored separately. As has already been pointed out, it is far easier to join things together than to take them apart. If separate name parts are critical to your DB operation, make them separate from the get-go. This after-the-fact stuff will make you tear out your hair.
Next question. How many names are we talking about? Because sometimes you can take the approach of doing it by hand if it is a small enough number.
Follow-up question. Admittedly if there are enough names this is impractical, too. But.... try getting your "splitter" routine to split the names. Now get Access to EXPORT the names to a comma-delimited text file along with some key field like an autonumber. Now go back in that file and note those places where an abomination occurred. Use NOTEPAD to add or remove commas to make the name "columns" match up. Now re-import the text file using the autonumber to allow you to put back the proper results via UPDATE query. In other words, take your best shot at automation and then tweak the result where your best shot wasn't quite good enough.
Let me put it another way. Life's too short to screw around trying to make the perfect name splitter when it is equally possible to just get close and adjust the result to final perfection. You'll get old and grey like I am before you "solve" the insoluble problem of people's choices of names.
