SQL 주요함수: DUAL 테이블과 숫자함수, 문자함수

SQL 주요함수

1. DUAL 테이블

기술할 테이블이 없을 때 사용하는 DUMMY 테이블. 오라클에서 테이블 없이 단순한 산술식을 계산할 때 쓸 수 있다.

SELECT 24*60 FROM DUAL;

SELECT SYSDATE FROM DUAL;

SYSDATE: 현재 년월일이 표시된다.

2. 숫자 함수

ABS : 절대값을 구한다.

COS : 코사인값

EXP : E(2.71828183...)의 N승 반환

FLOOR : 소수점 아래 절삭

LOG : LOG값

POWER : POWER(M, N) M의 N승

SIGN(N) : N<0이면 -1, N=0이면 0, N>0이면 1

SIN : 사인값

TAN : 탄젠트값

ROUND : 특정 자리수 반올림

TRUNC : 특정 자리수 버림

MOD : 입력받은 수를 나눈 나머지값

1) ABS : 절대값을 구한다.

ex) SELECT -10, ABS(-10) FROM DUAL;

결과는 -10과 10

2) COS : 코사인값

3) EXP : E(2.71828183...)의 N승 반환

4) FLOOR : 소수점 아래 절삭

ex) SELECT 34.5678, FLOOR(34.5678) FROM DUAL;

결과는 34.5678과 34

5) POWER : POWER(M, N) M의 N승

6) SIGN(N) : N<0이면 -1, N=0이면 0, N>0이면 1

7) ROUND : 특정 자리수 반올림

ex) SELECT 34.5678, ROUND(34.5678) FROM DUAL;

결과는 34.5678과 35

ex) SELECT 34.5678, ROUND(34.5678, 2) FROM DUAL; 

결과는 34.5678과 34.57 -- 소수점 이하 3번째 자리를 반올림하여 2번째 자리까지 표시.

ex) SELECT 34.5678, ROUND(34.5678, -1) FROM DUAL; 

결과는 34.5678과 30 -- 음수를 넣으면 소수점 이상자리, 1자리에서 반올림하여 표시.

8) TRUNC : 특정 자리수 버림

ex) SELECT TRUNC(34.5678, 2), TRUNC(34.5678, -1), TRUNC(34.5678) FROM DUAL;

결과는 34.56, 30, 34 

-- 2이면 소수점 이하 3번째 자리에서 버림 연산으로 소수점 2번째 자리까지 표시

-- 음수를 넣는 경우: -1인 경우 양수 1자리를 버리는 연산.

-- 아무것도 넣지 않으면(0이면) 소수점 자리 전체를 버림 연산.

9) MOD : 입력받은 수를 나눈 나머지값

ex) SELECT MOD (27, 2), MOD (27, 5), MOD (27, 7) FROM DUAL;

결과는 1, 2, 6

3. 문자 처리 함수

LOWER : 소문자로 변환

UPPER : 대문자로 변환

INTICAP : 첫글자만 대문자, 나머지는 소문자로 변환

CONCAT : 문자의 값을 연결

SUBSTR(필드, 위치, 수량) : 문자를 잘라 추출(한글 1Byte)

SUBSTRB(필드, 위치, 수량) : 문자를 잘라 추출(한글 2Byte)

LENGTH : 문자의 길이를 반환(한글 1Byte)

LENGTHB : 문자의 길이를 반환(한글 2Byte)

INSTR(데이터, 찾을 데이터, 시작위치, 검색순번) : 특정 문자의 위치값을 반환(한글 1Byte)

INSTRB(데이터, 찾을 데이터, 시작위치, 검색순번) : 특정 문자의 위치값을 반환(한글 2Byte)

LPAD, RPAD : 정렬하여 특정 길이의 문자열로 반환

TRIM : 잘라내고 남은 문자 표시

CONVERT : CHAR SET을 변환

CHR : 문자를 ASCII 코드값으로 변환

ASCII : ASCII 코드값을 문자로 변환

REPLACE(데이터, 찾을 문장, 바꿀 문장) : 문자열에서 특정 문자를 변경

1) LOWER : 소문자로 변환

ex) SELECT 'Welcome to Oracle', LOWER('Welcome to Oracle') FROM DUAL;

결과는 Welcome to Oracle과 welcome to oracle

2) UPPER : 대문자로 변환

ex) SELECT 'Welcome to Oracle', UPPER('Welcome to Oracle') FROM DUAL;

결과는 Welcome to Oracle과 WELCOME TO ORACLE

3) INTICAP : 첫글자만 대문자, 나머지는 소문자로 변환

ex) SELECT 'WELCOME TO ORACLE', UPPER('WELCOME TO

 ORACLE') FROM DUAL;

결과는 WELCOME TO ORACLE과 Welcome to Oracle

4) SUBSTR(필드, 위치, 수량) : 문자를 잘라 추출(한글 1Byte)

ex) SELECT SUBSTR('Welcome to Oracle', 4, 3) FROM DUAL;

결과는 com -- 문자열의 4번째부터 3글자를 추출

ex) SELECT SUBSTR('Welcome to Oracle', -4, 3) FROM DUAL;

결과는 acl -- 문자열 뒤에서부터 4번째부터 3글자를 추출

ex) SELECT SUBSTR(HIREDATE, 1, 2) 년도, SUBSTR(HIREDATE, 4, 2) 달 FROM EMP;

HIREDATE 컬럼에서 첫글자부터 2개가 년도이므로 SUBSTR(HIREDATE,1,2), 4번째 글자부터 2개는 월이므로 SUBSTR(HIREDATE,4,2)를 넣어 검색.

ex) SELECT SUBSTR(HIREDATE, 4, 2) = '09';

-- 9월에 입사한 사원만 출력하는 경우

예제) 이름의 3번째 자리가 R로 끝나는 사원을 검색하기 위한 방법은 다음과 같다. 이를 SUBSTR로 바꾸면?

SELECT EMPNO, ENAME FROM EMP WHERE ENAME LIKE '__R%';

답) SELECT * FROM EMP WHERE SUBSTR(ENAME, 3, 1) = 'R'; 

5) SUBSTRB(필드, 위치, 수량) : 문자를 잘라 추출(한글 2Byte)

ex) SELECT SUBSTR('Welcome to Oracle', 3, 4), SUBSTRB('Welcome to Oracle', 3, 4) FROM EMP;

- 문자가 영문인 경우 SUBSTR이나 SUBSTRB나 결과는 동일하게 lcom이 나온다.

ex) SELECT SUBSTR('웰컴투오라클', 3, 4), SUBSTRB('월컴투오라클', 3, 4) FROM EMP;

- 문자가 한글인 경우 SUBSTR은 '투오라클', SUBSTRB는 '컴'이 나온다. 한글 1자는 2Byte를 차지하기 때문.

6) LENGTH : 문자의 길이를 반환(한글 1Byte)

ex) SELECT LENGTH('Oracle'), LENGTH('오라클') FROM DUAL;

결과는 6과 3 -- 글자의 개수를 구한다.

7) LENGTHB : 문자의 길이를 반환(한글 2Byte)

ex) SELECT LENGTHB('Oracle'), LENGTHB('오라클') FROM DUAL;

결과는 6과 9 -- 글자의 바이트수를 구한다. (한글 1글자는 2바이트를 차지)

8) INSTR(데이터, 찾을 데이터, 시작위치, 검색순번) : 특정 문자의 위치값을 반환(한글 1Byte)

ex) SELECT INSTR('WELCOME TO ORACLE', 'O') FROM DUAL;

결과는 5. 왼쪽에서 5번째에 있는 O를 검색한다.

ex) SELECT INSTR('WELCOME TO ORACLE', 'O', 6, 2) FROM DUAL;

결과는 12. 6번째칸(E)에서 시작하여 나오는 O 중에서 2번째 O를 검색했기 때문.

예제) 이름의 3번째 자리가 R로 끝나는 사원을 검색하기 위한 방법은 다음과 같다. 이를 INSTR로 바꾸면?

SELECT EMPNO, ENAME FROM EMP WHERE ENAME LIKE '__R%';

답) SELECT * FROM EMP WHERE INSTR(ENAME, 'R', 3, 1) = 3; 

-- 3번째 위치가 3이냐로 검색하기 때문에 INSTR(ENAME, 'R', 3, 1) = 3;

9) INSTRB(데이터, 찾을 데이터, 시작위치, 검색순번) : 특정 문자의 위치값을 반환(한글 2Byte)

ex) SELECT INSTR('데이터베이스', '이', 3, 1), INSTRB('데이터베이스', '이', 3, 1) FROM DUAL;

결과는 5와 4. 한글은 한글자가 2Byte이므로 1바이트씩 찾는 INSTR과 2바이트씩 찾는 INSTRB의 결과가 달라진다.

10) LPAD, RPAD : 정렬하여 특정 길이의 문자열로 반환

- LPAD(데이터, 전체자리수, 왼쪽 빈공간에 채울 문자) 

- RPAD(데이터, 전체자리수, 오른쪽 빈공간에 채울 문자)

ex) SELECT RPAD('Oracle', '10', '#') FROM DUAL;

결과는 Oracle####. 10자리로 만든 뒤 데이터의 오른쪽을 #으로 채워넣음.

ex) SELECT LPAD(SAL, 10, '0') FROM EMP;

원래 있던 데이터를 10자리로 만든 뒤 왼쪽에 0을 채워넣음.

11) TRIM : 잘라내고 남은 문자 표시

ex) SELECT LTRIM(' Oracle ') FROM DUAL;

결과는 Oracle 왼쪽 공백을 제거하고 Oracle만 출력

ex) SELECT RTRIM(' Oracle ') FROM DUAL;

결과는 Oracle 오른쪽 공백을 제거하고 Oracle만 출력

ex) SELECT TRIM(' Oracle ') FROM DUAL;

결과는 Oracle 모든 공백문자를 제거하고 Oracle만 출력

ex) SELECT TRIM('a' FROM 'aaaaOracleaaaa') FROM DUAL;

결과는 'aaaaOracleaaaa'에서 'a'를 모두 제거하고 'Oracle'만 출력