SQL & PL/SQL

Hi all,

I am trying to extract attachment ids from each row of my table but somehow it retuning single value more than once.

For rownbr 1 there are two attachment ids but below query retuning second attachment id 3 times.

WITH Temp as (select 1 rownbr,'Source:123,attachmentId":"4678",Source:456,attachmentId":"4679",Name:32' Sdata from dual UNION ALL

                select 2 rownbr,'Source:321,attachmentId":"4680",Source:654,attachmentId":"4681",Name:33' from dual UNION ALL

                select 3 rownbr,'Source:987,attachmentId":"4682",Source:874,attachmentId":"4683",Name:34' from dual )

      select REGEXP_cOUNT(Sdata,'attachmentId'),regexp_substr(Sdata,'attachmentId[^,]*',1,level) output

  from  Temp

  CONNECT BY level<=regexp_count(Sdata,'attachmentId');

Expected result:

Output:

rownbr    output

1             4678

1            4679

2            4680

2            4681

3           4682

3           4683

Please help me out.

Thanks in advance.