connect by level scenario in Db2

Question

I have a scenario in DB2 where I have a column value as

select value from table where id='02346'

value

1,2,3,4,5,6,7

I want to convert this above value as below using DB2 query.

1
2
3
4
5
6
7

How can I achieve this? i

Using oracle its done as below. with test as (select '421907802490;421907672085;421911460415;421905464170;421907802292' col from dual) select regexp_substr(col, '[^;]+', 1, level) result from test connect by level <= length(regexp_replace(col, '[^;]+')) + 1;

Answer 1

Run it as is.

select x.tok
from 
-- uncomment this and comment out "(values ...) t (id, value)"
-- table t
(
 values ('02346', '1,2,3,4,5,6,7')
) t (id, value)
, xmltable
(
  'for $id in tokenize($s, ",") return <i>{string($id)}</i>' 
  passing 
    t.value as "s"
  columns 
    tok varchar(4000) path '.'
) x
where t.id = '02346'

Update
If you are not at Db2 for LUW, you may use the same RCTE approach:

with 
-- Uncomment to run as is
/*
mytab (id, value) as 
(
  select '02346', '1,2,3,4,5,6,7' from sysibm.sysdummy1
),
*/ 
cte (tok, value) as 
(
  select 
    substr (value, 1, coalesce (nullif (locate (',', value) - 1, -1), length (value)))
  , substr (value, nullif (locate (',', value) + 1, 1))
  from mytab
  where id = '02346' and length (value) <> 0
    union all 
  select 
    substr (value, 1, coalesce (nullif (locate (',', value) - 1, -1), length (value)))
  , substr (value, nullif (locate (',', value) + 1, 1))
  from cte
  where value is not null
)
select tok
from cte

The result is the same:

TOK
1
2
3
4
5
6
7

fiddle