SQL Oracle - Fungsi Character

kali ini saya akan memberikan list dari fungsi yang di sediakan oleh oracle untuk memanipulasi Character ...

Function	Description	Example	Result
regexp_count	menghitung Jumlah Karakter	select regexp_count( 'belajaroracle', 'a' ) from dual	3
CONCAT	menggabungkan 2 karakter atau column	SELECT CONCAT('Mike ', 'Hunt') FROM dual;	Mike Hunt
SUBSTR,INSTR	mengambil character sebelum special character	SELECT NVL (SUBSTR ('ABC_BLAH', 0, INSTR ('ABC_BLAH', '_') - 1), 'ABC_BLAH') TES_SUBSTR1,        NVL (SUBSTR ('123434-4', 0, INSTR ('123434-4', '-') - 1), '123434-4') TES_SUBSTR2   FROM DUAL	ABC, 123434

Format Date 

-- Date and Time Format Model 
-- source : http://docs.oracle.com/cd/B12037_01/server.101/b10759/sql_elements004.htm#sthref478

-- blog reference for Date Function http://erporacleapps.blogspot.com/2013/04/how-to-work-with-date-functions.html

DATE AND TIME
SELECT TO_DATE('1998 05 20','YYYY MM DD') TES_DATE FROM DUAL
Output : 20/05/1998

select to_date (to_char ('01-JAN-16') || ' 23:59:59', 'DD-MON-RR HH24:MI:SS')  concat_date_with_time from dual;
Output  : 01/01/2016 23.59.59

SELECT TO_CHAR(TO_DATE('27-OCT-18', 'DD-MON-RR') ,'fmDay Month YYYY') as tesDate FROM DUAL;
Output : Saturday October 2018

NEXT_DAY 
select next_day('04-Dec-19','Wed') from dual;  -- 11/12/2019
select  to_char ( add_months( trunc(sysdate,'y'), level-1 ), 'Month' ) IN_Month , 
       add_months( trunc(sysdate,'y'), level-1 ) open_period,  
       last_day ( add_months( trunc(sysdate,'y'), level-1 ) )  close_period
       from dual 
connect by level <= 12;

Output : 

+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++