Split a variable length string in oracle 10g

Question

I am having trouble to split a space delimited variable length string containing names in oracle 10g.Below are the few example of what I mean

1. Name : Alpha Beta 
   First_Name : Alpha
   Last_Name : Beta

2. Name : Alpha Beta Gamma
   First_Name : Alpha
   Middle_Name : Beta
   Last_Name : Gamma

3. Name : Alpha Beta Gamma Omega
   First_Name : Alpha
   Middle_Name : Beta
   Last_Name : Gamma Omega

Thank you.

Answer 1

Oracle 10g? That's an oldie.

SQL> with test (id, name) as
  2    (select 0, 'Alpha'                  from dual union all
  3     select 1, 'Alpha Beta'             from dual union all
  4     select 2, 'Alpha Beta Gamma'       from dual union all
  5     select 3, 'Alpha Beta Gamma Omega' from dual
  6    )
  7  select id,
  8    substr(name, 1, case when instr(name, ' ') = 0 then length(name)
  9                         else instr(name, ' ') - 1
 10                    end
 11          ) first_name,
 12    --
 13    substr(name, instr(name, ' ') + 1,
 14                 instr(name, ' ', 1, 2) - instr(name, ' ') - 1
 15          ) middle_name,
 16    --
 17    case when instr(name, ' ') = 0 then null
 18         else substr(name, instr(name, ' ', 1, case when instr(name, ' ', 1, 2) = 0 then 1
 19                                                    else 2
 20                                               end) + 1)
 21    end last_name
 22  from test;

        ID FIRST_NAME      MIDDLE_NAME     LAST_NAME
---------- --------------- --------------- ---------------
         0 Alpha
         1 Alpha                           Beta
         2 Alpha           Beta            Gamma
         3 Alpha           Beta            Gamma Omega

SQL>

What does it do? Searches for spaces in name column and extract values as you described. Nothing special about it, except - perhaps - last_name which also has case expression. It checks whether the 2nd space exists or not; if not, substring after the 1st space is last name; otherwise, substring after the 2nd space is last name.