IT에서 함수는 기능이라고 이해하면 될 것 같다.
SQL 함수들은 기본적인 Query문을 더욱 강력하게 해주고 데이터 값을 조작하는데 사용된다.
문자를 입력 받고 문자와 숫자 값 모두를 RETURN 할 수 있는 문자형 함수가 있다.
1. LOWER 함수
대소문자가 혼합되어 있거나 대문자인 문자열을 소문자로 변환한다.
사용법>
LOWER(column | expression)
예>
LOWER('MANAGER') ==> manager
문제> EMP 테이블에서 scott의 사원번호, 성명, 담당업무(소문자로), 부서번호를 출력하여라.
SELECT empno, ename, LOWER(job), deptno
FROM emp
WHERE ename = 'scott';
EMPNO ENAME LOWER(JOB) DEPTNO
----- -------------------- ------------------ ----------
7788 SCOTT analyst 20
2. UPPER 함수
대소문자가 혼합되어 있거나 소문자인 문자열을 대문자로 변환한다.
사용법>
UPPER(column | expression)
예>
UPPER('manager') ==> MANAGER
문제> EMP 테이블에서 scott의 사원번호, 성명, 담당업무, 부서번호를 출력하여라.
SELECT empno, ename, job, deptno
FROM emp
WHERE ename = UPPER('scott');
EMPNO ENAME JOB DEPTNO
----- -------------------- ------------------ ----------
7788 SCOTT ANALYST 20
3. INITCAP 함수
각 단어의 첫번째 문자를 대문자로, 나머지 문자는 소문자로 변경한다.
사용법>
INITCAP(column | expressioni)
예>
INITCAP('ORACLE SERVER') ==> Oracle Server
문제> DEPT 테이블에서 컬럼의 첫 글자들만 대문자로 변환하여 모든 정보를 출력하여라.
SELECT deptno, INITCAP(dname), INITCAP(loc)
FROM dept;
DEPTNO INITCAP(DNAME) INITCAP(LOC)
------ ---------------------------- ------------
10 Accounting New York
20 Research Dallas
30 Sales Chicago
40 Operations Boston
4. CONCAT 함수
두 개의 문자열을 연결한다. 두 개의 매개변수만 사용 가능.
사용법>
CONCAT(column1 | expression1, column2 | expression2)
예>
CONCAT('ORACLE', 'SERVER') ==> ORACLESERVER
5. SUBSTR 함수
지정된 길이만큼의 문자열을 추출한다.
사용법>
SUBSTR(column | expression, m, (,n))
예>
SUBSTR('000101-3234232', 8, 1) ==> 3
문제> EMP 테이블에서 이름의 첫글자가 'K'보다 크고 'Y'보다 작은 사원의 사원번호, 이름, 업무, 급여, 부서번호를 출력하여라.
단, 이름순으로 정렬하여라.
SELECT empno, ename, job, sal, deptno
FROM emp
WHERE SUBSTR(ename, 1, 1) > 'K' AND SUBSTR(ename, 1, 1) < 'Y'
// ename의 첫번째 글자니까 ename, 1, 1로 한것이다. ename 뒤에 1, 1의 의미는 1번째 자리부터 1자리까지 추출하는 것이다.
// 만약 1, 3 이면 1번째 자리부터 3자리를 추출할 것이다.
ORDER BY ename;
결과>
EMPNO ENAME JOB SAL DEPTNO
----- -------------------- ------------------ ---------- ----------
7654 MARTIN SALESMAN 1250 30
7934 MILLER CLERK 1300 10
7788 SCOTT ANALYST 3000 20
7369 SMITH CLERK 800 20
7844 TURNER SALESMAN 1500 30
7521 WARD SALESMAN 1250 30
6. LENGTH 함수
문자열의 길이를 숫자값으로 RETURN 한다.
사용법> LENGTH(column | expression)
예> LENGTH('000101-3234232') ==> 14
문제> EMP 테이블에서 부서가 20번인 사원의 사원번호, 이름, 이름의 자리수, 급여, 급여의 자리수를 출력하여라.
SELECT empno, ename, LENGTH(ename), sal, LENGTH(sal)
FROM emp
WHERE deptno = 20;
EMPNO ENAME LENGTH(ENAME) SAL LENGTH(SAL)
----- -------------------- ------------- ---------- -----------
7369 SMITH 5 800 3
7566 JONES 5 2975 4
7788 SCOTT 5 3000 4
7876 ADAMS 5 1100 4
7902 FORD 4 3000 4
7. INSTR 함수
명명된 문자의 위치를 숫자 값으로 RETURN 한다.
사용법> INSTR(column | expression, m(,n))
예> LENGTH('MILLER', 'L', 1, 2) ==> 4
문제> EMP 테이블에서 이름 중 'L'자의 위치를 출력하여라.
SELECT ename, INSTR(ename, 'L') e_null, INSTR(ename, 'L', 1, 1) e_11,
INSTR(ename, 'L', 1, 2) e_12, INSTR(ename, 'L', 4, 1) e_41,
INSTR(ename, 'L', 4, 2) e_42
// ename, 'L', 4, 2 의 의미는 enama의 4번째 자리를 기준으로 2번째 자리부터 L을 검색해서 위치를 리턴하는 것이다.
FROM emp
ORDER BY ename;
ENAME E_NULL E_11 E_12 E_41 E_42
-------------------- ---------- ---------- ---------- ---------- ----------
ADAMS 0 0 0 0 0
ALLEN 2 2 3 0 0
BLAKE 2 2 0 0 0
CLARK 2 2 0 0 0
FORD 0 0 0 0 0
JAMES 0 0 0 0 0
JONES 0 0 0 0 0
KING 0 0 0 0 0
MARTIN 0 0 0 0 0
MILLER 3 3 4 4 0
SCOTT 0 0 0 0 0
ENAME E_NULL E_11 E_12 E_41 E_42
-------------------- ---------- ---------- ---------- ---------- ----------
SMITH 0 0 0 0 0
TURNER 0 0 0 0 0
WARD 0 0 0 0 0
8. LPAD 함수
문자값을 좌측부터 채운다.
사용법> LPAD(column | expression, n, 'string')
예) LPAD('MILLER', 10, '*') => ****MILLER
// 10자리의 공간을 확보하고 MILLER를 넣고 남은 자리에 *가 들어가게 되는 것이다.
// 만약 실제 데이터보다 확보한 공간이 작으면 그 확보한 공간까지만 데이터가 출력된다.
9. RPAD 함수
문자값을 우측부터 채운다.
나머지 사용법이나 예는 위에 LPAD의 형식이다. 결과는 string이 우측부터 채워진다.
10. TRIM 함수
왼쪽 문자를 지우는 함수이다.
사용법> TRIM(leading/trailing/both trim_character FROM trim_source)
leading은 왼쪽에 있는 문자를 지우는 옵션이고, trailing은 오른쪽 문자, both는 양쪽에 있는 문자를 지우는 옵션이다.
디폴트는 both이다.
trim은 가장자리에 있는 문자만 지우고 가운데에 있는 문자는 지우지 못한다.
앞 뒤 공백을 지우는 데 쓰면 유용하겠다.
예> TRIM('H' FROM 'HelloWorld') ==> elloWorld
문제> EMP 테이블에서 10번 부서의 사원에 대하여 담당 업무 중 좌측에 'M'을 삭제하여 출력하여라.
SELECT TRIM('M' FROM job)
FROM emp
WHERE deptno = 10;
TRIM('M'FROMJOB)
------------------
ANAGER
PRESIDENT
CLERK
11. REPLACE 함수
특정 문자열을 대신하는 함수이다.
사용법> REPLACE(column | expression1, 'string1', 'string2')
예> REPLACE('JACK and JUE', 'J', 'BL') ==> BLACK and BLUE
문제> EMP 테이블에서 JOB에 'A'를 '$'로 바꾸어 출력하여라.
SELECT job, REPLACE(job, 'A', '$')
FROM emp;
JOB REPLACE(JOB,'A','$
------------------ ------------------
CLERK CLERK
SALESMAN S$LESM$N
SALESMAN S$LESM$N
MANAGER M$N$GER
SALESMAN S$LESM$N
MANAGER M$N$GER
MANAGER M$N$GER
ANALYST $N$LYST
PRESIDENT PRESIDENT
SALESMAN S$LESM$N
CLERK CLERK
JOB REPLACE(JOB,'A','$
------------------ ------------------
CLERK CLERK
ANALYST $N$LYST
CLERK CLERK
