Extracting Subsrings in columns

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!
PattiNAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
slightwv (䄆 Netminder)Connect With a Mentor Commented:
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
 
PattiNAuthor Commented:
PERFECT!  Thank you so much.

Merry Christmas!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.