Solved

SUBSTRING in ORACLE SQL

Posted on 2014-10-29
10
233 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
  • 5
  • 2
  • 2
10 Comments
 
LVL 73

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
 

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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

Author Comment

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

Lastname followed by comma then Firstname space middlename
0
 
LVL 73

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Suggested Solutions

Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

707 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now