1) How to remove Special Characters
"REPLACE(REPLACE(TRIM(REGEXP_REPLACE(:FREIGHT_TERMS_CODE,'[^a-z_A-Z ]')),CHR(11),''),CHR(13),'')"
2) How to remove last 2 character from String
substr( xpem.period_name,1,length(xpem.period_name)-2)
3) Query to get Sunday?
select
(next_day(last_day(trunc(sysdate)),'?')-7
-next_day(trunc(sysdate,'mm')-1,'?'))/7+1
as "sundays"
from dual;
SELECT TO_CHAR(dat,'DD-MON-RRRR')
FROM
(SELECT TRUNC(SYSDATE,'MM') + level - 1 dat FROM dual
connect by level <= LAST_DAY(TRUNC(SYSDATE)) - TRUNC(SYSDATE,'MM') + 1)
WHERE TO_CHAR(dat,'DY') = 'SUN'
GROUP BY TO_CHAR(dat,'MON-RRRR')
/* Formatted on 2018/04/06 15:18 (Formatter Plus v4.8.8) */
SELECT TRUNC (:p_to_dat, 'MM') + LEVEL - 1 dat,TO_CHAR (TRUNC (:p_to_dat, 'MM') + LEVEL - 1, 'DY') days,
CASE
WHEN TO_CHAR (TRUNC (:p_to_dat, 'MM') + LEVEL - 1, 'DY') =
'SUN'
THEN 'Holiday'
END abc
FROM DUAL
CONNECT BY LEVEL <= LAST_DAY (TRUNC (:p_to_dat)) - TRUNC (:p_to_dat, 'MM') + 1
------------------------------------------------------End Query to get sunday--------------------
