Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Extracting Subsrings in columns

Posted on 2013-12-04
2
Medium Priority
?
325 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
[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
2 Comments
 
LVL 77

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

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

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

Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

722 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