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.

CodePudding user response：

Oracle 10g? That's an oldie.

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

        ID FIRST_NAME      MIDDLE_NAME     LAST_NAME
---------- --------------- --------------- ---------------
         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.

CodePudding user response：

You can use some regular expression functions in Oracle DB 10g version such as REGEXP_INSTR() and REGEXP_SUBSTR() :

WITH t(id,name) AS
 (SELECT 1,'Alpha Beta'
    FROM dual
  UNION ALL
  SELECT 2,'Alpha Beta Gamma'
    FROM dual
  UNION ALL
  SELECT 3,'Alpha Beta Gamma Omega' FROM dual)
SELECT id,
       REGEXP_SUBSTR(name, '([^ ] )', 1, 1) AS First_Name,
       CASE
         WHEN LENGTH(name) - LENGTH(REPLACE(name,' ')) > 1 THEN
          REGEXP_SUBSTR(name, '([^ ] )', 1, 2)
       END AS Middle_Name,
       CASE
         WHEN LENGTH(name) - LENGTH(REPLACE(name,' ')) = 1 THEN
          REGEXP_SUBSTR(name, '([^ ] )', 1, 2)
         ELSE
          TRIM(REGEXP_SUBSTR(name, '(( .*) )',REGEXP_INSTR(name, ' ',1,2),1))
       END AS Last_Name       
  FROM t

Result set :