0

I want to assign a value to string column. Below is the column contains the leave and working day data of one month.

Holiday_list (Column name)
--------------------------------------------------
WHHHHHHHWHHHHHHHWHHHHHHHWHHHHH

I want to know what are all the dates are working day and holiday in the list.

Velmurugan Saravanan
  • 1
  • 1

2 Answers2

1

You can use a recursive sub-query factoring clause and SUBSTR:

WITH holidays ( column_name, dt, is_working, lvl ) AS (
  SELECT column_name, 
         TRUNC( SYSDATE, 'MM' ), -- Replace with the start date for your list
         SUBSTR( column_name, 1, 1 ),
         1
  FROM   holiday_list
UNION ALL
  SELECT column_name, 
         dt + INTERVAL '1' DAY,
         SUBSTR( column_name, lvl + 1, 1 ),
         lvl + 1
  FROM   holidays
  WHERE  lvl < LENGTH( column_name )
)
SELECT dt,
       is_working
FROM   holidays;

Which, for your sample data:

CREATE TABLE Holiday_list (Column_name) AS
SELECT 'WHHHHHHHWHHHHHHHWHHHHHHHWHHHHH' FROM DUAL;

Outputs:

DT                  | IS_WORKING
:------------------ | :---------
2021-03-01 00:00:00 | W         
2021-03-02 00:00:00 | H         
2021-03-03 00:00:00 | H         
2021-03-04 00:00:00 | H         
2021-03-05 00:00:00 | H         
2021-03-06 00:00:00 | H         
2021-03-07 00:00:00 | H         
2021-03-08 00:00:00 | H         
2021-03-09 00:00:00 | W         
2021-03-10 00:00:00 | H         
2021-03-11 00:00:00 | H         
2021-03-12 00:00:00 | H         
2021-03-13 00:00:00 | H         
2021-03-14 00:00:00 | H         
2021-03-15 00:00:00 | H         
2021-03-16 00:00:00 | H         
2021-03-17 00:00:00 | W         
2021-03-18 00:00:00 | H         
2021-03-19 00:00:00 | H         
2021-03-20 00:00:00 | H         
2021-03-21 00:00:00 | H         
2021-03-22 00:00:00 | H         
2021-03-23 00:00:00 | H         
2021-03-24 00:00:00 | H         
2021-03-25 00:00:00 | W         
2021-03-26 00:00:00 | H         
2021-03-27 00:00:00 | H         
2021-03-28 00:00:00 | H         
2021-03-29 00:00:00 | H         
2021-03-30 00:00:00 | H

Or, if you want them all in a single row:

SELECT SUBSTR( column_name,  1, 1 ) AS day01,
       SUBSTR( column_name,  2, 1 ) AS day02,
       SUBSTR( column_name,  3, 1 ) AS day03,
       SUBSTR( column_name,  4, 1 ) AS day04,
       SUBSTR( column_name,  5, 1 ) AS day05,
       SUBSTR( column_name,  6, 1 ) AS day06,
       SUBSTR( column_name,  7, 1 ) AS day07,
       SUBSTR( column_name,  8, 1 ) AS day08,
       SUBSTR( column_name,  9, 1 ) AS day09,
       SUBSTR( column_name, 10, 1 ) AS day10,
       SUBSTR( column_name, 11, 1 ) AS day11,
       SUBSTR( column_name, 12, 1 ) AS day12,
       SUBSTR( column_name, 13, 1 ) AS day13,
       SUBSTR( column_name, 14, 1 ) AS day14,
       SUBSTR( column_name, 15, 1 ) AS day15,
       SUBSTR( column_name, 16, 1 ) AS day16,
       SUBSTR( column_name, 17, 1 ) AS day17,
       SUBSTR( column_name, 18, 1 ) AS day18,
       SUBSTR( column_name, 19, 1 ) AS day19,
       SUBSTR( column_name, 20, 1 ) AS day20,
       SUBSTR( column_name, 21, 1 ) AS day21,
       SUBSTR( column_name, 22, 1 ) AS day22,
       SUBSTR( column_name, 23, 1 ) AS day23,
       SUBSTR( column_name, 24, 1 ) AS day24,
       SUBSTR( column_name, 25, 1 ) AS day25,
       SUBSTR( column_name, 26, 1 ) AS day26,
       SUBSTR( column_name, 27, 1 ) AS day27,
       SUBSTR( column_name, 28, 1 ) AS day28,
       SUBSTR( column_name, 29, 1 ) AS day29,
       SUBSTR( column_name, 30, 1 ) AS day30,
       SUBSTR( column_name, 31, 1 ) AS day31
FROM   holiday_list

Which outputs:

DAY01 | DAY02 | DAY03 | DAY04 | DAY05 | DAY06 | DAY07 | DAY08 | DAY09 | DAY10 | DAY11 | DAY12 | DAY13 | DAY14 | DAY15 | DAY16 | DAY17 | DAY18 | DAY19 | DAY20 | DAY21 | DAY22 | DAY23 | DAY24 | DAY25 | DAY26 | DAY27 | DAY28 | DAY29 | DAY30 | DAY31
:---- | :---- | :---- | :---- | :---- | :---- | :---- | :---- | :---- | :---- | :---- | :---- | :---- | :---- | :---- | :---- | :---- | :---- | :---- | :---- | :---- | :---- | :---- | :---- | :---- | :---- | :---- | :---- | :---- | :---- | :----
W     | H     | H     | H     | H     | H     | H     | H     | W     | H     | H     | H     | H     | H     | H     | H     | W     | H     | H     | H     | H     | H     | H     | H     | W     | H     | H     | H     | H     | H     | null

db<>fiddle here

MT0
  • 143,790
  • 11
  • 59
  • 117
0

If the only input is a string of 30 letters like you show in your Question, then it's meaningless. The first letter is W, but what date does it correspond to?

In the example below, I assume that your input has two columns - one shows "month" and the other the string of W and H as in your Question. I chose March 2021, on purpose, to illustrate an issue with your arrangement: the month has 31 days, but your string only has 30 letters. So, is March 31 a work day or a holiday?

The output of the query below is the same as in MT0's answer. The query itself is different. The with clause simulates your data (your table) - it is not part of the query you would run. Instead, the main select statement would reference your actual table and columns in that table.

with holidays as (
  select 'March 2021' as mth, 'WHHHHHHHWHHHHHHHWHHHHHHHWHHHHH' as holiday_list
  from   dual
)
select  to_date(mth, 'Month yyyy', 'nls_date_language=English') + level - 1 as dt,
        substr(holiday_list, level, 1) as is_working_day
from    holidays
connect by level <= length(holiday_list)
;