I have a column within a table containing personal details, among them the column "surname". For reasons best known to ourselves, included in this surname are the initials of the person's first names, e.g. Michael J Fox would have his surname listed as "Fox MJ".
What I need to do is write a query which removes these initials, bearing in mind that all the surnames will be different lengths, and some will have no initials at all after their surname.
In my opinion, the results you want are incongruent with the design of the table. If the last name with no appended data is needed, then the data would have been best stored that way. It gets back to long-standing data design policies.