Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

Extracting Last First and Middle Names from a String

RoxyrollersAug 18 2011 — edited Aug 18 2011
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
This post has been answered by JustinCave on Aug 18 2011
Jump to Answer

Comments

JustinCave
Answer
Assuming every name is in exactly the same format
SQL> ed
Wrote file afiedt.buf

  1  select student_id,
  2         SUBSTR(student_name,
  3                INSTR(student_name,',')+1,
  4                LENGTH(student_name)-INSTR(student_name,' ')) first_name,
  5         SUBSTR(student_name,
  6                INSTR(student_name,' ')+1) middle_name,
  7         SUBSTR(student_name,
  8                INSTR(student_name,' ')+1,
  9                1) middle_initial,
 10         SUBSTR(student_name,1, INSTR(student_name,',')-1) last_name
 11* from   grad_student_tb
SQL> /

STU FIRST_NAME      MIDDLE_NAME          MIDDL LAST_NAME
--- --------------- -------------------- ----- ---------------
001 Robert          Murphy               M     Adams
002 Eric            Craig                C     Green
003 Jennifer        William H            W     Brown
In general, of course, parsing names like this is far more complicated because you have to deal with last names like "de la Hoya" that contain spaces and cannot easily be differentiated from someone with multiple middle names.

Justin
Marked as Answer by Roxyrollers · Sep 27 2020
Roxyrollers
I should have been more specific ...

When you take into consideration the Name in the dabase is stored in the format
Last Name,First Name Middle Name
Frank Kulash
Hi,

So far, so good! You're separating the last name from the other stuff correctly, using INSTR and ','.
Now you need to separate the first name from the rest of the stuff, using INSTR and ' '.
Make your current query a sub-query. Thenm in the main query, split first_middle_name into first_name and middle_name: everything up to the first space will be first_name, all the rest will be middle_name. To handle people who doe have any middle name, you can concatenate a space to the end before using INSTR.

Too bad you don't have Oracle 10. Regular expression, which were introduced then, make this much easier.
Roxyrollers
Yes, unfortunately not on 10g. I will give your solution a try too. So far, Justin's solution is working perfectly.
Hoek
I found (in PL/SQL Developer) that I needed to TRIM the first_name:
SQL> select student_id
  2  ,      substr( student_name
  3               , instr(student_name, ',')+1
  4               , length(student_name) - instr(student_name, ',')
  5               ) first_middle_name
  6  ,      substr( student_name
  7               , 1
  8               , instr(student_name, ',')-1
  9               ) last_name
 10  ,      '#'||substr( student_name
 11               , instr(student_name, ',')+1
 12               , length(student_name) - instr(student_name, chr(32))
 13               )
 14            ||'#'  first_name1  
 15  ,      '#'||trim (
 16            substr( student_name
 17                  , instr(student_name, ',')+1
 18                  , length(student_name) - instr(student_name, chr(32))
 19                  )
 20                )
 21            ||'#'  first_name2
 22  ,      substr( student_name
 23               , instr(student_name, chr(32))+1
 24               , 1
 25               ) initials     
 26  from   grad_student_tb;

STU FIRST_MIDDLE_NAME         LAST_NAME                 FIRST_NAME1               FIRST_NAME2               I
--- ------------------------- ------------------------- ------------------------- ------------------------- -
001 Robert Murphy             Adams                     #Robert#                  #Robert#                  M
002 Eric Craig                Green                     #Eric #                   #Eric#                    C
003 Jennifer William H        Brown                     #Jennifer #               #Jennifer#                W
Added the #'s to make it visible in SQL*Plus
Frank Kulash
Hi,

Yes, Justin's soution is simpler than what I was suggesting, but it assumes that you never have a space in the last name, e.g
'Cervantes Saavedra,Miguel'
"Vaughn Williams, Ralph' or
'Von der Trave,Thomas'
Roxyrollers
Apologies everyone ... I wasn't very clear in my Requirements. I have added some more insert statements and the last Student (White,Snow) does not have a middle Name.

So, the table contains the Full Name (Last Name followed by a comma then First Name followed by a Space and then everything else after that is the Middle Name)

With the current solutions, here is what I am outputting. So, for Student ID 004, its' including 'W' in the First Name and in case of Student ID 005, the Middle Initial is coming up with 'W' which is also incorrect.
STUDENT_ID FIRST_NAME                                                   MIDDLE_NAME                                                  MIDDLE_INITIAL LAST_NAME                                                    
---------- ------------------------------------------------------------ ------------------------------------------------------------ -------------- ------------------------------------------------------------ 
001        Robert                                                       Murphy                                                       M              Adams                                                        
002        Eric                                                         Craig                                                        C              Green                                                        
003        Jennifer                                                     William H                                                    W              Brown                                                        
004        Michael W                                                    William H                                                    W              Adams                                                        
005        Snow                                                         White,Snow                                                   W              White                                                        
Frank Kulash
Hi,

If you're not sure that full_name contains a space, then don't use INSTR to look for a space in the raw full_name column: use
INSTR ( full_name || ' '
      , ' '
      )
instead.
JustinCave
One option would be
SQL> ed
Wrote file afiedt.buf

  1  select student_id,
  2         last_name,
  3         (CASE WHEN instr(first_and_middle_name,' ') > 0
  4               THEN substr( first_and_middle_name,
  5                            instr(first_and_middle_name,' ')+1 )
  6               ELSE null
  7            END) middle_name,
  8         (CASE WHEN instr(first_and_middle_name,' ') > 0
  9               THEN substr( first_and_middle_name,
 10                            1,
 11                            instr(first_and_middle_name,' ') )
 12               ELSE first_and_middle_name
 13            END) first_name
 14    from (
 15      select student_id,
 16             SUBSTR(student_name,1, INSTR(student_name,',')-1) last_name,
 17             SUBSTR(student_name,
 18                    INSTR(student_name,',')+1) first_and_middle_name
 19*     from   grad_student_tb )
SQL> /

STU LAST_NAME       MIDDLE_NAME          FIRST_NAME
--- --------------- -------------------- ---------------
001 Adams           Murphy               Robert
002 Green           Craig                Eric
003 Brown           William H            Jennifer
004 White                                Snow
Justin
1 - 9
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Sep 15 2011
Added on Aug 18 2011
9 comments
21,093 views