[Oracle] Standard Function

-- Standard Function

-- DUAL TABLE : 가상테이블 -> 결과용 테이블
SELECT 1 FROM DUAL;
SELECT 'A' FROM DUAL;
SELECT '가나' FROM DUAL;
SELECT 23 * 45 FROM DUAL;

-- 문자함수
-- CHR( 숫자 ) : ASCII 값으로 변환 -> 문자
SELECT CHR(65) FROM DUAL;
SELECT CHR(97) FROM DUAL;

-- String str = "안녕" + "하세요"
SELECT '내 점수는 ' || CHR(65) || '입니다' FROM DUAL;
-- CONCAT

-- LPAD(RPAD) : 나머지 빈칸(지정문자)로 채운다
SELECT LPAD('BBB', 10) FROM DUAL;
SELECT RPAD('BBB', 10) FROM DUAL;
SELECT LPAD('BBB', 10, '-') FROM DUAL;
SELECT RPAD('124', 10, '0') FROM DUAL;

-- INSTR == indexOf('a') -> 'abcde' -> 0
SELECT INSTR('abcde', 'a') FROM DUAL;
SELECT INSTR('123ABC456DEFABC', 'A') FROM DUAL;
SELECT INSTR('123ABC456DEFABC', 'A', 7) FROM DUAL;
SELECT INSTR('123ABC456DEFABCABC', 'A', 7, 1) FROM DUAL;
SELECT INSTR('123ABC456DEFABCABC', 'A', 7, 2) FROM DUAL;

-- REPLACE : 문자열 치환
SELECT REPLACE('AAAAABCD', 'A') FROM DUAL;
SELECT REPLACE('AAAAABCD', 'A', 'a') FROM DUAL;
SELECT REPLACE('AAAAABCD', 'AA', 'a') FROM DUAL;

-- TRANSLATE : 문자 치환
SELECT TRANSLATE('AAAAABCD', 'A', 'a') FROM DUAL;    -- 글자 지정해 주지 않으면 에러
SELECT TRANSLATE('AAAAABCD', 'AA', 'a') FROM DUAL;

-- SUBSTR   "ABCDE" -> substring(1, 3) ==> "BC"
SELECT SUBSTR('ABCDE', 3) FROM DUAL;
SELECT SUBSTR('ABCDE', 3, 2) FROM DUAL; -- "CD"


-- 숫자
-- 올림
SELECT CEIL(13.1) FROM DUAL;
-- 내림
SELECT FLOOR(13.9) FROM DUAL;

-- 나눈 나머지
SELECT MOD(3, 2) FROM DUAL;
-- 승수
SELECT POWER(3, 2) FROM DUAL;

-- 반올림
SELECT ROUND(13.5) FROM DUAL;

-- 부호 + -> 1 0 -> 0 - -> -1     정규화    100 0 -45 -> 1 0 -1
-- SIGN
SELECT SIGN(13.4) FROM DUAL;
SELECT SIGN(0) FROM DUAL;
SELECT SIGN(-0.4) FROM DUAL;

-- TRUNC 버림(소수점)
SELECT TRUNC(123.456) FROM DUAL;
SELECT TRUNC(123.456, 2) FROM DUAL;
SELECT TRUNC(123.456, 1) FROM DUAL;
SELECT TRUNC(123.456, -1) FROM DUAL;

-- TO_CHAR
-- DATE -> VARCHAR2
SELECT TO_CHAR(SYSDATE) FROM DUAL;
SELECT TO_CHAR(SYSDATE, 'YYYY-MM-DD HH-MI-SS') FROM DUAL;
SELECT TO_CHAR(SYSDATE, 'YYYY/MM/DD HH:MI:SS') FROM DUAL;

SELECT TO_CHAR(10000000, '$999,999,999') FROM DUAL;

-- TO_DATE
-- VARCHAR2 -> DATE
SELECT TO_DATE('20210525') FROM DUAL;
SELECT TO_DATE('20210525', 'YYYYMMDD') FROM DUAL;

-- TO_NUMBER    VARCHAR2 -> NUMBER
SELECT TO_NUMBER('123') + 45 FROM DUAL;

-- LAST_DAY
SELECT LAST_DAY('22/02/02') FROM DUAL;

SELECT LAST_DAY(TO_DATE('210301', 'YYMMDD'))
FROM DUAL;