select hyphen seperated column to rows

Question

I have a hyphen separated column containing names. I want to split this column into one single name rows.

I am using the following sql :

for t_cur in (select id, names str from table where names is not null) loop
insert into inv value (select id,SPLIT_VALUES,'TOT' from ( select t_cur.id, trim(REGEXP_SUBSTR (t_cur.STR, '[^-]+', 1, LEVEL)) SPLIT_VALUES  FROM dual 
 CONNECT BY LEVEL <= (SELECT LENGTH (REPLACE (t_cur.STR, ',', NULL)) FROM dual
 ) ) where SPLIT_VALUES is not null) ;
 end loop;
end;

The issue is that some names are composed. for example i could have :

Firstname1-secondname1 lastname1 - Firstname2 lastname2 - Firstname3 Lastname3

in this example i am having as a result :

Firstname1
secondname1 lastname1
Firstname2 lastname2 
Firstname3 Lastname3

can you help me with the right regular expression to use to have this result :

Firstname1-secondname1 lastname1
Firstname2 lastname2
Firstname3 Lastname3

the solution should be to check char until (space+hypehen) and not just (hyphen)

Thanks !

Answer 1

With a little help of REPLACE function that replaces a "true" separator (space-dash-space) with something else (# in my example), it becomes much simpler:

SQL> with test (col) as
  2    (select 'Firstname1-secondname1 lastname1 - Firstname2 lastname2 - Firstname3 Lastname3'
  3     from dual
  4    )
  5  select regexp_substr(replace(col, ' - ', '#'), '[^#]+', 1, level) name
  6  from test
  7  connect by level <= regexp_count(replace(col, ' - ', '#'), '#') + 1;
NAME
--------------------------------------------------------------------------
Firstname1-secondname1 lastname1
Firstname2 lastname2
Firstname3 Lastname3

SQL>

Answer 2

No need for a replace function with this form of regex:

WITH TEST (COL) AS
      (SELECT 'Firstname1-secondname1 lastname1 - Firstname2 lastname2 - Firstname3 Lastname3'
       FROM dual
      )
    SELECT REGEXP_SUBSTR(COL, '(.*?)( - |$)', 1, LEVEL, NULL, 1) NAME
    FROM TEST
    CONNECT BY LEVEL <= REGEXP_COUNT(COL, ' - ') + 1;