-2

I have full_name column data like

|Victoria Brown  |
|Sam Allen JR    |
|Ray M James III |

I want to split base on the number of space the fistname, lastname HERE is what I did but last case statement is coming wrong it still getting the suffix when we have 3 space. also need to combinne them into one column please.

Bamba
  • 65
  • 6
  • 4
    Good luck. Some last names, like mine, contain a blank character, – Gilbert Le Blanc Dec 09 '21 at 01:45
  • Dr. Mary Joan De La Hoya - Mc Donalds III Jr, MD name can have salutaion (Dr), multiple first/middle names, last names may have prefixes (Mc, O, Della, etc), names can be doubled, there can be multiple suffixes (generational, professional, etc) – Igor N. Dec 09 '21 at 02:22
  • @DaleK I have tried this but the last case statement isn't coming correct. – Bamba Dec 09 '21 at 14:59

1 Answers1

1

This is unfortunately a lot more complex than it may first seem, and how you handle it may have to do largely with the end goal for the data.

Here's a post that covers this same issue in fairly great depth - SQL: parse the first, middle and last name from a fullname field

As Gilbert pointed out, some names are just different, and it will be hard to get everything right, but there are certainly things you can do to limit errors.

One of the better pieces of advice from that article would be to alter your collection method to get First/Middle/Last/Suffixes/Prefixes entered separately and join them after, rather than try to parse through richer text that contains them all.

Here is a function that you could get creative with - https://blog.seandaylor.com/sql-server-split-part/

procopypaster
  • 416
  • 1
  • 6