SQL / How to get multiple records based on a semicolon char in a particular column

Question

I'm wondering how to solve this without using any functions. I've got some kind of embedded SQL Client (12c) in the application where I'm working on and therefore I cannot use functions & procedures outside of the query.

I'm having this input: ID ¦ APP 14 ¦ AAA;BBBB;CC 15 ¦ AAA;DDDDD 16 ¦ BBBB;CC 17 ¦ AAA;BBBB;CC;DDDDD

I would like dynamically transform into this: ID ¦ APP 14 ¦ AAA 14 ¦ BBBB 14 ¦ CC 15 ¦ AAA 15 ¦ DDDDD 16 ¦ BBBB 16 ¦ CC 17 ¦ AAA 17 ¦ BBBB 17 ¦ CC 17 ¦ DDDDD

Any help appreciated.

did you tried to find: [how to split string](https://stackoverflow.com/search?q=%5Boracle%5D+split+string+comma) — Marmite Bomber, Jan 29 '18 at 20:23
you may consider this ( by replacing `,` with `;` ) : https://stackoverflow.com/questions/48376315/split-columntext-to-rows-extract-delimiter-in-bracket-oracle-sql — Barbaros Özhan, Jan 29 '18 at 20:24
One of many duplicates of [How to convert comma separated values to rows in oracle?](https://stackoverflow.com/questions/38371989/how-to-convert-comma-separated-values-to-rows-in-oracle). This must have been asked at least once a day since the mid-90s. — William Robertson, Jan 29 '18 at 20:41

score 1 · Accepted Answer · answered Jan 29 '18 at 20:25

Here's one option:

SQL> with test (id, app) as
  2  (select 14, 'aaa;bbb;cc' from dual union
  3   select 15, 'aaa;dddd'   from dual union
  4   select 16, 'bbbb;cc'    from dual
  5  )
  6  select id, regexp_substr(app, '[^;]+', 1, column_value) app
  7  from test,
  8       table(cast(multiset(select level from dual
  9                           connect by level <= regexp_count(app, ';') + 1)
 10                           as sys.odcinumberlist))
 11  order by 1, 2;

        ID APP
---------- ----------------------------------------
        14 aaa
        14 bbb
        14 cc
        15 aaa
        15 dddd
        16 bbbb
        16 cc

7 rows selected.

SQL>

score 1 · Answer 2 · answered Feb 03 '18 at 01:06

Littlefoot's answer was accepted as the answer, but I enjoy playing with common table expressions so I developed a solution using CTE. This solution can be used on non-Oracle platforms:

WITH
    test_dataset (id, app)
    AS
        (SELECT 14 AS id, 'aaa;bbb;cc' AS app
           FROM DUAL
         UNION
         SELECT 15, 'aaa;dddd'
           FROM DUAL
         UNION
         SELECT 16, 'bbbb;cc'
           FROM DUAL
         UNION
         SELECT 17, 'ZZZZ'
           FROM DUAL),
    aset (id, app, extra)
    AS
        (SELECT id
              , CASE WHEN INSTR (app, ';') = 0 THEN app ELSE SUBSTR (app, 1, INSTR (app, ';') - 1) END AS app
              , CASE WHEN INSTR (app, ';') = 0 THEN NULL ELSE SUBSTR (app, INSTR (app, ';') + 1) END AS extra
           FROM test_dataset
         UNION ALL
         SELECT id
              , CASE WHEN INSTR (extra, ';') = 0 THEN extra ELSE SUBSTR (extra, 1, INSTR (extra, ';') - 1) END
              , CASE WHEN INSTR (extra, ';') = 0 THEN NULL ELSE SUBSTR (extra, INSTR (extra, ';') + 1) END
           FROM aset
          WHERE extra IS NOT NULL)
  SELECT id, app
    FROM aset
ORDER BY id, app;

The answer?

ID APP
14 aaa
14 bbb
14 cc
15 aaa
15 dddd
16 bbbb
16 cc
17 ZZZZ

SQL / How to get multiple records based on a semicolon char in a particular column

2 Answers2