Hello, I need your help to write a view that will remove the middle name initial and suffix from a single full_name field.
Assumptions:
a) The middle name and suffix can be in the beginning, middle or the end of the string.
b) The middle name will always be a single letter and it may or may not be followed by a period.
c) Once removing the comma and period from the string, anything with less than 2 characters in that string can be assumed to be a middle name or a suffix:
Input data:
John A Doe
A John Doe
John Doe A
John A. Doe
Mr. John Doe A Jr.
Output data:
John Doe
John Doe
John Doe
John Doe
I guess this can be done by
a) Removing period (.) and comma (,) using for example: REPLACE(REPLACE(full_name, '.', ''), ',', '') from the string
b) Then, by removing any single word that has a length of 2 or less from the same string.
When I get the corrected code, I will need to update full_name field with the cleaned string.
Thank you for your help!