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.

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!

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 1 Answer and 4 Comments.
Start Free Trial
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