[Query] SELECT시 자주 쓰는 컬럼 정보 변환

--시스템 날짜(DATETIME)를 년월일로 변환
  TO_CHAR(SYSDATE,'YYYYMMDD')

--시스템 날짜(DATETIME)를 시간을 24시간제로 변환
, TO_CHAR(SYSDATE,'HH24MI')      
 
--시스템 날짜 시간까지 변환
, TO_CHAR(SYSDATE,'yyyymmddhh24miss')
 
--주민번호에서 성별가져오기(여러 조건별 값 case when)
, DECODE(SUBSTR(A.JUMIN_NO,7,1),'1','M','2','F','3','M','4','F','M')
 
--201405 같은 날짜에서 한달 더하기
, TO_CHAR(ADD_MONTHS(TO_DATE(CLS_MTH || '01', 'yyyy-MM-dd'), 1), 'yyyyMM')

--해당일자에서 특정일수를 더한 결과를 보여준다.(예: 20160101->20160108)
,TO_CHAR(SYSDATE + 7, 'YYYYMMDD')

--특정일에서 오늘까지의 경과일(count)
, TO_DATE(TO_CHAR(SYSDATE, 'YYYY-MM-DD')) - to_date('2017-09-10') 
 
//주민번호별 나이 체크
,CASE WHEN SUBSTR(A.JUMIN_NO,1,6) IN ('111111','222222')
                THEN 0
                WHEN SUBSTR(A.JUMIN_NO,7,1) IN ('1','2','5','6')
                THEN TO_NUMBER(SUBSTR(A.REQ_DT,1,4)) - TO_NUMBER('19'||SUBSTR(A.JUMIN_NO,1,2)) + 1
                ELSE TO_NUMBER(SUBSTR(A.REQ_DT,1,4)) - TO_NUMBER('20'||SUBSTR(A.JUMIN_NO,1,2)) + 1
 
-- 1234561234567형태의 주민번호를 123456-1234567형태로 출력 아래 $ <- 없어도 되는 듯
, REGEXP_REPLACE( B.JUMIN_NO, '(\d{6})(\d{7})$', '\1-\2')
, REGEXP_REPLACE( A.REQ, '(\d{5})(\d{3})(\d{5})', '\1-\2-\3')

, REGEXP_REPLACE( STRDATE, '(\d{4})(\d{2})(\d{2})', '\1/\2/\3')  AS DT    ( 20140405 -> 2014/04/05)



--특정 문자열에서 짜르기
, REGEXP_SUBSTR('001,033,036','[^,]+')
 
--DATEDIFF
TO_DATE(A.RST_SCHE_DT,'YYYYMMDD') -  TO_DATE(Z.BASE_DAY,'YYYYMMDD') DEFF_DAY
 
--UPPER, LOWER
UPPER('aaaaaa')
LOWER('AAAAAA')
 
--TRIM
TRIM('RESMET    ')
LTRIM('    RESMET    ')
RTRIM('RESMET    ')
 
--숫자변환
TO_NUMBER(RESOHP)
 
--해당 위치 반환
INSTR(UPPER(RESGAL), 'OVER')  문자열이 나타나는 위치 반환
 
--숫자 나누기 나누는 수가 0이거나 null 일경우는 NULL 그게 아니면 계산하기
DECODE(B.NST_CNT, NULL, NULL, 0, NULL, ROUND(B.NST_AMT / B.NST_CNT)) XXX
from dual;
 
 
--문자 숫자인 데이터에서 숫자만 추출하기 sfsdfsdf33333 
,TRANSLATE('sfsdfsdf33333','1234567890.','XXXXXXXXXXX') NUMCHANGE --지정된 문자를 특정 문자로 변환
,REPLACE(TRANSLATE('sfsdfsdf33333','1234567890.','XXXXXXXXXXX'),'X') --문자열에서 일단 숫자를 제거
,REPLACE('sfsdfsdf33333', REPLACE(TRANSLATE('sfsdfsdf33333','1234567890.','XXXXXXXXXXX'),'X'),'') --문자를 제거
TO_NUMBER(RTRIM(LTRIM(REPLACE('sfsdfsdf33333', REPLACE(TRANSLATE('sfsdfsdf33333','1234567890.','XXXXXXXXXXX'),'X'),'') ))) --숫자로 변환
 
 
--Oracle 개행문자(\r\n) 제거하기
--chr(10) : Line Feed   <-- 줄바꾸기
--chr(13) : CR   <-- 행의 처음으로
, REPLACE(REPLACE(TEXT,CHR(10),''),CHR(13),'') TEXT 

--첫번째 컬럼값이 두번째 값이면 세번째 값을 반환하고 아니면 4번째 값을 반환한다. if~else
, DECODE(MR.COL, '01', 'Y', '') AS COL1

--1,2,3,4....등의 행별로 있는 번호가 필요할 때(예: 일괄채번시)
SELECT 
      ROWNUM
FROM tablename

--leading zero 앞에 특정 문자를 붙인다.    12548 -> 0012548
SELECT 
        LPAD('12548', 7, '0') --LEFT   0012548
,       RPAD('12548', 7, '0')--RIGHT  1254800
FROM DUAL