• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 276
  • Last Modified:

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
0
angel7170
Asked:
angel7170
  • 5
  • 2
  • 2
1 Solution
 
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
0
 
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
0
 
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

0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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
0
 
angel7170Author Commented:
Format of the column is LASTNAME, FIRSTNAME MIDDLENAME

Lastname followed by comma then Firstname space middlename
0
 
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
0
 
angel7170Author Commented:
Still the last name is not picking up MILLER JR, it just gets MILLER and drops JR.
0
 
angel7170Author Commented:
Firstname and middlename seems to be fine
0
 
angel7170Author Commented:
Nevermind, it works now. I see that you have removed the space in LASTNAME. Thank you very much
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

  • 5
  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now