Need to rearrange order of names from a fullname column to LAST, SUFFIX, FIRST and accommodating for no suffix

Transact-SQL
1

I currently have fullnames that are first last and suffix (Laverne Holley, LPN). I am trying to rearrange the name to be LASTNAME, SUFFIX, FIRSTNAME (Holley, LPN, Laverne) that is if there IS a suffix. Does anyone know how I can do this? Thank you!

CURRENT OUTPUT:
LAVERNE HOLLEY, LPN
MEGAN WATERS

DESIRED OUTPUT
HOLLEY, LPN, LAVERNE
WATERS, MEGAN

2

Adjusting names like that requires extraordinarily complex logic. You could have multiple first names, multiple middle names, Jr/Sr/III/IV etc.. I suggest a Google search to "borrow" someone else's deep logic for this.

Better yet, have people enter the parts of their name separately and store them separately. It's vastly, vastly easier to combine names in various ways than to split a combined name apart!

2 Likes
3

And there's some things you should review while considering any changes:

https://www.kalzumeus.com/2010/06/17/falsehoods-programmers-believe-about-names/

Follow-up:

And this isn't even dealing with things like Titles, Jobs, etc. like you have in your sample. And no matter how simple you think (or want to believe) your data will be, you probably already have an exception in there now.

1 Like