Split Full name into First Middle and Last

jbandley
jbandley Member
in Magic ETL

I am looking for a good way to split a full name into First Middle and Last. What is the best way to go about this.

Examples of possible name formats

Doe, John

Doe, John M

Doe, Joe James

Doe, John Henry

Doe-Kahn, John

Aba Doe, John

Best Answer

  • MarkSnodgrass
    MarkSnodgrass Coach
    Answer ✓

    You can use the SPLIT_PART function primarily to split these all out:

    First Name 

    SPLIT_PART(TRIM(SPLIT_PART(`Name`,',',2)),' ',1)

    Last Name

    SPLIT_PART(`Name`,',',1)

    Middle Name

    SPLIT_PART(TRIM(SPLIT_PART(`Name`,',',2)),' ',2)

    Based on your examples, this will do the trick.

    **Check out my Domo Tips & Tricks Videos

    **Make sure to <3 any users posts that helped you.
    **Please mark as accepted the ones who solved your issue.

Answers

  • MarkSnodgrass
    MarkSnodgrass Coach
    Answer ✓

    You can use the SPLIT_PART function primarily to split these all out:

    First Name 

    SPLIT_PART(TRIM(SPLIT_PART(`Name`,',',2)),' ',1)

    Last Name

    SPLIT_PART(`Name`,',',1)

    Middle Name

    SPLIT_PART(TRIM(SPLIT_PART(`Name`,',',2)),' ',2)

    Based on your examples, this will do the trick.

    **Check out my Domo Tips & Tricks Videos

    **Make sure to <3 any users posts that helped you.
    **Please mark as accepted the ones who solved your issue.
  • GrantSmith
    GrantSmith Coach

    Hi @jbandley

    @MarkSnodgrass offer's a good solution. I was going to suggest another option utilizing SUBSTRING, INSTR and LENGTH but his is a simpler option.

    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**

Categories