Removing middle initials from a text name field

[Anonymous]

I have a name column, some people have a middle initial, some do not. 

The format is [SURNAMES], [FIRSTNAMES] [MIDDLE INITIAL]

For example SMITH, JOSEPH or ADAMS, GOMEZ P

Some people have two firstnames or two surnames for example THORNTON, BILLY BOB or DAY-LEWIS, DANIEL

I need to remove the middle initial only. So ADAMS, GOMEZ P would become ADAMS, GOMEZ. My other examples would remain the same. 

I have no idea if this is possible. Any ideas?

[Anonymous]

Hi @Anonymous,

Are there any particular values or conditions in this field that can be used to remark the target records?
If not, I think you may need to use AI or machine learning features to recognize and analyze these recorded first.
Regards,

Xiaoxin Sheng

[Anonymous]

Anyone...?

[mh2587]

Split the column in PQ into three column First Name ,Middle Name and Last name if you don't need the middle name column just remove this, approach i got in my mind

[smpa01]

@Anonymous  what differentiates  ADAMS, GOMEZ P from THORNTON, BILLY BOB

[Anonymous]

@smpa01 it's complex in that I require to do a join on this name column. The table to which i'm joining omits middle initials. It would keep BILLY BOB but it wouldn't keep BILLY B. Totally a HR thing, nothing logical my end which I can control, other than to try and get the two tables married up before the join.