CODEWITHSUNDEEP
sqloracle-databaseplsql
Josh
Josh

Reputation: 429

Split Name String using regexp_substr

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

Upvotes: 2

Views: 2612

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270361

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

Upvotes: 1

Related Questions