I wasn’t sure how to title this question, but I’ve got an Oracle table that has a column for each day of the week. These represent the days a business is open. The values in these columns is either ‘Y’ or ‘N’. This is how the table was setup years ago and I am unable to change it.
ID | MONDAY | TUESDAY | WEDNESDAY | THURSDAY | FRIDAY | SATURDAY | SUNDAY 1 Y Y Y Y Y N N 2 N Y Y Y Y Y N
I need to be able to query this table and get results like the following, but am not sure where to even start.
ID | DAYS_OPEN 1 MONDAY - FRIDAY 2 TUESDAY - SATURDAY
Is this even possible in a query or will I need to use a function? If a function would make it easier, I’d be fine with that solution as well.
Advertisement
Answer
Found easier solution:
replace(
trim(
'.' from
replace(replace(replace(replace(replace(replace(replace(
regexp_replace(
decode(MONDAY ,'Y','1','.') ||
decode(TUESDAY ,'Y','2','.') ||
decode(WEDNESDAY ,'Y','3','.') ||
decode(THURSDAY ,'Y','4','.') ||
decode(FRIDAY ,'Y','5','.') ||
decode(SATURDAY ,'Y','6','.') ||
decode(SUNDAY ,'Y','7','.')
,'(d)d*(d)','1-2')
,'1','MONDAY')
,'2','TUESDAY')
,'3','WEDNESDAY')
,'4','THURSDAY')
,'5','FRIDAY')
,'6','SATURDAY')
,'7','SUNDAY')
),'.',',')
Full example:
with t(ID, MONDAY, TUESDAY, WEDNESDAY, THURSDAY, FRIDAY, SATURDAY, SUNDAY) as (
select 1,'Y', 'Y', 'Y', 'Y', 'Y' ,'N', 'N' from dual union all
select 2,'N', 'Y', 'Y', 'Y', 'Y' ,'Y', 'N' from dual union all
select 3,'Y', 'N', 'Y', 'Y', 'N' ,'Y', 'Y' from dual
)
select
id
,replace(
trim(
'.' from
replace(replace(replace(replace(replace(replace(replace(
regexp_replace(
decode(MONDAY ,'Y','1','.') ||
decode(TUESDAY ,'Y','2','.') ||
decode(WEDNESDAY ,'Y','3','.') ||
decode(THURSDAY ,'Y','4','.') ||
decode(FRIDAY ,'Y','5','.') ||
decode(SATURDAY ,'Y','6','.') ||
decode(SUNDAY ,'Y','7','.')
,'(d)d*(d)','1-2')
,'1','MONDAY')
,'2','TUESDAY')
,'3','WEDNESDAY')
,'4','THURSDAY')
,'5','FRIDAY')
,'6','SATURDAY')
,'7','SUNDAY')
),'.',',')
as sch
from t;
Result:
ID SCH
---------- ------------------------------------------------------------
1 MONDAY-FRIDAY
2 TUESDAY-SATURDAY
3 MONDAY,WEDNESDAY-THURSDAY,SATURDAY-SUNDAY
Edit: expanding on the original answer to apply the same process to months of the year. I had to wrap the month number in square brackets in the decode. Then in the regexp_replace, I specified a non-matching list (period and whitespace).
Here’s the full example:
with t(ID, JANUARY, FEBRUARY, MARCH, APRIL, MAY, JUNE, JULY, AUGUST, SEPTEMBER, OCTOBER, NOVEMBER, DECEMBER ) as (
select 1,'Y', 'Y', 'Y', 'Y', 'Y' ,'N', 'N', 'Y','Y','Y','Y','Y' from dual union all
select 2,'Y', 'Y', 'Y', 'Y', 'N' ,'N', 'N', 'N','Y','Y','Y','Y' from dual union all
select 3,'N', 'N', 'Y', 'N', 'N' ,'Y', 'Y', 'N','N','N','Y','N' from dual union all
select 4,'Y', 'N', 'Y', 'N', 'Y' ,'N', 'Y', 'N','Y','N','Y','N' from dual
)
select
id,
regexp_replace(
trim( '.' from
replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(
regexp_replace(
decode(JANUARY ,'Y','01','.') ||
decode(FEBRUARY ,'Y','02','.') ||
decode(MARCH ,'Y','03','.') ||
decode(APRIL ,'Y','04','.') ||
decode(MAY ,'Y','05','.') ||
decode(JUNE ,'Y','06','.') ||
decode(JULY ,'Y','07','.') ||
decode(AUGUST ,'Y','08','.')||
decode(SEPTEMBER ,'Y','09','.')||
decode(OCTOBER ,'Y','10','.')||
decode(NOVEMBER ,'Y','11','.')||
decode(DECEMBER ,'Y','12','.')
,'(dd)d*(dd)','1-2')
,'01','Jan')
,'02','Feb')
,'03','Mar')
,'04','Apr')
,'05','May')
,'06','Jun')
,'07','Jul')
,'08','Aug')
,'09','Sep')
,'10','Oct')
,'11','Nov')
,'12','Dec')
)
,'.+',', ') MNTHS
from t;
and the results:
ID MNTHS 1 Jan-May, Aug-Dec 2 Jan-Apr, Sep-Dec 3 Mar, Jun-Jul, Nov 4 Jan, Mar, May, Jul, Sep, Nov