Create multiple rows based on pipe delimited data in SQL

Question

In SQL I'm trying to split a column into multiple rows. This is how the data looks:

Person	Affiliation
Bob	AB, CR, DE
Jane	AB

This is how I'd like the results to look:

Person	Affiliation
Bob	AB
Bob	CR
Bob	DE
Jane	AB

I made a pretty unsophisticated attempt and am looking for feedback on alternate ways to accomplish this.

with 
  AB as  (select AB.person, 'AB' affiliation from UP where up.affiliation like '%AB%'),
  CR as  (select CR.person, 'CR' affiliation from UP where up.affiliation like '%CR%'),
  DE as  (select DE.person, 'DE' affiliation from UP where up.affiliation like '%DE%')

select * from AB
union
select * from CR
union
select * from DE

Answer 1

There are several ways to solve this problem. I provided two solutions.

with rws as (
      select 'Bob' person, 'AB,CR,DE' str from dual UNION ALL
      select 'Jane' person, 'AB' str from dual
    )
select    rws.person
,     ca.val
from      rws
cross apply (
                select  regexp_substr ( rws.str
                      , '[^,]+'
                      , 1
                      , level
                          )  as val
        from    dual
        connect by  level <= regexp_count (rws.str, '[^,]+')
            )  ca
ORDER BY    rws.person, ca.val

 
PERSON  VALUE
Bob CR
Jane AB
Bob DE
Bob AB

You can also use a recursive query and simple string functions (which is slightly more to type but is faster than regular expressions):

with rws (person, str) as (
  select 'Bob', 'AB,CR,DE' from dual UNION ALL 
  select 'Jane', 'AB'      from dual 
),
bounds (person, str, spos, epos) AS (
  SELECT person,
         str,
         1,
         INSTR(str, ',', 1)
  FROM   rws
UNION ALL
  SELECT person,
         str,
         epos + 1,
         INSTR(str, ',', epos + 1)
  FROM   bounds
  WHERE  epos > 0
)
SELECT person,
       CASE epos
       WHEN 0
       THEN SUBSTR(str, spos)
       ELSE SUBSTR(str, spos, epos - spos)
       END AS value
FROM   bounds
ORDER BY person, value;