Solved

Extracting Subsrings in columns

Posted on 2013-12-04
2
317 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 76

Accepted Solution

by:
slightwv (䄆 Netminder) earned 500 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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…

910 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

18 Experts available now in Live!

Get 1:1 Help Now