Oracle : SQL Query : Special Character, Substr

 

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--------------------