I have a table. It contains 2 columns (id,value). Value columns holds date and status.(a:active,s:suspend,d:deactive)

id | value
1  | 220325a|220608s
2  | 220325a|220325d|220822a|220822d
3  | 220325a
4  | 220325a|220329d

What i want to do is to parse value columns like that

My new table :

 id | start_date | end_date
 1  | 25.03.2022 | 08.06.2022
 2  | 25.03.2022 | 25.03.2022
 2  | 22.08.2022 | 22.08.2022 
 3  | 25.03.2022
 4  | 25.03.2022 | 29.03.2022

I think I have to use nested loop but how do i find dates one by one?

CodePudding user response：

You can use a recursive query and simple string functions:

WITH bounds (id, value, spos, mpos, epos) AS (
  SELECT id,
         value,
         1,
         INSTR(value, '|', 1, 1),
         INSTR(value, '|', 1, 2)
  FROM   table_name
UNION ALL
  SELECT id,
         value,
         epos   1,
         INSTR(value, '|', epos   1, 1),
         INSTR(value, '|', epos   1, 2)
  FROM   bounds
  WHERE  epos > 0
)
SEARCH DEPTH FIRST BY id SET order_id
SELECT id,
       TO_DATE(SUBSTR(value, spos, 6), 'RRMMDD') AS start_date,
       CASE 
       WHEN mpos = 0
       THEN NULL
       ELSE TO_DATE(SUBSTR(value, mpos   1, 6), 'RRMMDD')
       END AS end_date,
       SUBSTR(value, spos   6, 1) AS start_status,
       CASE 
       WHEN mpos = 0
       THEN NULL
       ELSE SUBSTR(value, mpos   7, 1)
       END AS end_status
FROM   bounds;  

Which, for the sample data:

CREATE TABLE table_name (id, value) AS
SELECT 1, '220325a|220608s' FROM DUAL UNION ALL
SELECT 2, '220325a|220325d|220822a|220822d' FROM DUAL UNION ALL
SELECT 3, '220325a' FROM DUAL UNION ALL
SELECT 4, '220325a|220329d' FROM DUAL;

Outputs:

fiddle