Parsing First, Last, Middle Initial and suffix from full name

Ali Ahad 91

I am trying to extract first last middle initial and suffix from a name field and there's no set pattern please see the pattern of the in my table

LOPEZ ROQUE, CARMELINA
ORTEGA, GISELLE A A
RUSSELL JR,  WILLIE C
NUNEZ, LILIANA

I have looked online and found different solutions but it doesn't check for suffix in a name. Not sure if there's a solution to separate it properly. Please let me know.

Erland Sommarskog 101.1K Reputation points MVP

2024-04-01T09:36:39.5+00:00

This is by no means a trivial exercise. And SQL Server is not the correct venue for this operation, but you should do this in a client-side language like Python, C# or Perl where you have access to regular expressions. But even with RegExps, there is a lot work and you may need a bit of heuristics.

Regular expressions are not available in SQL Server... Yet. At SQL Bits two weeks ago, Microsoft announced a private preview of Regular Expressions in Azure SQL Database.
Greg Low 1,495 Reputation points Microsoft Regional Director

2024-04-02T09:27:17.26+00:00

Yep, like Erland said. This is not trivial at all. And you won't get it right no matter how careful you are, at least if you have to deal with worldwide data. For example, when you say "Last Name", are you presuming that's the family name? In many places the family name is first. etc. etc. Spanish names can be very challenging too as they tend to append many names.

Might be too late, but the best option by far, is to get the person to separate it themselves. Otherwise, you can just do your best effort, and accept that it will likely be wrong in some cases. (Which is often enough to probably annoy some people)
Yitzhak Khabinsky 24,946 Reputation points

2024-04-02T12:20:26.9066667+00:00

While asking a question you need to provide a minimal reproducible example:

(1) DDL and sample data population, i.e. CREATE table(s) plus INSERT, T-SQL statements.

(2) What you need to do, i.e. logic, and your attempt implementation of it in T-SQL.

(3) Desired output based on the sample data in the #1 above.

(4) Your SQL Server version (SELECT @@version;)