I am trying to split a string "HHHWWWHHHHWWWWWHHWWWWWHHWWWWWHH" is there any possibility to make like :

H
H
H
W
W
W

BRANCH_CODE  YEAR   MONTH   HOLIDAY_LIST
1   001      2021   1        HHHWWWHHHHWWWWWHHWWWWWHHWWWWWHH
2   001      2021   2        WWWWWHHWWWWWHHWWWWWHHWHWWWHH

CodePudding user response：

From Oracle 12, you can use:

SELECT branch_code, year, month, day, holiday
FROM   branches
       CROSS JOIN LATERAL (
         SELECT LEVEL AS day,
                SUBSTR(holiday_list, LEVEL, 1) AS holiday
         FROM   DUAL
         CONNECT BY LEVEL <= LENGTH(holiday_list)
       )

Which, for the sample data:

CREATE TABLE branches (BRANCH_CODE, YEAR, MONTH, HOLIDAY_LIST) AS
SELECT '001', 2021, 1, 'HHHWWWHHHHWWWWWHHWWWWWHHWWWWWHH' FROM DUAL UNION ALL
SELECT '001', 2021, 2, 'WWWWWHHWWWWWHHWWWWWHHWHWWWHH'    FROM DUAL

Outputs:

BRANCH_CODE	YEAR	MONTH	DAY	HOLIDAY
001	2021	1	1	H
001	2021	1	2	H
001	2021	1	3	H
001	2021	1	4	W
...	...	...	...	...
001	2021	1	29	W
001	2021	1	30	H
001	2021	1	31	H
001	2021	2	1	W
001	2021	2	2	W
001	2021	2	3	W
...	...	...	...	...
001	2021	2	26	W
001	2021	2	27	H
001	2021	2	28	H

db<>fiddle here

CodePudding user response：

If it's Oracle:

with data AS (
select 'WWWWWHHWWWWWHHWWWWWHHWHWWWHH' AS letters 
from dual
)
select substr (
           letters,
           level,
           1
         ) value
from data
connect by level <= 
    length ( letters )