Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Extracting Subsrings in columns

Posted on 2013-12-04
2
Medium Priority
?
326 Views
Last Modified: 2013-12-04
I need to extract first name as one field and last name as another field from a column in a table that stores the whole name as 'last name, first name mi'.  For example, I need the following results:

from:  'Doe, Jane L'
         'Smith Jr., Frank C'
         'Brown M.D., Lawrence'

I need two columns in my SQL result set:
First Name             Last Name
Jane                         Doe
Frank                       Smith Jr.
Lawrence                Brown M.D.


Thank You!
0
Comment
Question by:PattiN
2 Comments
 
LVL 78

Accepted Solution

by:
slightwv (䄆 Netminder) earned 2000 total points
ID: 39696647
Try this:

drop table tab1 purge;
create table tab1(name varchar2(50));

insert into tab1 values('Doe, Jane L');
insert into tab1 values('Smith Jr., Frank C');
insert into tab1 values('Brown M.D., Lawrence');
commit;

select
	substr(regexp_substr(name,', [^ ]+'),3),
	regexp_substr(name,'^[^,]+')
from tab1;

Open in new window

0
 

Author Closing Comment

by:PattiN
ID: 39696758
PERFECT!  Thank you so much.

Merry Christmas!
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

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…
Shell script to create broker configuration file using current broker Configuration, solely for purpose of backup on Linux. Script may need to be modified depending on OS-installation. Please deploy and verify the script in a test environment.
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.
Suggested Courses

824 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