?
Solved

SUBSTRING in ORACLE SQL

Posted on 2014-10-29
10
Medium Priority
?
263 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
Independent Software Vendors: 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!

 

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 2000 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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.
Suggested Courses

801 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