Ashutosh Agrawal Ashutosh Agrawal - 6 months ago 15
SQL Question

Oracle substring table based on hierarchy

I have a string '1,2,3,4,5,6' and I need output in table format like

1,2,3,4,5,6

1,2,3,4,5

1,2,3,4

1,2,3

1,2

1

MT0 MT0
Answer

Query - Use a recursive sub-query factoring clause:

WITH table_name ( list ) AS (
  SELECT '1,2,3,4,5,6' FROM DUAL
),
rsqfc ( list ) AS (
  SELECT list FROM table_name
  UNION ALL
  SELECT SUBSTR( list, INSTR( list, ',', -1 ) -1 )
  FROM   rsqfc
  WHERE  INSTR( list, ',', -1 ) > 0
)
SELECT * FROM rsqfc;

Query - Hierarchical Query:

WITH table_name ( list ) AS (
  SELECT '1,2,3,4,5,6' FROM DUAL
)
SELECT CASE LEVEL
            WHEN 1 THEN list
            ELSE SUBSTR( list, 1, INSTR( list, ',', -1, LEVEL - 1 ) - 1 )
            END AS list
FROM   table_name
CONNECT BY INSTR( list, ',', -1, LEVEL - 1 ) > 0;

Output:

(Both output the same)

list
------------
1,2,3,4,5,6
1,2,3,4,5
1,2,3,4
1,2,3
1,2
1