Avatar of Cobra967
Flag for United States of America

asked on 

Remove the Middle name initials and the suffixes from a string in SQL Server

Hello, I need your help to write a view that will remove the middle name initial and suffix from a single full_name field.

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!
Microsoft SQL Server

Avatar of undefined
Last Comment

8/22/2022 - Mon