Split Full name into First Middle and Last

jbandley · 2021-07-28T21:55:06+00:00

There was an error rendering this rich post.

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

Find more posts tagged with

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.

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.

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