troubleshooting Question

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

Avatar of Cobra967
Cobra967Flag for United States of America asked on
Microsoft SQL Server
4 Comments1 Solution234 ViewsLast Modified:
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!
Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 1 Answer and 4 Comments.
Join the Community
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 4 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros