asked on

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

Sean Stuber

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

Stampel

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

Stampel

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

angel7170

ASKER

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

angel7170

ASKER

Format of the column is LASTNAME, FIRSTNAME MIDDLENAME

Lastname followed by comma then Firstname space middlename

ASKER CERTIFIED SOLUTION

Sean Stuber

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

angel7170

ASKER

Still the last name is not picking up MILLER JR, it just gets MILLER and drops JR.