여러 함수 작성해보기(SUBSTR, NEXT_DAY, ADD

여러 함수 작성해보기(SUBSTR, NEXT_DAY, ADD_MONTHS...)

Ch's Lee 2025. 6. 18. 15:02

SELECT FIRST_NAME AS BEFORE
, REPLACE(FIRST_NAME, 'E', 'A') AS AFTER
, REGEXP_REPLACE(FIRST_NAME, 'E|e', 'A') AS AFTER
, REPLACE(FIRST_NAME, 'e', ' ') AS AFTER
FROM EMPLOYEES;

SELECT REPLACE('이충현', SUBSTR('이충현', 2, 1), '*') AS 이름
, RPAD('900310-', 15, '*') AS 주민번호
, RPAD('010-5059-', 13, '*') AS 휴대폰
FROM DUAL;

SELECT *
FROM EMPLOYEES;

SELECT FIRST_NAME || ' ' || LAST_NAME AS 이름
, TO_CHAR(HIRE_DATE, 'YYYY"년"MM"월"DD"일"')
FROM EMPLOYEES
WHERE TO_CHAR(HIRE_DATE, 'YYYY') LIKE '2002'
OR TO_CHAR(HIRE_DATE, 'YYYY') LIKE '2004';

SELECT FIRST_NAME || ' ' || LAST_NAME AS 이름
, LPAD(SUBSTR(HIRE_DATE, 1, 2), 4,'20') || '년' ||
REPLACE(SUBSTR(HIRE_DATE, 3, 4),'/','') || '월' ||
SUBSTR(HIRE_DATE, 7, 8) || '일' AS 년도
, HIRE_DATE
FROM EMPLOYEES
WHERE SUBSTR(HIRE_DATE, 1, 2) IN ('02', '04');
--WHERE SUBSTR(HIRE_DATE, 1, 2) LIKE '02' -- 02/01/24
--OR SUBSTR(HIRE_DATE, 1, 2) LIKE '04';

SELECT TRIM(' TEST ')
, LENGTH(' TEST ')
, LENGTH(TRIM(' TEST '))
, RTRIM(' TEST ')
, RTRIM(' TESTAA', 'A')
FROM DUAL;

-- -3-2-1 0 1 2 3 4
-- 1 2 3 4 . 5 6 7 8
-- 해당 자리수의 둿 부분이 5이상이면 올림
-- 단, TRUNC는 다르다. 그냥 버림

SELECT
-- ROUND(1234.5678)
-- , ROUND(1234.5678, 1)
-- , ROUND(1234.5678, 0)
-- , ROUND(1234.5678, -1)
CEIL(3.67) -- 양수이면, 올림
, CEIL(3.14) -- 음수이면, 그냥둠
, FLOOR(-3.77) -- 음수이면, 내림
, FLOOR(-3.14) -- 양수이면, 그냥둠
-- , TRUNC(1234.5678)
-- , TRUNC(1234.5678, 1)
-- , TRUNC(1234.5678, 0)
-- , TRUNC(1234.5678, -1)
FROM EMPLOYEES
WHERE COMMISSION_PCT IS NOT NUll;

SELECT MOD(4,2) AS 나머지
, 4/2 AS 몫
FROM DUAL;

SELECT ROUND(MONTHS_BETWEEN(sysdate, '2025-05-20'),0) AS "개강 후 몇개월"
, ROUND(MONTHS_BETWEEN('2025-11-26', '2025-05-20'),0) AS "개강 후 몇개월"
, ROUND(TO_DATE('2025-11-26') - SYSDATE, 0) AS "남은 수업일수"
, ROUND(TO_DATE('2025-11-26') - TO_DATE('2025-05-20')) AS "전체 수업일수"
FROM DUAL;

-- 월은 일이 16이상이면, 앞 월을 반올림
-- 일은 시간이 12이상이면, 앞 일을 반올림

SELECT SYSDATE AS 오늘
, ADD_MONTHS(SYSDATE, 5) AS "5개월후"
, ROUND(TO_DATE('2025-02-15'), 'DAY')
, ROUND(TO_DATE('2025-02-16'), 'MONTH')
, ROUND(TO_DATE('2025-02-15'), 'MONTH')
, ROUND(TO_DATE('2025-02-15 12:30:30', 'YYYY-MM_DD HH24:MI:SS'))
FROM DUAL;

-- 1이 일요일

SELECT NEXT_DAY(SYSDATE, '일요일')
, NEXT_DAY(SYSDATE, 1)
FROM DUAL;