Oracle DBMS를 기준으로 작성되었습니다. 다른 DBMS에서는 적용되지 않을 수도 있습니다!
1. 문자 함수
- UPPER(), LOWER(), INITCAP() : 대/소문자 변환
SELECT DISTINCT UPPER(JOB), LOWER(JOB), INITCAP(JOB) FROM EMP;
INITCAP() 함수는 첫 글자 대문자, 이후 글자는 소문자로 변환된다.
- LENGTH(), LENGTHB() : 문자 길이, 문자 바이트 수 반환
SELECT DISTINCT JOB, LENGTH(JOB), LENGTHB(JOB) FROM EMP;
영어는 한 문자당 1byte, 한글은 2byte로 처리된다.
- SUBSTR(문자열, 시작 위치, (추출 길이)) : 문자열 일부 추출
SELECT DISTINCT JOB, SUBSTR(JOB,3,2), SUBSTR(JOB,-3), SUBSTR(JOB, -LENGTH(JOB))
FROM EMP;
- INSTR(문자열, 찾는 문자열, (시작 위치), (찾는 문자열 횟수)) : 문자 위치 찾아 반환
SELECT DISTINCT(JOB),
INSTR(JOB, 'MAN'), INSTR(JOB, 'M', 3), INSTR(JOB, 'A', 1, 2)
FROM EMP
ORDER BY INSTR(JOB, 'MAN') DESC;
- REPLACE(문자열, 대체될 문자열, (대체할 문자열)) : 특정 문자 대체
SELECT HIREDATE,
REPLACE(HIREDATE, '/') AS REPLACE_1,
TO_CHAR(HIREDATE, 'YYMMDD') AS REPLACE_1_1,
REPLACE(HIREDATE, '/', '-') AS REPLACE_2,
REPLACE(REPLACE(HIREDATE, '/', '-'), '-', '/') AS REPLACE_3
FROM EMP
ORDER BY HIREDATE;
위 예시에서는 HIREDATE의 데이터 타입이 DATE이다. 따라서, REPLACE_1로 변환하는 과정에서 년도 데이터가 일부 소실된 것을 볼 수 있으며 날짜 데이터를 변환하는 TO_CHAR 함수를 사용할 수 있다.
- LPAD(문자열, 자릿수, (채울 문자열)), RPAD() : 빈 공간 대체
SELECT ENAME,
LPAD(ENAME, LENGTH(ENAME) + 5, 'NAME_'),
RPAD(ENAME, LENGTH(ENAME) + 5, '_NAME')
FROM EMP;
- CONCAT() : 두 문자열 결합
SELECT CONCAT(EMPNO, ENAME) AS CONCAT_1,
CONCAT(EMPNO, CONCAT(' : ',ENAME)) AS CONCAT_2
FROM EMP;
- TRIM((삭제 옵션) (삭제할 문자) FROM 원본 문자열) : 특정 문자열 삭제
- LTRIM(원본 문자열, (삭제할 문자)), RTRIM() : 특정 문자열 삭제
SELECT TRIM('_' FROM '__ORACLE__') AS TRIM,
TRIM(LEADING '_' FROM '__ORACLE__') AS TRIM_LEADING,
TRIM(TRAILING '_' FROM '__ORACLE__') AS TRIM_TRAILING,
LTRIM('__ORACLE__', '_') AS LTRIM,
RTRIM('__ORACLE__', '_') AS RTRIM
FROM DUAL;
TRIM에서 LEADING 삭제 옵션은 LTRIM, TRIM에서 TRAILING 삭제 옵션은 RTRIM으로 대체할 수 있다.
2. 숫자 함수
| 함수 | 설명 | 예시 |
|---|---|---|
| ROUND() | 반올림 | ROUND(3.141592,2) = 3.14, ROUND(314159.2,-2) = 314200 |
| TRUNC() | 버림 | TRUNC(3.141592,2) = 3.14, TRUNC(314159.2, -2) = 314100 |
| CEIL() | 가장 가까운 큰 정수 | CEIL(3.14) = 4 |
| FLOOR() | 가장 가까운 작은 정수 | CEIL(3.14) = 3 |
| MOD() | 나머지 | MOD(15,6) = 3 |
3. 날짜 함수
- SYSDATE : 현재 날짜
SELECT SYSDATE AS NOW, SYSDATE-1 AS YESTERDAY, SYSDATE+1 AS TOMORROW
FROM DUAL;
MySQL에서는 DATE()를 통해 현재 날짜를 출력!
- ADD_MONTHS(날짜, 더할 개월 수) : 몇 개월 이후의 날짜 반환
- MONTHS_BETWEEN() : 두 날짜의 개월 수 차이 반환
SELECT ENAME, HIREDATE, ADD_MONTHS(HIREDATE, 12*40) AS RETIREDATE,
TRUNC(MONTHS_BETWEEN(ADD_MONTHS(HIREDATE, 12*40), SYSDATE)) AS REMAIN_MONTHS
FROM EMP
WHERE SYSDATE BETWEEN ADD_MONTHS(HIREDATE, 12*30) AND ADD_MONTHS(HIREDATE, 12*40);
ADD_MONTHS 함수를 통해 30년 ~ 40년간 근무한 직원들만 추출한 후, 근무 시작일로부터 40년이 되는 날짜(퇴직일)와 MONTHS_BETWEEN 함수를 통해 퇴직일까지 남은 개월 수를 출력한 결과이다.
- TO_CHAR(날짜, 문자 형태) : 날짜 데이터를 문자 데이터로 변환
- TO_DATE(문자열, 날짜 형태) : 문자 데이터를 날짜 데이터로 변환
- TRUNC(날짜, 버림 포맷) : 날짜 버림
- NEXT_DAY(날짜, 요일) : 돌아오는 요일의 날짜 반환
- LAST_DAY(날짜) : 해당 달의 마지막 날짜 반환
SELECT SYSDATE,
TO_CHAR(SYSDATE, 'YY-MM-DD') AS NOW_CHAR,
TO_DATE(TO_CHAR(SYSDATE, 'YY-MM-DD'),'YY/MM/DD') AS NOW_DATE,
TRUNC(SYSDATE, 'Q') AS QUARTER_FIRST,
NEXT_DAY(TRUNC(SYSDATE, 'DD'), '월') AS NEXTDAY,
TO_CHAR(LAST_DAY(SYSDATE), 'YYYY-MM-DD') AS LASTDAY
FROM DUAL;
TRUNC 함수에서 'DD' 포맷으로 날짜를 버림하면 현재의 날짜가 반환된다. 'Q' 버림 포맷은 같은 분기의 첫 날짜를 반환한다. 기타 날짜 표현 형식은 iso 국제표준 날짜 형식에서 알 수 있다.
MySQL에서는 DATE_FORMAT()을 통해 날짜 형식 변환!
4. NULL 처리 함수
- NVL(검사할 데이터, 데이터가 NULL일 경우 반환할 데이터) : NULL 대체값 반환
SELECT ENAME, SAL, COMM, SAL+COMM, NVL(COMM, 0),
SAL+NVL(COMM,0) AS TOTAL
FROM EMP;
NVL 함수를 적용하지 않은 경우 연산이 되지 않으므로 COMM에서 NULL값이 나온 행은 NVL 함수를 통해 0으로 대체했다. SAL과 NVL 함수를 적용한 COMM을 더한 TOTAL을 최종 급여로 저장했다.
- NVL2(검사할 데이터, 데이터가 NULL이 아닐 경우 반환할 데이터, NULL일 경우 반환할 데이터) : NULL 대체값 + NULL이 아닐 때 대체값 반환
SELECT ENAME, COMM, NVL2(COMM,'O','X')
FROM EMP;
MySQL에서는 ISNULL(), MSSQL에서는 IFNULL() 사용!
5. 조건 함수
- DECODE(검사할 데이터,
조건1, 데이터가 조건1과 일치할 때 반환될 결과,
...
조건n, 데이터가 조건n과 일치할 때 반환될 결과,
조건1~조건n과 일치한 경우가 없을 때 반환될 결과)
- CASE 검사할 데이터
WHEN 조건1 THEN 데이터가 조건1과 일치할 때 반환될 결과
...
WHEN 조건n THEN 데이터가 조건n과 일치할 때 반환될 결과
ELSE 조건1~조건n과 일치한 경우가 없을 때 반환될 결과
END
SELECT ENAME, JOB, SAL,
DECODE(JOB,
'MANAGER', SAL*1.2,
'SALESMAN', SAL*1.1,
'CLERK', SAL*1.05,
SAL) AS UPSAL
FROM EMP
ORDER BY UPSAL DESC;
SELECT ENAME, JOB, SAL,
CASE JOB
WHEN 'MANAGER' THEN SAL*1.2
WHEN 'SALESMAN' THEN SAL*1.1
WHEN 'CLERK' THEN SAL*1.05
ELSE SAL
END AS UPSAL
FROM EMP
ORDER BY UPSAL DESC;
JOB이 MANAGER일 경우, SAL에 1.2를 곱한 값을 UPSAL에 저장한다. SALESMAN, CLERK도 마찬가지로 같은 동작을 수행하고, 기타 JOB 값들은 SAL을 그대로 반환해 UPSAL에 저장한다.
