Home » SQL & PL/SQL » SQL & PL/SQL » Help with a Query
Help with a Query [message #224018] Mon, 12 March 2007 11:57 Go to next message
Messages: 67
Registered: February 2006
I'm trying to parse out a name field into lname,fname and mname. Running into few issues when i try to run this query against my table. In the 1st query the '.' in the middle name. In the second query--if no middle name then it doesn't parse out the first name.

Please let me know how I can fix this query.
Thanks in advance for your help!

SELECT 'Doe, John A.',
substr('Doe, John A.',1, instr('Doe, John A.',',') -1) last_name,
substr('Doe, John A.',instr('Doe, John A.',',',1)+1, instr('Doe, John A.',' ',1,2)-instr('Doe, John A.',' ',1)+1) first_name,
substr('Doe, John A.',instr('Doe, John A.',' ',1,2)+1) middle_name
FROM dual;
SELECT 'Doe, John',
substr('Doe, John',1, instr('Doe, John',',') -1) last_name,
substr('Doe, John',instr('Doe, John',',',1)+1, instr('Doe, John',' ',1,2)-instr('Doe, John',' ',1)+1) first_name,
substr('Doe, John',instr('Doe, John',' ',1,2)+1) middle_name
FROM dual;
Re: Help with a Query [message #224033 is a reply to message #224018] Mon, 12 March 2007 13:13 Go to previous messageGo to next message
Messages: 68
Registered: February 2007

you can use rtrim to remove '.' from middle name
and to hanlde others use CASE expression
Re: Help with a Query [message #224132 is a reply to message #224018] Tue, 13 March 2007 03:50 Go to previous messageGo to next message
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
If you can guarantee that there will always be a comma after the last name, and that the first and middle names will always be sepatated by a space (if the middle name exists) then you can do it like this:
SQL> with names as (select 'Doe, John A' full_name from dual union all
  2                 select 'Doe, Jane'   full_name from dual)
  3      ,bit   as (select trim(substr(full_name,1,instr(full_name,',')-1)) lname
  4                       ,trim(substr(full_name,instr(full_name,',')+1))   other
  5                 from names)
  6  select case when instr(other,' ') = 0 then other
  7              else substr(other,1,instr(other,' ')-1) end  fname
  8        ,case when instr(other,' ') = 0 then null
  9              else substr(other,instr(other,' ')+1) end mname
 10        ,lname
 11  from bit ;

----------- ----------- -----------
John        A           Doe
Jane                    Doe
Re: Help with a Query [message #224158 is a reply to message #224018] Tue, 13 March 2007 05:36 Go to previous message
Messages: 2794
Registered: April 2006
Senior Member
Check this thread.
Previous Topic: Coursor
Next Topic: how to copy data from one DB to another DB
Goto Forum:

Current Time: Sat Jul 22 22:17:06 CDT 2017

Total time taken to generate the page: 0.17786 seconds