Break down integer interval into rows

Question

I'm a developer that uses Oracle database with .Net application. I successfully created a query to return some data that I want, and it returns something like this:

cod	charac1	charac2	items
I001	blue	solid	X;1-3,5
I002	red	liquid	Y;3-6,7,10

When I take this in, .Net is okay to handle the last column, I mean, it's easy to break it down like "X;1-3,5" to "X1,X2,X3,X5". However, my question is how can I do this by the query itself? That's is, how can I get the below table instead?

cod	charac1	charac2	items
I001	blue	solid	X1
I001	blue	solid	X2
I001	blue	solid	X3
I001	blue	solid	X5
I002	red	liquid	Y3
I002	red	liquid	Y4
I002	red	liquid	Y5
I002	red	liquid	Y6
I002	red	liquid	Y7
I002	red	liquid	Y10

I need this because the above result has hundreds of thousands and it may take some time to process it in client application.

Also, I did some search and I found a very good post Converting intervals of values into an IN-list for query filters that explains pretty much like I would like to have, but "only for one row". I don't know how I can apply it to my case. Do you have any directions to help me?

Note: the "items" column has always a letter and ";".

Edit1: the integers runs over 1-9999 interval. This can simplify the use of the tips given by 1.

Thank you all!

Answer 1

Here's one option; read comments within code.

SQL> with
  2  test (cod, charac1, charac2, items) as
  3  -- sample data
  4    (select 'I001', 'blue', 'solid', 'X;1-3,5'    from dual union all
  5     select 'I002', 'red' , 'liquid','Y;3-6,7,10' from dual
  6    ),
  7  temp as
  8  -- split ITEMS into rows separated by comma; extract starting and ending point of the span
  9    (select cod, charac1, charac2, items,
 10       substr(items, 1, instr(items, ';') - 1) item_char,
 11       to_number(regexp_substr(regexp_substr(substr(items, instr(items, ';') + 1), '[^,]+', 1, column_value), '^\d+')) item_span_from,
 12       to_number(regexp_substr(regexp_substr(substr(items, instr(items, ';') + 1), '[^,]+', 1, column_value), '\d+$')) item_span_to
 13     from test cross join
 14       table(cast(multiset(select level from dual
 15                           connect by level <= regexp_count(items, ',') + 1
 16                          ) as sys.odcinumberlist))
 17    )
 18  -- finally, concatenate item's character value to appropriate numeric value based on the span from previous step
 19  select cod, charac1, charac2,
 20    item_char || to_char(item_span_from + column_value - 1) as item
 21  from temp cross join
 22    table(cast(multiset(select level from dual
 23                        connect by level <= item_span_to - item_span_from + 1
 24                       ) as sys.odcinumberlist));

Result:

COD  CHARAC1    CHARAC2    ITEM
---- ---------- ---------- ----------
I001 blue       solid      X1
I001 blue       solid      X2
I001 blue       solid      X3
I001 blue       solid      X5
I002 red        liquid     Y3
I002 red        liquid     Y4
I002 red        liquid     Y5
I002 red        liquid     Y6
I002 red        liquid     Y7
I002 red        liquid     Y10

10 rows selected.

SQL>