Oracle DB #4 SQL 주요 함수

1. SQL 주요 함수

1) 대문자로 변환하는 UPPER 함수

- UPPER 함수는 입력한 문자값을 대문자로 변환하는 함수

SELECT 'Welcome to Oracle', UPPER('Welcome to Oracle')

FROM DUAL;

2) 소문자로 변환하는 LOWER 함수

- LOWER 함수는 문자열을 모두 소문자로 변경

SELECT 'Welcome to Oracle', LOWER('Welcome to Oracle')

FROM DUAL;

3) 이니셜만 대문자로 변환하는 INITCAP 함수

- INITCAP 함수는  문자열의 이니셜만 대문자로 변경

SELECT 'WELCOME TO ORACLE',

              INITCAP('WELCOME TO ORACLE')

FROM DUAL;

4) 문자 길이를 구하는 LENGTH

- LENGTH 함수는 컬럼에 저장된 데이터 값이 몇 개의 문자로 구성되었는지 길이를 알려주는 함수

SELECT LENGTH('Oracle'), LENGTH('오라클')

FROM DUAL;

5) 바이트 수를 알려주는 LENGTHB 함수

-LENGTHB 함수는 바이트 수를 알려주는 함수

SELECT LENGTHB('Oracle'), LENGTHB('오라클')

FROM DUAL;

 

6) 문자열 일부만 추출하는 SUBSTR 함수

- SUBSTR 과 SUBSTRB 함수는 대상 문자열이나 칼럼의 자료에서 시작위치부터 선택 개수만큼의 문자를 추출

SELECT SUBSTR('Welcome to Oracle', 4, 3)

FROM DUAL;

1	2	3	4	5	6	7	8	9	10	11	12	13	14	15	16	17
W	e	l	c	o	m	e		t	o		O	r	a	c	l	e

SELECT SUBSTR('Welcome to Oracle', -4, 3)

FROM DUAL;

 

-17	-16	-15	-14	-13	-12	-11	-10	-9	-8	-7	-6	-5	-4	-3	-2	-1
W	e	l	c	o	m	e		t	o		O	r	a	c	l	e

SELECT *

FROM EMP

WHERE SUBSTR(HIREDATE, 4, 2)='09';

7) 바이트 수를 기준으로 문자열 일부만 추출하는 SUBSTRB 함수

- SUBSTRB 함수는 문자열을 추출하기 위해서 문자를 셀 때 문자의 개수가 아닌 그 문자가 메모리에 저장되는 바이트 수로 셈

- 영문자는 1자는 메모리에 1바이트로 저장되기 때문에 SUBSTR 함수와 SUBSTRB 함수 어떤 함수를 사용하여도 결과가 동일

SELECT SUBSTR('Welcome To Oracle', 3, 4),

              SUBSTRB('Welcome To Oracle', 3, 4)

FROM DUAL;

SELECT SUBSTR('웰컴투오라클', 3, 4), SUBSTRB('웰컴투오라클', 3, 4)

FROM DUAL;

 

8) 특정 문자의 위치를 구하는 INSTR 함수

- INSTR 함수는 대상 문자열이나 칼럼에서 특정 문자가 나타나는 위치를 알려줌

SELECT INSTR('WELCOME TO ORACLE', 'O')

FROM DUAL;

SELECT INSTR('WELCOME TO ORACLE', 'O', 6, 2)

FROM DUAL;

 

9) 특정 기호로 채우는 LPAD/RPAD 함수

- LPAD(LEFT PADDING) 함수는 칼럼이나 대상 문자열을 명시된 자릿수에서 오른쪽에 나타내고, 남은 왼쪽 자리를 특정 기호로 채움

 

SELECT LPAD('Oracle', 20, '#')

FROM DUAL;

- RPAD(RIGHT PADDING) 함수는 반대로 칼럼이나 대상 문자열을 명시된 자릿수에서 왼쪽에 나타내고, 남은 오른쪽 자리를 특정 기호로 채움

 

SELECT LPAD('Oracle', 20, '#')

FROM DUAL;

10) 특정 문자를 잘라내는 TRIM 함수

SELECT TRIM(' Oracle ')

FROM DUAL;

11) 날짜 함수

구 분	설 명
SYSDATE	시스템 저장된 현재 날짜를 반환한다.
MONTHS_BETWEEN	두 날짜 사이가 몇 개월인지를 반환한다.
ADD_MONTHS	특정 날짜에 개월 수를 더한다.
NEXT_DAY	특정 날짜에서 최초로 도래하는 인자로 받은 요일의 날짜를 반환한다.
LAST_DAY	해당 달의 마지막 날짜를 반환한다.
ROUND	인자로 받은 날짜를 특정 기준으로 반올림한다.
TRUNC	인자로 받은 날짜를 특정 기준으로 버린다.

12) 현재 날짜를 반환하는 SYSDATE 함수

- SYSDATE 함수는 시스템에 저장된 현재 날짜를 반환하는 함수

SELECT SYSDATE

FROM DUAL;

 

13) 날짜 연산

- 날짜 형 데이터에 숫자를 더하면(날짜+숫자) 그 날짜로부터 그 기간만큼 지난 날짜를 계산

- 날짜 형 데이터에 숫자를 빼면(날짜-숫자) 그 날짜로부터 그 기간만큼 이전 날짜를 계산

SELECT SYSDATE-1 어제, SYSDATE 오늘, SYSDATE+1 내일

FROM DUAL;

 

2. 형 변환 함수

1) 문자형으로 변환하는 TO_CHAR 함수 날짜형을 문자형으로 변환

TO_CHAR (날짜 데이터, '출력형식')

SELECT SYSDATE, TO_CHAR(SYSDATE, 'YYYY-MM-DD')

FROM DUAL;

SELECT HIREDATE, TO_CHAR (HIREDATE, 'YYYY/MM/DD DAY')

FROM EMP;

시간 출력 형식 종류 표

종류	의미
AM 또는 PM A.M 또는 P.M HH또는 HH12 HH24 MI SS	오전(AM), 오후(PM) 시각 표시 오전(A.M), 오후(P.M) 시각 표시 시간(1~12) 24시간으로 표현(0~23) 분 표현 초 표현

 

2) 날짜형으로 변환하는 TO_DATE 함수

- 날짜 형으로 변환하기에 앞서 날짜 형에 대해서 살펴보도록 합시다. 날짜 형은 세기, 년도, 월, 일. 시간, 분. 초와 같이 날짜와 시간에 대한 정보를 저장

- 오라클에서 기본 날짜 형식은 ‘YY/MM/DD'형식으로 ’년/월/일‘ 예를 들면 ’06/03/08‘ 식으로 나타냅니다.

- 만일 년도를 4자리로 출력하려면 ‘YYYY/MM/DD' 형식으로 지정

- TO_DATE 함수는 문자열을 날짜 형으로 변환

 

TO_DATE(‘문자’, ‘format')

 

SELECT ENAME, HIREDATE FROM EMP

WHERE HIREDATE=TO_DATE(19810220,'YYYYMMDD')

 

올해 며칠이 지났는지 현재 날짜에서 2008/01/01을 뺀 결과를 출력해보자

SELECT TRUNC(SYSDATE-TO_DATE('2008/01/01', 'YYYY/MM/DD'))

FROM DUAL;

3) 숫자형으로 변환하는 TO_NUMBER 함수

- TO_NUMBER 함수는 특정 데이터를 숫자형으로 변환해 주는 함수

SELECT TO_NUMBER('20,000', '99,999') - TO_NUMBER('10,000', '99,999')

FROM DUAL;

4) NULL을 다른 값으로 변환하는 NVL 함수

- NVL 함수는 NULL을 0 또는 다른 값으로 변환하기 위해서 사용하는 함수

SELECT ENAME, SAL, COMM, SAL*12+COMM,

NVL(COMM, 0), SAL*12+NVL(COMM, 0)

FROM EMP

ORDER BY JOB;