Oracle 단일행 함수

1. 문자 함수

함수	설명
UPPER(문자열) LOWER(문자열)	UPPER는 문자 데이터를 모두 대문자로 변환하여 반환하고, LOWER는 소문자로 변환하여 반환한다.
INITCAP(문자열)	문자 데이터 중 첫 글자는 대문자로, 나머지 문자는 소문자로 변환하여 반환한다.
LENGTH(문자열)	특정 문자열의 길이를 구할 때 사용한다.
LENGTHB(문자열)	특정 문자열의 바이트 수를 구할 때 사용한다.
SUBSTR(문자열, 시작 위치, 추출 길이)	- 문자열 데이터의 시작 위치부터 추출 길이만큼 추출한다. - 추출 길이를 생략할 경우 문자열 데이터 끝까지 추출한다.
INSTR(문자열, 찾으려는 문자, 시작 위치, 찾으려는 문자 순서)	- 문자열 데이터 안에 특정 문자나 문자열이 어디에 포함되어 있는지 확인할 때 사용한다. - 시작 위치를 생략할 경우 기본값 1이 적용된다. - 찾으려는 문자 순서를 생략할 경우 기본값 1이 적용된다.
REPLACE(문자열, 찾는 문자, 대체할 문자)	- 문자열 데이터에 포함된 문자를 다른 문자로 대체할 때 사용한다. - 대체할 문자를 생략할 경우 찾는 문자로 지정한 문자는 문자열에서 삭제된다.
LPAD(문자열, 자릿수, 빈 공간에 채울 문자) RPAD(문자열, 자릿수, 빈 공간에 채울 문자)	- 데이터 길이가 지정한 자릿수보다 작을 경우 나머지 공간을 특정 문자로 채운다. - LPAD는 남은 빈 공간을 왼쪽에 채우고, RPAD는 오른쪽에 채운다. - 빈 공간에 채울 문자를 지정하지 않으면, 빈 공간의 자릿수만큼 공백 문자로 처리된다.
CONCAT(문자열, 문자열)	두 개의 문자열 데이터를 하나의 데이터로 연결한다.
TRIM([삭제 옵션] [삭제할 문자] FROM 문자열)	- 문자열 데이터 내에서 양쪽 공백을 제거하기 위해 사용한다. 특정 문자를 지울 수도 있다. - LEADING: 왼쪽에 있는 글자를 지우는 삭제 옵션이다. - TRAILING: 오른쪽에 있는 글자를 지우는 삭제 옵션이다. - BOTH: 양쪽의 글자를 모두 지우는 삭제 옵션이다. - 삭제 옵션은 생략할 수 있다. 생략할 경우 BOTH로 자동 설정된다. - 삭제할 문자는 한 개만 작성할 수 있다. - 삭제할 문자가 생략될 경우, 공백을 제거한다. - 삭제 옵션과 삭제할 문자를 모두 생략할 경우 FROM 또한 생략해야 한다.
LTRIM(문자열, 삭제할 문자) RTRIM(문자열, 삭제할 문자)	- LTRIM은 왼쪽, RTRIM은 오른쪽의 지정 문자를 삭제한다. - 삭제할 문자를 지정하지 않을 경우 공백 문자가 삭제된다. - 삭제할 문자가 여러 개 올 수 있다.

/* UPPER, LOWER 함수 예시 */
SELECT UPPER(ENAME), LOWER(ENAME)
FROM EMP;

/* INITCAP 함수 예시 */
SELECT INITCAP(ENAME)
FROM EMP;

/* LENGTH 함수 예시 */
SELECT ENAME
FROM EMP
WHERE LENGTH(ENAME) >= 5;

/* LENGTHB 함수 예시 */
SELECT LENGTHB(ENAME)
FROM EMP;

/* SUBSTR 함수 예시 */
SELECT SUBSTR(JOB, 3, 2)
FROM EMP;

/* INSTR 함수 예시 */
SELECT INSTR('HELLO!', 'L', 2, 2)
FROM DUAL;

/* REPLACE 함수 예시 */
SELECT REPLACE('010-1234-5678', '-', ' ')
FROM DUAL;

/* LPAD, RPAD 함수 예시 */
SELECT LPAD('HELLO', 10, '*'), RPAD('HELLO', 10)
FROM DUAL;

/* CONCAT 함수 예시 */
SELECT CONCAT(EMPNO, CONCAT(' : ', ENAME))
FROM EMP;

/* TRIM 함수 예시1 */
SELECT TRIM(' _HELLO_ '), TRIM(LEADING, ' _HELLO_ ')
FROM DUAL;

/* TRIM 함수 예시2 */
SELECT TRIM('_' FROM '_HELLO_'), TRIM(BOTH '_' FROM '_HELLO_')
FROM DUAL;

/* LTRIM, RTRIM 함수 예시 */
SELECT LTRIM(' _HELLO_ '), RTRIM('<_HELLO_>', '_<')
FROM DUAL;

 

1.1 || 연산자

- CONCAT 함수와 유사하게 열이나 문자열을 연결한다.

/* 예시 */
SELECT EMPNO || ' : ' || ENAME
FROM EMP;

 

2. 숫자 함수

함수	설명
ROUND(숫자, 반올림 위치)	- 특정 숫자를 반올림한다. - 반올림 위치를 설정하지 않으면 소수점 첫 째 자리에서 반올림한다. 반올림 위치를 0으로 설정한 것과 같다. - 반올림 위치가 음수로 내려가면 자연수 쪽으로 한 자리씩 위로 반올림하게 된다.
TRUNC(숫자, 버림 위치)	- 특정 숫자를 버림한다. - 버림 위치를 설정하지 않으면 소수점 첫 째 자리에서 버림한다. 버림 위치를 0으로 설정한 것과 같다.
CEIL(숫자) FLOOR(숫자)	CEIL 함수는 입력된 숫자와 가장 가까운 큰 정수를 반환하고, FLOOR 함수는 가장 가까운 작은 정수를 반환한다.
MOD(숫자, 나눌 숫자)	특정 숫자를 나누고 그 나머지를 출력한다.

/* ROUND 함수 예시 */
SELECT ROUND(123.456, -2)
FROM DUAL;

/* TRUNC 함수 예시 */
SELECT TRUNC(123.456, -1)
FROM DUAL;

/* CEIL, FLOOR 함수 예시 */
SELECT CEIL(3.14), FLOOR(-3.14)
FROM DUAL;

/* MOD 함수 예시 */
SELECT MOD(11, 2)
FROM DUAL;

 

3. 날짜 함수

함수	설명
ADD_MONTHS(날짜 데이터, 더할 개월 수)	특정 날짜 데이터에 입력한 개월 수만큼의 이후 날짜를 출력한다.
MONTHS_BETWEEN(날짜 데이터, 날짜 데이터)	두 날짜 간의 개월 수 차이를 구한다.
NEXT_DAY(날짜 데이터, 요일 문자)	특정 날짜를 기준으로 돌아오는 요일의 날짜를 출력한다.
LAST_DAY(날짜 데이터)	특정 날자가 속한 달의 마지막 날짜를 출력한다.
ROUND(날짜 데이터, 반올림 기준 포맷) TRUNC(날짜 데이터, 버림 기준 포맷)	포맷을 기준으로 반올림과 버림을 적용한다.

/* ADD_MONTHS 함수 예시 */
SELECT ENAME
FROM EMP
WHERE ADD_MONTHS(HIREDATE, 120) > SYSDATE;

/* MONTHS_BETWEEN 함수 예시 */
SELECT TRUNC(MONTHS_BETWEEN(SYSDATE, HIREDATE))
FROM EMP;

/* NEXT_DAY 함수 예시 */
SELECT NEXT_DAY(SYSDATE, '월요일')
FROM DUAL;

/* LAST_DAY 함수 예시 */
SELECT LAST_DAY(SYSDATE)
FROM DUAL;

/* ROUND, TRUNC 함수 예시 */
SELECT ROUND(SYSDATE, 'Q'), TRUNC(SYSDATE, 'CC')
FROM DUAL;

 

3.1 날짜 데이터 연산

연산	설명
날짜 데이터 + 숫자	날짜 데이터보다 숫자 일 수 이후의 날짜로 연산된다.
날짜 데이터 - 숫자	날짜 데이터보다 숫자 일 수 이후의 날짜로 연산된다.
날짜 데이터 + 날짜 데이터	두 날짜 데이터 간의 일수 차이로 연산된다.
날짜 데이터 - 날짜 데이터	지원하지 않는 연산이다.

/* 날짜 데이터 + 숫자 연산 예시 */
SELECT SYSDATE +3
FROM DUAL;

/* 날짜 데이터 - 숫자 연산 예시 */
SELECT SYSDATE -3
FROM DUAL;

 

3.2 날짜 데이터 기준 포맷

포맷 모델	기준 단위
CC, SCC	네 자리 연도의 끝 두 자리를 기준으로 사용한다.
SYYYY, YYYY, YEAR, SYEAR, YYY, YY, Y	날짜 데이터의 해당 연, 월, 일의 7월 1일을 기준으로 사용한다.
IYYY, IYY, IY, I	ISO 8601에서 제정한 날짜 기준년도 포맷을 기준으로 사용한다.
Q	각 분기의 두 번째 달의 16일을 기준으로 사용한다.
MONTH, MON, MM, RM	각 달의 16일을 기준으로 사용한다.
WW	해당 연도의 몇 주(1~53번째 주)를 기준으로 사용한다.
IW	ISO 8601에서 제정한 날짜 기준 해당 연도의 주를 기준으로 사용한다.
W	해당 월의 주(1~5번째 주)를 기준으로 사용한다.
DDD, DD, J	해당 일의 정오를 기준으로 사용한다.
DAY, DY, D	한 주가 시작되는 날짜를 기준으로 사용한다.
HH, HH12, HH24	해당일의 시간을 기준으로 사용한다.
MI	해당일 시간의 분을 기준으로 사용한다.

 

4. 형 변환 함수

형 변환 함수

함수	설명
TO_CHAR(날짜 데이터, 원하는 문자 형태, 'NLS_DATE_LANGUAGE = language')	- 날짜 데이터를 원하는 형태의 문자열로 출력한다. - NLS_DATE_LANGUAGE를 사용하면 특정 언어에 맞게 출력할 수 있다. 필수 설정은 아니다.
TO_NUMBER(문자열 데이터, 원하는 숫자 형태)	문자열을 지정한 형태의 숫자로 인식하여 숫자 데이터로 변환한다.
TO_DATE(문자열 데이터, 원하는 날짜 형태)	문자열 데이터를 날짜 데이터로 변환한다.

/* TO_CHAR 함수 예시1 */
SELECT TO_CHAR(SYSDATE, 'YYYY/MM/DD HH12:MI:SS A.M.')
FROM DUAL;

/* TO_CHAR 함수 예시2 */
SELECT TO_CHAR(SYSDATE, 'MON', 'NLS_DATE_LANGUAGE = ENGLISH')
FROM DUAL;

/* TO_CHAR 함수 예시3 */
SELECT TO_CHAR(SAL, '999,999.00')
FROM EMP;

/* TO_NUMBER 함수 예시 */
SELECT TO_NUMBER('1,500', '999,999')
FROM DUAL;

/* TO_DATE 함수 예시 */
SELECT *
FROM EMP
WHERE HIREDATE > TO_DATE('1980/1/1', 'YYYY/MM/DD');

 

4.1 날짜 표현 형식

형식	설명
CC	세기
YYYY, RRRR	연(4자리 숫자)
YY, RR	- 연(2자리 숫자) - YY는 어떤 두 자리 수가 입력되어도 현 시점의 연도와 동일한 연도로 계산한다. - RR은 현 시점 혹은 입력된 연도의 끝 자리 수가 00~49, 50~99인 경우를 계산하여 비교적 가까운 날짜 데이터를 계산한다.
MM	월(2자리 숫자)
MON	월(언어별 월 이름 약자)
MONTH	월(언어별 월 이름 전체)
DD	일(2자리 숫자)
DDD	1년 중 며칠(1~366)
DY	요일(언어별 요일 이름 약자)
DAY	요일(언어별 요일 이름 전체)
W	1년 중 몇 번째 주(1~53)

 

4.2 시간 표현 형식

형식	설명
HH24	24시간으로 표현한 시간
HH, HH12	12시간으로 표현한 시간
MI	분
SS	초
AM, PM, A.M., P.M.	오전, 오후 표시

 

4.3 숫자 데이터 형식

형식	설명
9	숫자의 한 자리를 의미한다. 빈 자리를 채우지 않는다.
0	빈 자리를 0으로 채운다.
$	달러($) 표시를 붙여서 출력한다.
L	L(Locale) 지역 화폐 단위 기호를 붙여서 출력한다.
.	소수점을 표시한다.
,	천 단위의 구분 기호를 표시한다.

 

5. NULL 처리 함수

함수	설명
NVL(데이터, NULL일 경우 반환할 데이터)	데이터가 NULL이 아닐 경우 데이터를 그대로 반환하고, NULL인 경우 지정한 데이터를 반환한다.
NVL2(데이터, NULL이 아닐 경우 반환할 데이터, NULL일 경우 반환할 데이터)	데이터가 NULL이 아닐 때와 NULL일 때 출력 데이터를 각각 지정한다.

/* NVL 함수 예시 */
SELECT SAL+NVL(COMM, 0)
FROM EMP;

/* NVL2 함수 예시 */
SELECT NVL2(COMM, 'O', 'X'), NVL2(COMM, SAL*12+COMM, SAL*12)
FROM EMP;

 

6. DECODE 함수와 CASE문

/* DECODE 함수 */
DECODE(데이터,
[조건1], [데이터=조건1일 경우 반환할 결과],
...
[조건n], [데이터=조건n일 경우 반환할 결과],
일치하는 경우가 없을 때 반환할 결과)

/* CASE문 */
CASE 데이터
WHEN 조건1 THEN 조건1=TRUE일 경우 반환할 결과
...
WHEN 조건n THEN 조건n=TRUE일 경우 반환할 결과
ELSE 일치하는 경우가 없을 때 반환할 결과
END

함수	설명
DECODE	기준이 되는 데이터를 먼저 지정한 후 해당 데이터 값에 따라 다른 결과 값을 반환한다.
CASE 문	- 특정 조건에 따라 반환할 데이터를 설정할 때 사용한다. - DECODE 함수와 달리, 기준 데이터가 없어도 되고 같은 조건 이외의 조건 또한 사용 가능하다.

/* DECODE 함수 예시 */
SELECT DECODE(JOB, 'MANAGER', SAL*1.1, 'SALESMAN', SAL*1.05, SAL*1.03)
FROM EMP;

/* CASE문 예시1 */
SELECT
CASE JOB
WHEN 'MANAGER' THEN SAL*1.1
WHEN 'SALESMAN' THEN SAL*1.05
ELSE SAL*1.03
END AS UPSAL
FROM EMP;

/* CASE문 예시2 */
SELECT
CASE
WHEN COMM IS NULL THEN '해당사항 없음'
WHEN COMM=0 THEN '수당없음'
WHEN COMM>0 THEN '수당: ' || COMM
END AS COMM_TEXT
FROM EMP;

 

참고

- 『오라클로 배우는 데이터베이스 입문』

- webstudynote.tistory.com