Hello folks,
I am trying to extract the Last Name, First Name, First Middle Names and Initials of some Student Names for a Report. I have some success with Last Name and the First Middle Names but am having some difficulty coming up with the other two. I am using Oracle 9i and would like to come up with the SQL.
Script to create table:
create table grad_student_tb
(student_id varchar2(3) primary key,
salutation varchar2(4),
student_name varchar2(60));
Script to insert records into the table:
Insert into GRAD_STUDENT_TB (STUDENT_ID,SALUTATION,STUDENT_NAME) values ('001','Mr','Adams,Robert Murphy');
Insert into GRAD_STUDENT_TB (STUDENT_ID,SALUTATION,STUDENT_NAME) values ('002','Mr','Green,Eric Craig');
Insert into GRAD_STUDENT_TB (STUDENT_ID,SALUTATION,STUDENT_NAME) values ('003','Miss','Brown,Jennifer William H');
Insert into GRAD_STUDENT_TB (STUDENT_ID,SALUTATION,STUDENT_NAME) values ('004','Mr','Adams,Michael William H');
Insert into GRAD_STUDENT_TB (STUDENT_ID,SALUTATION,STUDENT_NAME) values ('005','Miss','White,Snow');
Here is what I would like on my Report:
Student ID First Middle Name Last Name First Name Initial
==================================================================
001 Robert Murphy Adams Robert M
002 Eric Craig Green Eric C
003 Jennifer William H Brown Jennifer W
004 Michael William H Adams Michael W
005 Snow White Snow
Currently my SQL looks liek this:
select student_id,
SUBSTR(student_name, INSTR(student_name,',')+1, LENGTH(student_name)) first_middle_name,
SUBSTR(student_name,1, INSTR(student_name,',')-1) last_name
from grad_student_tb;
Output:
STUDENT_ID FIRST_MIDDLE_NAME LAST_NAME
---------- ------------------------------------------------------------ ------------------------------------------------------------
001 Robert Murphy Adams
002 Eric Craig Green
003 Jennifer William H Brown
I need to come up with the First Name and Initial of the Students.
Any help is greatly appreciated!
Thanks
Edited by: Roxyrollers on Aug 18, 2011 12:53 PM