Split Name String using regexp_substr

Question

I've searched all over and I can't find the expression for what I'm looking to do. Could really use some help.

Here's a sample string which I want to break out into three columns... Lastname, Firstname, and Middle Initial. I'm able to get the lastname out without much of an issue because it's always going to be the string before the first comma.

Where I need help is with the first name and middle initial. This data has some spaces in it which are complicating things.

Here's a sample string which I need help splitting out.

NameString = "ARREOLA-GONZALEZ, LUZ M"

I'm able to pull the lastname using REGEXP_SUBSTR(NameString,'[^,]+',1,1)

Can someone please help me with the expression for the firstname and middle initial? Also, there isn't always a middle initial... so would need to return a null for middle initial if it doesn't exist.

Here's what I've got so far:

REGEXP_SUBSTR(NameString,'[^,]+',1,1) "LAST_NAME",
TRIM( REGEXP_SUBSTR(NameString,'[^,]+$')   ) "FIRST_NAME",
REGEXP_SUBSTR(   TRIM(REGEXP_SUBSTR(NameString,   '[^, ]+$'  )),   '[^ ]+$') "MIDDLE_NAME",

Which produces:

LAST_NAME           FIRST_NAME     MIDDLE_NAME
----------------------------------------------
ARREOLA-GONZALEZ    LUZ M          M

Gordon Linoff · Accepted Answer · 2018-07-23 02:16:26Z

1

This should do it:

select regexp_substr(name, '[^,]+', 1, 1) as lastname,
       regexp_substr(name, '[^ ]+', 1, 2) as firstname,
       regexp_substr(name, '[^ ]+', 1, 3) as middle

In other words, you can use space for the first and middle names.

If a comma or space can be used as a separator, then I think you can do:

select regexp_substr(name, '[^,]+', 1, 1) as lastname,
       regexp_substr(name, '[^ ,]+', 1, 2) as firstname,
       regexp_substr(name, '[^ ,]+', 1, 3) as middle

edited Jul 23, 2018 at 2:16

answered Jul 23, 2018 at 1:46

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Josh Over a year ago

Thanks so much Gordon. This looks like it's extremely close to what I need! I noticed that in my NameString rows there are a few which don't have a space after the comma. How would you suggest handling these? Here's an example: "MCGRATH,EMILY E"

Gordon Linoff Over a year ago

@Josh . . . I would suggest that you fix the data. In the meantime, you can use a case expression to selectively apply regexp_substr().

Josh Over a year ago

Thanks Gordon... ya, unfortunately this data isn't something I"m in control of... I'd absolutely fix it if I could :-) Thanks so much for the help tonight.. awarding you the answer and upvoting!! Thanks again!

AndyDan Over a year ago

You also have to be careful of first names with spaces in them. In our person table of 88k records, there are over 700 with a space in the first name, names like 'Bobbi Jo" or "Mary Anne".

Collectives™ on Stack Overflow

Split Name String using regexp_substr

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related