1. 문자 데이터 가공 함수
1-1. 대 ˙ 소문자를 바꿔 주는 UPPER, LOWER, INITCAP 함수
| 함수 |
설명 |
| UPPER |
괄호 안 문자 데이터를 모두 대문자로 변환 |
| LOWER |
괄호 안 문자 데이터를 모두 소문자로 변환 |
| INITCAP |
괄호 안 문자 데이터 중 첫 글자는 대문자로, 나머지는 소문자로 변환 |
- UPPER / LOWER / INITCAP 함수는 모두 함수 안에 열(데이터)를 넣는 형태로 수행 가능
## 기본 형태
SELECT UPPER(열1), LOWER(열2), INITCAP(열3) FROM TABLE;
## 활용 형태
SELECT * FROM TABLE
WHERE UPPER(열1) == UPPER('Like'); ## 대소문자를 하나로 통일해서 반환
## LIKE와의 활용 형태
SELECT * FROM TABLE
WHERE UPPER(열1) LIKE UPPER('%scott%');
1-2. 문자열 길이를 구하는 LENGTH 함수
- Python에서의 len() 함수와 동일하게, character의 길이를 반환하는 함수
- 글자 개수가 아닌, 바이트 크기를 구하려면 LENGTHB를 사용하면 된다.
SELECT ENAME, LENGTH(ENAME) FROM EMP;
1-3. 문자열 일부를 추출하는 SUBSTR 함수
- R에서의 SUBSTRING 함수와 동일한 작업을 수행하는 함수
- SUBSTRING( data, i, n)으로 i번째부터 n만큼 뽑는 함수라고 생각하면 된다. (n이 없으면 전체 반환)
## 활용 예시
SELECT JOB, SUBSTR(JOB,1,2), SUBSTR(JOB,3,2) FROM EMP;
##JOB 열의 1번째부터 2개
##JOB 열의 3번째부터 2개
## length와의 활용 예시
SELECT JOB, SUBSTR(JOB, -LENGTH(JOB)) FROM EMP; ## 뒤에서부터 -LENGTH만큼 출력
## 파이썬에서의 -로 INDEX 반환하는 것과 동일한 이치
1-4. 문자열 데이터 안에서 특정 문자 위치를 찾는 INSTR함수
- INSTR([데이터],[타겟 문자],[시작 위치],[몇 번째 문자?])
- 위의 파라미터를 통해서 주어진 데이터에서 몇 번째부터 원하는 문자의 INDEX를 반환 받을지 작성
- 마지막 파라미터를 통해, K번째 원하는 문자를 반환받을 수 있음
SELECT INSTR('HELLO ORACLE!','L') AS INSTR_1 ## 첫 번째 L INDEX 반환
INSTR('HELLO ORACLE','L',5) AS INSTR_2 ## 5번째 L INDEX 반환
INSTR('HELLO ORACLE','L',2,2) AS INSTR_3 ## INDEX 2번부터 시작해서 5번째 L INDEX반환
1-5. 특정 문자를 바꾸는 REPLACE
- 파이썬와 R에서 나오는 REPLACE와 동일한 메커니즘의 함수
- REPLACE([DATA],[TARGET],[RESULT])의 형식으로 TARGETㅇ르 RESULT로 바꾸는 작업을 수행한다
SELECT '010-1234-5678' AS REPLACE,
REPLAEC('010-1234-5678','-','') AS REPLACE2
FROM TABLE2;
1-6. 데이터의 빈 공간을 문자로 채우는 LPAD, RPAD
- 패딩이라는 말처럼, 왼쪽 및 오른쪽에 대해 원하는 자리수만큼 특정 문자열을 반복하는 것을 말한다
- LPAD([데이터],[문자열 길이],[어떤 문자로?])의 형태로 사용하면 된다. (어떤 문자의 default는 공백)
SELECT LPAD('abc',5,'_') AS LPAD1 ## 이럴 경우 __abc가 된다.
RPAD('ABC',5,'_') AS RPAD1 ## 이럴 경우 ABC__가 된다.
1-7. 두 문자를 합치는 CONCAT 함수
- EXCEL에서의 CONCAT와 동일한 작업을 수행하는 함수
- CONCAT([문자1], [문자2]) 이런식으로 표현할 수 있음
- CONCAT 대신에 ' || ' 연산자도 활용할 수 있음
- 열1 || 열2 OR 열1 || " : " || 열2 이런식으로 표현 가능
SELECT CONCAT(열1,열2),
CONCAT(열1, CONCAT(" : ",열2))
FROM TABLE;
1-8. 특정 문자를 지우는 TRIM, LTRIM, RTRIM 함수
- 문자를 제거하는 함수이며 기본적으로 TRIM([삭제하고 싶은 문자] FROM [문자열])로 이해하면 된다.
- DEFAULT로 공백을 제거함, LTRIM은 좌측을, RTRIM을 우측부터 하나만 제거하는 함수라고 생각
SELECT TRIM(' ORACLE ') AS TRIM, ## 이렇게 DEFAULT값을 이용할 수 도
TRIM(BOTH '_' FROM '__ORACLE__') AS TRIM ## 이렇게 표현할 수도
FROM TABLE;
2. 숫자 데이터 및 자료형 변환 관련 함수
2-1. 숫자데이터 관련 요약
- 파이썬에서 활용하는 함수들과 동일한 이름 및 과정을 수행
| 함수 |
설명 |
형태 |
비고 |
| ROUND |
지정된 숫자의 특정 위치에서 반올림한 값을 반환 |
ROUOND(숫자, 위치) |
위치가 음수면 10의 자리 |
| TRUNC |
지정된 숫자의 특정위치에서 버림한 값을 반환 |
TRUNC(숫자,위치) |
위치가 음수면 10의 자리 |
| CEIL |
지정된 숫자보다 큰 정수 중 가장 작은 정수를 반환 |
CEIL(숫자) |
|
| FLOOR |
지정된 숫자보다 작은 정수 중 큰 정수를 반환 |
FLOOR(숫자) |
|
| MOD |
지정된 숫자를 나눈 나머지 값을 반환 |
MOD(숫자, 나눌 숫자) |
|
## ROUND
SELECT ROUND(1234.5678) AS ROUND, ## 이렇게 하면 1235
ROUND(1234.5678,0), ## 1235
ROUND(1234.5678,1), ## 1234.6
ROUND(1234.5678,-1) ## 1230
FROM TABLE;
## TRUNC
SELECT TRUNC(1234.5678) AS TRUNC FROM DUAL
## CEIL / FLOOR
SELECT CEIL(3.14), FLOOR(3.14) FROM DUAL;
## MOD
SELECT MOD(10,3), MOD(2,1) FROM DUAL;
2-2. 날짜 데이터를 다루는 날짜 함수
- 날짜 데이터는 숫자 데이터와 함께 연산이 가능하다. (날짜 ± 숫자)는 날짜에서 숫자만큼 더하고 뺀 날짜를, 날짜-날짜는 두 날짜 간의 차이를 의미
- 단, 날짜 데이터 + 날짜 데이터는 연산이 불가능하다.
| 함수명 |
기능 |
형태 |
| SYSDATE |
현재 날짜와 시간 출력 |
SYSDATE |
| ADD_MONTHS |
N개월 후를 출력 |
ADD_MONTHS([날짜], [숫자] |
| MONTHS_BETWEEN |
두 날짜 간의 개월 차이 출력 |
MONTHS_BETWEEN(A,B) |
| NEXT_DAY |
돌아오는 요일의 날짜를 출력 |
NEXT_DAY(날짜, 요일 문자) |
| LAST_DAY |
해당 날짜가 속한 달의 마지막 날짜 출력 |
LAST_DAY(날짜) |
| ROUND |
날짜 데이터를 형식에 맞게 출력 |
ROUND(날짜, 포맷) |
| TRUNC |
날짜 데이터를 형식에 맞게 출력 |
TRUNC(날짜, 포맷) |
SELECT SYSDATE,
ADD_MONTHS(SYSDATE,3),
MONTHS_BETWEEN(SYSDATE, HIREDATE),
NEXT_DAY(SYSDATE,'월요일'),
LAST_DAY(SYSDATE),
ROUND(SYSDATE,'HH'),
TRUNC(SYSDATE,'HH') FROM TABLE;
2-3. 날짜, 숫자 데이터를 문자 데이터로 변환하는 TO_CHAR 함수
- 자료형을 문자로 변환하는 함수로 TO_CHAR([데이터], [포멧])으로 작성하면 된다.
| 연 월 일에 대한 포맷 |
| CC |
세기 |
MONTH |
월(언어별 월 이름 전체) |
| YYYY,RRRR |
연(4자리) |
DD |
일(2자리 숫자) |
| YY,RR |
연(2자리) |
DDD |
1년 중 며칠 |
| MM |
월(2자리) |
DY |
요일(언어별 요일 이름 약자) |
| MON |
월(언어별 월 이름 약자) |
DAY / W |
요일(언어별 요일 이름 전체) / 1년 중 몇 째주 |
| 시간 형식에 대한 포맷 |
| HH24 |
24시간으로 표현한 시간 |
HH,HH12 |
12시간으로 표현한 시간 |
| MI / SS |
분 / 초 |
AM,PM,A.M,P.M |
오전, 오후 표시 |
## 연/월 포맷
SELECT SYSDATE,
TO_CHAR(SYSDATE, 'MM'),
TO_CHAR(SYSDATE, 'YYYY'),
TO_CHAR(SYSDATE, 'DD')
FROM TABLE;
## 시간 포맷
SELECT SYSDATE,
TO_CHAR(SYSDATE, 'HH'),
TO_CHAR(SYSDATE, 'HH24'),
TO_CHAR(SYSDATE, 'HH:MI:SS'),
TO_CHAR(SYSDATE, 'HH:MI:SS AM')
2-4. 문자 데이터를 숫자 데이터로 바꿔주는 TO_NUMBER 함수
- 문자를 숫자로 바꿔주는 수행을 해준다. TO_NUMBER(데이터, 숫자형태)의 형태로 작성
SELECT TO_NUMBER('1,300','999,999') FROM TABLE; ## 이러면 6자리 숫자로 이루어진 형태로 1,300을 반환
2-5. 문자 데이터를 날짜 데이터로 변환
- TO_DATE 함수로 문자를 날짜 데이터로 변환할 수 있다.
- TO_DATE(데이터, 날짜 형태)로 입력하면 된다. 기본적으로는 YYYY-MM-DD가 있다.
SELECT TO_DATE('2018-09-10', 'YYYY-MM-DD') FROM TABLE;
## 이외 형식들
# YY/MM/DD
# RR/MM/DD 등..
3. NULL 처리 및 조건문 함수
3-1. NULL 처리 함수 NVL / NVL2
- NVL 함수는 IFELSE 함수와 비슷한 포맷으로 구성되어 있다. 다만, 조건은 무조건 NULL값으로 고정
- NVAL(데이터, NULL일 경우 반환할 데이터)
SELECT NVL(COMM,0) FROM TABLE ## NULL이면 0으로 반환 아니면 그냥 반환
3-2. 상황에 따른 데이터를 반환하는 DECODE, CASE함수
- 기본적으로 둘 다 MUTATE IF, IF, SWITCH와 같은 함수류들과 비슷한 작업을 수행한다.
- DECODE의 경우 DECODE(데이터, 조건1, 조건1에 따른 반환값, 조건2, 조건2에 대한 반환값 ...)으로 구성
- CASE의 경우, 선언한 뒤 변수를 입력하고, WHEN 조건 THEN 출력값의 형태로 작성한다. (마무리는 항상 END)
## DECODE의 형태
SELECT DECODE(JOB,
'MANAGER', SAL*1.1,
'EMPLOYER', SAL * 1.0) FROM TABLE;
## CASE 형태
SELECT EMP,
CASE JOB ## CASE로 시작
WHEN MANAGER THEN SAL*1.1
WHEN EMPLOYER THEN SAL*1.0
ELSE SAL*0.5
END ## END로 마무리
FROM TABLE;
4. 연습문제 풀이
4-1.
- 조건1 : EMPNO열은 사원 이름이 다섯 글자이며, 여섯 글자 미만인 사원 정보 출력
- 조건2 : MASKING_EMPNO 열에는 사원 번호 앞 두 자리 외 뒷자리를 *로 출력
- 조건3 : MASKING_ENAME 열에는 사원 이름의 첫 글자만 보여주고 나머지 글자수만큼 *로 출력
## 내 풀이 (ORACLE를 다시 깔고 나서 확인이 가능할 것 같다)
SELECT EMPNO, REPLACE(EMPNO, SUBSTR(EMPNO,3,2),'*') AS MASKING_EMPNO
ENAME,
REPLACE(ENAME, SUBSTR(ENAME,2,4),"*") AS MASKING_ENAME
FROM EMP
WHERE LENGTH(ENMPNO) >= 5 AND LENGTH(EMPNO) <6;
## 답안
SELECT EMPNO,
RPAD(SUBSTR(EMPNO, 1, 2), 4, '*') AS MASKING_EMPNO,
ENAME,
RPAD(SUBSTR(ENAME, 1, 1), LENGTH(ENAME), '*') AS MASKING_ENAME
FROM EMP
WHERE LENGTH(ENAME) >= 5
AND LENGTH(ENAME) < 6;
- SUBSTR을 이용해서, 앞 글자만 뽑고 나머지를 RPAD를 활용하여 덮어씌운 것으로 확인!
- RPAD는 DATA, 개수, 문자로 반환값을 받는다.
4-2.
- 조건1: 하루 근무를 8시간을 봄
- 조건2: 사원들의 하루 급여와 시급을 게산하여 출력
- 조건3: 하루 급여는 소수점 세 번째자리에서 버리고, 시급은 두 번째 소수점에서 반올림
## 내 코드 (확인 필요)
SELECT EMPNO,ENAME,SAL,
TRUNC(SAL/(21.5),2) AS DAY_PAY,
ROUND(SAL/(21.5*8),1) AS TIME_PAY
FROM EMP;
## 정답
SELECT EMPNO, ENAME, SAL,
TRUNC(SAL / 21.5, 2) AS DAY_PAY,
ROUND(SAL / 21.5 / 8, 1) AS TIME_PAY
FROM EMP;
- 중요한 것은 FLOOR가 아니라 TRUNC함수를 썼다는 점. FLOOR은 파라미터가 하나 뿐!
- 또한 문항에서 '~ 번째에서' 반올림이기 때문에 그 앞자리를 기준으로 파라미터를 작성해야 함
4-3.
- 조건1 : 입사일을 기준으로 3개월이 지난 첫 월요일에 정직원이 됨
- 조건2 : 정직원이 되는 날짜를 YYYY-MM-DD 형식으로 출력
- 추가 수당이 없는 사원의 추가 수당은 N/A로 출력
## 내 답안 (틀림)
SELECT EMPNO, ENAME,HIREDATE,
TO_DATE(NEXT_DAY(ADD_MONTHS(HIREDATE,3),'월요일'),'YYYY-MM-DD') AS R_JOB
NVL(COMM, 'N/A') AS COMM
FROM EMP;
## 정답
SELECT EMPNO, ENAME, HIREDATE,
TO_CHAR(NEXT_DAY(ADD_MONTHS(HIREDATE, 3), '월요일'), 'YYYY-MM-DD') AS R_JOB,
NVL(TO_CHAR(COMM), 'N/A') AS COMM
FROM EMP;
- 보면, TO_DATE가 아닌 TO_CHAR로 표현했으며, COMM 열의 경우 미리 CHARACTER로 바꾼 다음 NVL 함수를 사용
- 실제로 코드 돌렸을 때, 아마 자료형태 때문에 오류가 생기지 않을까 생각이 든다.
- 항상 하나의 자료형만 같게 되니(열), 자료형태 맞추는걸 생각해두자!
4-4.
## 내 답안
SELECT EMPNO, ENAME, MGR,
CASE TO_CHAR(MGR)
WHEN MGR IS NULL THEN '0000'
WHEN SUBSTR(MGR,1,2) = '75' THEN '5555'
WHEN SUBSTR(MGR,1,2) = '76' THEN '6666'
WHEN SUBSTR(MGR,1,2) = '77' THEN '7777'
WHEN SUBSTR(MGR,1,2) = '78' THEN '8888'
ELSE TO_CHAR(MGR)
END AS CHG_GMR
FROM EMP;
## 정답
SELECT EMPNO, ENAME, MGR,
CASE
WHEN MGR IS NULL THEN '0000'
WHEN SUBSTR(MGR, 1, 2) = '78' THEN '8888'
WHEN SUBSTR(MGR, 1, 2) = '77' THEN '7777'
WHEN SUBSTR(MGR, 1, 2) = '76' THEN '6666'
WHEN SUBSTR(MGR, 1, 2) = '75' THEN '5555'
ELSE TO_CHAR(MGR)
END AS CHG_MGR
FROM EMP;
- 마찬가지로 자료형태(CHR) 잘 신경써주면 되며, 항상 조건 앞 뒤로 문장이 말이 되는지를 확인하자!
