데이터처리와 가공을 위한 오라클 기본 함수

대, 소문자를 바꿔 주는 UPPER, LOWER, INTCAP 함수

SELECT ENAME, UPPER(ENAME), LOWER(ENAME), INITCAP(ENAME) FROM EMP;

 

문자열 길이를 구하는 LENGTH 함수

SELECT ENAME, LENGTH(ENAME) FROM EMP WHERE LENGTH(ENAME) >= 3;

 

LENGTH 함수와 같지만 문자열 데이터 길이가 아닌 바이트 수를 반환하는 LENGTHB 함수

SELECT LENGTH('태극기'), LENGTHB('태극기') FROM DUAL;

# 결과 3, 6

 

문자열 일부를 추출하는 SUBSTR 함수

SELECT JOB, SUBSTR(JOB, 1, 2), SUBSTR(JOB, 3, 2), SUBSTR(JOB, 5) FROM EMP;

# JOB값이 SALESMAN 일때 결과 값

SUBSTR(JOB, 1, 2): SA

SUBSTR(JOB, 3, 2): LE

SUBSTR(JOB, 5): SMAN

 

SUBSTR 함수 안에 다른 함수 함께 사용하기

SELECT JOB, 

　　　　　SUBSTR(JOB, -LENGTH(JOB)),

　　　　　SUBSTR(JOB, -LENGTH(JOB), 2),

　　　　　SUBSTR(JOB, -3)

FROM EMP;

 

문자열 데이터 안에서 특정 문자 위치를 찾는 INSTR 함수

SELECT INST('HELLO, ORACLE!', 'L') AS INSTR_1,

　　　 INST('HELLO, ORACLE!', 'L', 5) AS INSTR_2,

　　　 INST('HELLO, ORACLE!', 'L', 2, 2) AS INSTR_3

FROM DUAL;

1	2	3	4	5	6	7	8	9	10	11	12	13	14
H	E	L	L	O	,		O	R	A	C	L	E	!

결과값

INSTR_1 → 3

INSTR_2 → 12

INSTR_3 → 4

 

INSTR 함수로 사원 이름에 문자 A가 있는 행 구하기

SELECT * FROM EMP WHERE INSTR(ENAME, 'A') > 0;

 

LIKE 연산자로 사원 이름에 문자 S가 있는 행 구하기

SELECT * FROM EMP WHERE ENAME LIKE '%S%';

 

REPLACE 함수로 문자열 안에 있는 특정 문자 바꾸기

SELECT '010-1234-5678' AS REPLACE_BEFORE, REPLACE('010-1234-5678', '-', ' ') AS REPLACE_1 FROM DUAL;

결과값

010-1234-5678 → 010 1234 5678

 

데이터의 빈 공간을 특정 문자로 채우는 LPAD, RPAD 함수

SELECT 'Oracle',

　　　LPAD('Oracle', 10, '#') AS LPAD_1,

　　　RPAD('Oracle', 10, '*') AS RPAD_1,

　　　LPAD('Oracle', 10) AS LPAD_2,

　　　RPAD('Oracle', 10) AS RPAD_2,

FROM DUAL;

결과값

Oracle | ####Oracle | Oracle**** | 　　Oracle | Oracle　　|

데이터의 자릿수에 10을 지정하여 데이터가 10자리가 되므로 'Oracle'이란 여섯 글자를 제외한 남은 자리를 #, * 로 채움

 

특정 문자로 자릿수 채워서 출력하기

RPAD 함수를 사용하여 개인정보 뒷자리 * 표시로 출력하기

SELECT RPAD('971225-', 14, '*') AS RPAD_JMNO, RPAD('010-1234-', 13, '*') AS RPAD_PHONE FROM DUAL;

결과값

971225-*******

010-1234-****

 

두 열 사이에 콜론(:) 넣고 연결하기

SELECT CONCAT(EMPNO, ENAME), CONCAT(EMPNO, CONCAT(' : ', ENAME)) FROM EMP WHERE ENAME = 'SCOTT';

결과값

CONCAT(EMPNO, ENAME) 7788SCOTT

CONCAT(EMPNO, CONCAT(' : ', ENAME)) 7788 : SCOTT

 

ROUND 함수를 사용하여 반올림된 숫자 출력하기

SELECT ROUND(1234.5678) AS ROUND, 

　　　　ROUND(1234.5678, 0) AS ROUND_0,

　　　　ROUND(1234.5678, 1) AS ROUND_1,

　　　　ROUND(1234.5678, 2) AS ROUND_2,

　　　　ROUND(1234.5678, -1) AS ROUND_MINUS1,

　　　　ROUND(1234.5678, -2) AS ROUND_MINUS2,

FROM DUAL;

결과값

ROUND	ROUND_0	ROUND_1	ROUND_2	ROUND_MINUS1	ROUND_MINUS2
1235	1235	1234.6	1234.57	1230	1200

 

특정 위치에서 버리는 TRUNC 함수

SELECT TRUNC(1234.5678) AS TRUNC,

　　　　TRUNC(1234.5678, 0) AS TRUNC_0,

　　　　TRUNC(1234.5678, 1) AS TRUNC_1,

　　　　TRUNC(1234.5678, 2) AS TRUNC_2,

　　　　TRUNC(1234.5678, -1) AS TRUNC_MINUS1,

　　　　TRUNC(1234.5678, -2) AS TRUNC_MINUS2,

FROM DUAL;

결과값

TRUNC	TRUNC_0	TRUNC_1	TRUNC_2	TRUNC_MINUS1	TRUNC_MINUS2
1234	1234	1234.5	1234.56	1230	1200

지정한 숫자와 가까운 정수를 찾는 CEIL, FLOOR 함수

SELECT CEIL(3.14), 

　　　　FLOOR(3.14),

　　　　CEIL(-3.14),

　　　　FLOOR(3.14),

FROM DUAL;

결과값

CEIL(3.14)	FLOOR(3.14)	CEIL(-3.14)	FLOOR(-3.14)
4	3	-3	-4

숫자로 나눈 나머지 값을 구하는 MOD 함수

SELECT MOD(15, 6),

　　　　MOD(10, 2),

　　　　MOD(11, 2),

FROM DUAL;

결과값

MOD(15, 6)	MOD(10, 2)	MOD(11, 2)
3	0	1

SYSDATE 함수를 사용하여 날짜 출력하기

SELECT SYSDATE AS NOW, SYSDATE-1 AS YESTERDAY, SYSDATE+1 AS TOMORROW FROM DUAL;

NOW	YESTERDAY	TOMORROW
2020-01-30 오후 07:30:42	2020-01-29 오후 07:30:42	2020-01-31 오후 07:30:42

SYSDATE와 ADD_MONTHS 함수로 3개월 후 날짜 구하기

SELECT SYSDATE, ADD_MONTHS(SYSDATE, 3) FROM DUAL;

결과값

SYSDATE	ADD_MONTHS(SYSDATE, 3)
2020-01-30 오후 07:30:42	2020-04-30 오후 07:30:42

입사 20주년 되는 사원들 데이터 출력하기

SELECT EMPNO, ENAME, HIREDATE, ADD_MONTHS(HIREDATE, 240) AS WORK20YEAR FROM EMP;

 

HIREDATE와 SYSDATE 사이의 개월 수를 MONTHS_BETWEEN 함수로 출력하기

SELECT EMPNO, ENAME, HIREDATE, SYSDATE,

　　　　MONTHS_BETWEEN(HIREDATE, SYSDATE) AS MONTHS1,

　　　　MONTHS_BETWEEN(SYSDATE, HIREDATE) AS MONTHS2,

　　　　TRUNC(MONTHS_BETWEEN(HIREDATE, SYSDATE)) AS MONTHS3

FROM EMP;

 

돌아오는 요일(NEXT_DAY), 달의 마지막 날짜(LAST_DAY) 구하기

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

SYSDATE	NEXT_DAY(SYSDATE, '화요일')	LAST_DAY(SYSDATE)
2021-01-30 오후 2:44:21	2021-02-02 오후 2:44:21	2021-01-31 오후 2:44:21

 

ROUND 함수를 사용하여 날짜 데이터 출력하기

SELECT SYSDATE,

　　　　ROUND(SYSDATE, 'YYYY') AS FORMAT_YYYY

　　　　ROUND(SYSDATE, 'DDDD') AS FORMAT_DDDD

　　　　ROUND(SYSDATE, 'HH') AS FORMAT_HH

FROM DUAL;

 

SYSDATE 날짜 형식 지정하여 출력하기

SELECT TO_CHAR(SYSDATE, 'YYYY/MM/DD HH24:MI:SS') AS 현재날짜시간 FROM DUAL;

결과값

2020/01/30 23:59:01

 

월과 요일을 다양한 형식으로 출력하기

SELECT SYSDATE, 

　　　　TO_CHAR(SYSDATE, 'MM') AS MM,

　　　　TO_CHAR(SYSDATE, 'MM') AS MON,

　　　　TO_CHAR(SYSDATE, 'MM') AS MONTH,

　　　　TO_CHAR(SYSDATE, 'MM') AS DD,

　　　　TO_CHAR(SYSDATE, 'MM') AS DY,

　　　　TO_CHAR(SYSDATE, 'MM') AS DAY

FROM DUAL;

결과값

SYSDATE	MM	MON	MONTH	DD	DY	DAY
2021/01/30 23:59:01	01	1월	1월	30	토	토요일

 

TO_DATE 함수로 문자 데이터를 날짜 데이터 변환하기

SELECT TO_DATE('2020-01-15', 'YYYY-MM-DD') AS TODATE1

　　　　TO_DATE('20200115', 'YYYY-MM-DD') AS TODATE2

FROM DUAL;

결과값

2020/01/15

2020/01/15