Solved

SUBSTRING in ORACLE SQL

Posted on 2014-10-29
10
259 Views
Last Modified: 2014-10-29
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
Comment
Question by:angel7170
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 2
  • 2
10 Comments
 
LVL 74

Expert Comment

by:sdstuber
ID: 40410601
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
 
LVL 7

Expert Comment

by:Stampel
ID: 40410605
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
 
LVL 7

Expert Comment

by:Stampel
ID: 40410692
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
Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

 

Author Comment

by:angel7170
ID: 40410745
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
 

Author Comment

by:angel7170
ID: 40410747
Format of the column is LASTNAME, FIRSTNAME MIDDLENAME

Lastname followed by comma then Firstname space middlename
0
 
LVL 74

Accepted Solution

by:
sdstuber earned 500 total points
ID: 40410767
>>> 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
 

Author Comment

by:angel7170
ID: 40410806
Still the last name is not picking up MILLER JR, it just gets MILLER and drops JR.
0
 

Author Comment

by:angel7170
ID: 40410810
Firstname and middlename seems to be fine
0
 

Author Comment

by:angel7170
ID: 40410816
Nevermind, it works now. I see that you have removed the space in LASTNAME. Thank you very much
0

Featured Post

Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

691 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question