SUBSTRING in ORACLE SQL

Hello,

I have a field WOKERNAME that is structured as "Lastname, FirstName Middlename". I need to separate this into three columns LastName, FirstName and MiddleName. How can I do that in Oracle SQL ? Please assist. Thank you

Example: SMITH, JOHN JAMES

LASTNAME= SMITH
FIRSTNAME = JOHN
MIDDLENAME = JAMES
angel7170Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

sdstuberCommented:
Assuming they are all capital letters then try this...

regexp_substr(workername,'[A-Z]+',1,1)  lastname,
regexp_substr(workername,'[A-Z]+',1,2)  firstname,
regexp_substr(workername,'[A-Z]+',1,3)  middlename

alternatively try looking for substrings that are NOT commas or spaces

regexp_substr(workername,'[^, ]+',1,1)  lastname,
regexp_substr(workername,'[^, ]+',1,2)  firstname,
regexp_substr(workername,'[^, ]+',1,3)  middlename
StampelCommented:
Just parse the string using this example :
http://oracletuts.net/plsql/how-to-tokenize-or-parse-a-string-in-plsql/
replacing delimiter with space.
Then you will just have to remove the trailing "," char , when present
StampelCommented:
Just parse the string using this example and replacing delimiter with space.
Then you will just have to remove the trailing "," char , when present

From http://oracletuts.net/plsql/how-to-tokenize-or-parse-a-string-in-plsql/

SQL> set serveroutput on
SQL>
SQL> DECLARE
  2      CURSOR cur IS
  3          WITH qry AS (
  4                          SELECT    'Paris#London#Rome#Oslo#Amsterdam#New York' city_string
  5                          FROM    dual
  6          )
  7          SELECT    regexp_substr (city_string, '[^#]+', 1, ROWNUM) city
  8          FROM    qry
  9          CONNECT BY LEVEL <= LENGTH(regexp_replace (city_string, '[^#]+')) + 1
 10          ;
 11
 12  BEGIN
 13      FOR rec IN cur LOOP
 14          dbms_output.put_line('City:' || rec.city);
 15      END LOOP;
 16  END;
 17  /

Open in new window

Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

angel7170Author Commented:
I tried using the expression below but I am encountering issue when last name has a space

for example MILLER JR, JOHN JAMES

How to work this out? please help. Thank you


regexp_substr(workername,'[^, ]+',1,1)  lastname,
regexp_substr(workername,'[^, ]+',1,2)  firstname,
regexp_substr(workername,'[^, ]+',1,3)  middlename
angel7170Author Commented:
Format of the column is LASTNAME, FIRSTNAME MIDDLENAME

Lastname followed by comma then Firstname space middlename
sdstuberCommented:
>>> Format of the column is LASTNAME, FIRSTNAME MIDDLENAME

that's overly simplistic since the delimiters change and can be part of a name

the format appears to be  "something not a comma", followed by a comma, followed by white space followed by "something not a space", followed by whitespace, followed by "something not a space"

given that format

try these

regexp_substr(workername,'[^,]+',1,1)  lastname,
regexp_substr(workername,'[^ ]+',instr(workername,',')+1,1)  firstname,
regexp_substr(workername,'[^ ]+',instr(workername,',')+1,2)  middlename

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
angel7170Author Commented:
Still the last name is not picking up MILLER JR, it just gets MILLER and drops JR.
angel7170Author Commented:
Firstname and middlename seems to be fine
angel7170Author Commented:
Nevermind, it works now. I see that you have removed the space in LASTNAME. Thank you very much
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.