SQL 기초3- 기본 함수

SQL 기초2에서는 SQL에서 가장 자주 쓰이는 기본적인 문법에 대해 다루어 보았다.

이번 글에서는 SQL에서 제공하는 함수들에 대해서 알아보고자 한다.

 

오라클에서는 내장함수를 기본적으로 제공하고 있다.

그 함수들을 잘 활용한다면 원하는 더 용이하고 다양하게 데이터를 다룰 수 있을 것이다. 

 

내장함수에는 단일행 함수와 다중행 함수가 있다.

단일행 함수= 데이터가 한 행씩 입력되고, 입력된 한 행당 결과가 하나씩 나오는 함수

다중행 함수= 데이터가 여러 행이 입력되지만, 결과가 하나의 행으로 나오는 함수

 

그럼 여러 함수들에 대하여 공부해보자..

 

1. 문자 함수 : 문자 데이터를 가공하는데 사용하는 함수

1) UPPER(문자열) - 괄호 안의 문자 데이터를 모두 대문자로 변환 

2)LOWER(문자열) - 괄호 안의 문자 데이터를 모두 소문자로 변환 

3)INITCAP(문자열) - 괄호 안의 문자 데이터 중 첫 글자는 대문자로, 나머지는 소문자로 변환

4)LENGTH(문자열) - 괄호 안의 문자 데이터의 길이를 반환

5)LENGTHB(문자열) - 괄호 안의 문자 데이터의 바이트 수를 반환

 

6)SUBSTR(문자열 데이터, 시작위치, 추출길이) - 문자열 데이터의 시작 위치부터 추출 길이만큼 추출

(단, 문자열의 시작은 0이 아니라 1부터)

   SUBSTR(문자열 데이터, 시작위치) - 문자열 데이터의 시작 위치부터 끝까지 추출

 

7)INSTR(문자열 데이터, 위치를 찾으려는 문자, 위치 찾기를 시작할 위치, 몇 번째 문자를 찾을 것인지 지정)

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

SELECT INSTR('HELLO, ORACLE!', 'L'), -- 3
INSTR('HELLO, ORACLE!', 'L', 5), -- 12
INSTR('HELLO, ORACLE!', 'L', 2, 2) FROM dual; -- 4

 

8)REPLACE(문자열 데이터, 찾는 문자, 대체할 문자)

만약 대체할 문자를 생략한다면, 찾는 문자는 삭제된다

SELECT REPLACE('010-1234-5678', '-', ' ') ~ -- 010 1234 5678
SELECT REPLACE('010-1234-5678', '-') ~ -- 01012345678

 

9)LPAD(문자열 or 컬럼명, 데이터의 자릿수, 빈공간에 채울 문자) -왼쪽 패딩/

데이터가 지정한 길이보다 짧을 경우에 남은 공간만큼 왼쪽에 특정 문자로 채움 

10)RPAD(문자열 or 컬럼명, 데이터의 자릿수, 빈공간에 채울 문자) -오른쪽 패딩/

데이터가 지정한 길이보다 짧을 경우에 남은 공간만큼 오른쪽에 특정 문자로 채움 

SELECT RPAD('Oracle', 10, '#'), LPAD('Oracle', 10) FROM dual;
-- Oracle####,     Oracle

11) CONCAT(컬럼1, 컬럼2) - 컬럼1과 컬럼2를 하나로 합치는 함수

      CONCAT(컬럼1, CONCAT('사이에 넣고 싶은 문자', 컬럼2)) - 컬럼1과 컬럼2 사이에 넣고 싶은 문자로 넣어서 합침

SELECT CONCAT(empno, ename) from emp WHERE ename='scott';
-- 7788SCOTT

12)TRIM/RTRIM/LTRIM - 양쪽/오른쪽/왼쪽 특정 문자 제거

 

 

 

2. 숫자 함수

1)ROUND(숫자, 반올림 위치) - 특정 위치에서 반올림

2)TRUNC(숫자, 버림 위치) - 특정 위치에서 버림

3)CEIL(숫자) - 해당 숫자와 가까운 정수 중에서 가장 큰 정수 

4)FLOOR(숫자) - 해당 숫자와 가까운 정수 중에서 가장 작은 정수 

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

 

4)FLOOR(숫자) - 해당 숫자와 가까운 정수 중에서 가장 작은 정수 

5)MOD(나눠질 숫자, 나눌 숫자) - 나머지 출력

 

6)COUNT(칼럼) - 해당 칼럼의 갯수를 반환

7)LIMIT A, B - A번째부터 B개를 가져오라는 뜻( 0부터 시작)

-- 데이터를 5개만 가져오기
SELECT * FROM book LIMIT 5;
-- 데이터를 2번째부터 5개를 가져오기(첫 번째가 0)
SELECT * FROM book LIMIT 1,5;

 

8)AVG(칼럼) - 해당 칼럼의 데이터의 평균

9)SUM(칼럼) - 해당 칼럼의 데이터의 총 합

10)MAX(칼럼)  - 해당 칼럼의 데이터의 최대값

11)MIN(칼럼)  - 해당 칼럼의 데이터의 최소값

 

 

 

3. 날짜 함수: 날짜를 다루는 함수

날짜 데이터 + 숫자 => 해당 날짜보다 숫자만큼의 일수 이후의 날짜

날짜 데이터 - 숫자  => 해당 날짜보다 숫자만큼의 일수 이전의 날짜

날짜 데이터 -날짜 데이터 => 두 날짜 데이터 간의 일수 차이

날짜 데이터 + 날짜 데이터 => 연산 불가(지원 안함)

 

1)SYSDATE - 현재 날짜 출력

SELECT SYSDATE FROM dual;

2)ADD_MONTHS(날짜 데이터, 더할 개월 수) - 날짜 데이터에 숫자만큼의 개월을 더함

3)MONTHS_BETWEEN(날짜1, 날짜2) - 두 날짜 간의 날짜 차이를 개월 수로 계산하여 출력

4)NEXT_DAY(날짜 데이터, 요일 문자) - 특정 날짜를 기준으로 돌아오는 요일의 날짜를 출력해주는 함수

5)LAST_DAY(날짜 데이터) - 특정 날짜가 속한 달의 마지막 날짜를 출력해주는 함수

 

 

 

4. 형 변환 함수

1) TO_CHAR(날짜 데이터, 출력되길 원하는 문자형태) - 숫자 또는 날짜 데이터를 문자 데이터로 변환

2) TO_NUMBER(문자열 데이터, 인식될 숫자 형태) - 문자 데이터를 숫자 데이터로 변환

3) TO_DATE(문자열 데이터, 인식될 날짜 형태) - 문자 데이터를 날짜 데이터로 변환

 

 

 

5. NULL 처리 함수

1)NVL(널 여부를 검사할 컬럼, 데이터가 널일 경우 반환할 데이터)

SELECT empno, ename, sal, comm, sal+comm,
	NVL(comm, 0)
    SAL+NVL(comm, 0)
  FROM EMP;
  
  -- comm이 널이면 0으로 반환하여 그 데이터를 특정 열의 데이터와 더함

 

 

2)NVL2(널 여부를 검사할 컬럼, 데이터가 널이 아닐 경우 반환할 데이터, 데이터가 널일 경우 반환할 데이터)

SELECT empno,ename, comm,
		NVL2(comm, 'O', 'X')
        NVL2(comm, sal*12+comm, sal*12)
    FROM EMP;
-- comm이 널일 경우 더할 필요도 없음