2장. SQL 단일행 함수

함수의 종류

• 단일행 함수 : 데이터베이스에서 연산되는 단위가 각각의 행 단위로 실행된다.
• 복수행 함수 : 행이 입력되는 매개변수가 여러 개의 행에 있는 데이터를 받아서 실행된다.

 

단일행 함수의 종류

• 문자 함수
• 숫자 함수
• 날짜 함수
• 변환 함수: 묵시적 데이터형 변환 명시적 데이터형 변환
• 일반 함수

 

 

 

---

1. 문자 함수

함수명	의미	사용예
INITCAP	입력 값의 첫 글자만 대문자로 변환	INITCAP('abcd') → Abcd
LOWER	입력 값을 전부 소문자로 변환	LOWER('ABCD') → abcd
UPPER	입력값을 전부 대문자로 변환	UPPER('abcd') → ABCD
LENGTH	입력된 문자열의 길이 값을 출력	LENGTH('한글') → 2
LENGTHB	입력된 문자열의 길이의 바이트 값을 출력	LENGTHB('한글') → 4
CONCAT	두 문자열을 결합해서 출력. || 연산자와 동일	CONCAT('A', 'B') → AB
SUBSTR	주어진 문자에서 특정 문자만 추출	SUBSTR('ABC', 1, 2) → AB
SUBSTRB	주어진 문자에서 특정 바이트만 추출	SUBSTRB('한글', 1, 2) → 한
INSTR	주어진 문자에서 특정문자의 위치 추출	INSTR('A*B#', '#') → 4
INSTRB	주어진 문자에서 특정문자의 위치 바이트값 추출	INSTRB('한글로', '로') → 5
LPAD	주어진 문자열에서 왼쪽으로 특정 문자를 채움	LPAD('love', 6, '＊') → **love
RPAD	주어진 문자열에서 오른쪽으로 특정 문자를 채움	RPAD('love', 6, '＊') → love**
LTRIM	주어진 문자열에서 왼쪽의 특정 문자를 삭제함	LTRIM('*love', '＊') → love
RTRIM	주어진 문자열에서 오른쪽의 특정 문자를 삭제함	RTRIM('love*', '＊') → love
REPLACE	주어진 문자열에서 A를 B로 치환함	REPLACE('AB', 'A', E') → EB
REGEXP_REPLACE	주어진 문자열에서 특정패턴을 찾아 치환함	아래 예 참조
REGEXP_INSTR	주어진 문자열에서 특정패턴의 시작 위치를 반환	아래 예 참조
REGEXP_SUBSTR	주어진 문자열에서 특정패턴을 찾아 반환함	아래 예 참조
REGEXP_LIKE	주어진 문자열에서 특정패턴을 찾아 반환함	아래 예 참조
REGEXP_COUNT	주어진 문자열에서 특정패턴의 횟수를 반환	아래 예 참조

 

 

1) INITCAP() 함수

Initial : 처음 / Capital : 대문자

중간에 공백이 있을 경우 단어를 분리해서 각 단어의 첫 번째 문자를 대문자로 바꿔준다.

INITCAP(문자열-또는-컬럼명);

 

 

2) LOWER() / UPPER() 함수

lowercase : 소문자 / uppercase : 대문자

모두 소문자(LOWER) 혹은 대문자(UPPER)로 변환

LOWER(문자열-또는-컬럼명)
UPPER(문자열-또는-컬럼명)

 

 

3) LENGTH() / LENGTHB() 함수

LENGTH : 논리적으로 길이를 세는 것.

LENGTHB : Byte로 길이를 세는 것. 영어일 경우 1byte, 한글일 경우 2byte 사용.

LENGTH(컬럼-또는-문자열)
LENGTHB(컬럼-또는-문자열)

 

 

4) CONCAT() 함수

연결 연산자를 사용하는 것이 더 편하다.

CONCAT('문자열1', '문자열2')

 

 

5) SUBSTR() / SUBSTRB() 함수

SUB : 일부, STR : 문자열을 떼어 온다. / B : Byte

SUBSTR('문자열'-또는-컬럼명, 숫자1, 숫자2)
SUBSTRB('문자열'-또는-컬럼명, 숫자1, 숫자2)

 

 

6) INSTR() 함수

특정 문자열에 내가 원하는 문자열이 있는지 찾는다.

INSTR('문자열'-또는-컬럼, 찾는 글자, 시작위치, 몇번째인지(기본값은-1))

 

 

7) LPAD() / RPAD() 함수

L : LEFT, R : RIGHT / PAD : 덧댄다, 채워 넣는다.

LPAD('문자열'-또는-컬럼명, 자리수, '채울-문자')
RPAD('문자열'-또는-컬럼명, 자리수, '채울-문자')

 

 

8) LTRIM() / RTRIM() 함수

왼쪽(LTRIM) 혹은 오른쪽(RTRIM)에 불필요한 것을 제거한다.

LTRIM('문자열'-또는-컬럼명, '제거할-문자')
RTRIM('문자열'-또는-컬럼명, '제거할-문자')

 

 

9) REPLACE() 함수

바꿔치기하는 함수. 굉장히 많이 사용한다.

REPLACE('문자열'-또는-컬럼명, '문자1', '문자2')

 

 

 

---

 

 

 

2. 숫자 관련 함수

이름	의미	사용 예
ROUND	주어진 숫자를 반올림한 후 출력함	ROUND(12.345, 2) → 12.35
TRUNC	주어진 숫자를 버림한 후 출력함	TRUNC(12.345, 2) → 12.34
MOD	주어진 숫자를 나누기한 후 나머지 값 출력함	MOD(12, 10) → 2
CEIL	주어진 숫자와 가장 근접한 큰 정수 출력함	CEIL(12.345) → 13
FLOOR	주어진 숫자와 가장 근접한 작은 정수 출력함	FLOOR(12.345) → 12
POWER	주어진 숫자1의 숫자2 승을 출력함	POWER(3, 2) → 9

 

 

1) ROUND() 함수

반올림. 자리수를 잘 보기. 숫자 관련 함수는 자리 수 하나 차이가 엄청난 차이를 낼 수 있기 때문에 특히 주의해야 한다.

ROUND(숫자, 출력을-원하는-자리수)

 

 

2) TRUNC() 함수

ROUND 함수와 사용법은 동일. 잘라냄, 버림.

TRUNC(숫자, 원하는-자리수)

 

 

3) MOD() 함수 / CEIL() 함수 / FRLOOR() 함수

MOD() 함수 : 나머지 값. 나머지 값을 구하는 함수

CEIL() 함수 : 천장 값. 주어진 숫자에서 가장 가까운 큰 정수를 구하는 함수

FLOOR() 함수 : 바닥 값. 주어진 함수와 가장 가까운 작은 정수를 구하는 함수

# 숫자1을 숫자2로 나누었을 때 값
MOD(숫자1, 숫자2)

# 숫자3의 가장 가까운 큰 정수
CEIL(숫자3)

# 숫자4의 가장 가까운 작은 정수
FLOOR(숫자4)

 

 

4) POWER() 함수

숫자1의 숫자2의 승수를 구해준다.

POWER(숫자1, 숫자2)

 

 

 

---

 

 

 

3. 날짜 관련 함수들

함수명	의미	결과
SYSDATE	시스템의 현재 날짜와 시간	날짜
MONTHS_BETWEEN	두 날짜 사이의 개월 수	숫자
ADD_MONTHS	주어진 날짜에 개월을 더함	날짜
NEXT_DAY	주어진 날짜를 기준으로 돌아오는 날짜 출력	날짜
LAST_DAY	주어진 날짜가 속한 달의 마지막 날짜 출력	날짜
ROUND	주어진 날짜를 반올림	날짜
TRUNC	주어진 날짜를 버림	날짜

 

 

1) SYSDATE 함수

서버의 시간 변경 조심하기

SELECT SYSDATE
FROM 테이블;

 

 

2) MONTHS_BETWEEN 함수

몇 개월 차이가 나는지 본다.

MONTHS_BETWEEN('날짜1', '날짜2')
MONTHS_BETWEEN('연/월/일', '연/월/일')

 

(1) 두 날짜 중 큰 날짜를 먼저 써야 양수가 나온다.

(2) 두 날짜가 같은 달에 속해 있으면 특정 규칙으로 계산된 값이 나온다

     (같은 1개월이라도 29일, 30일, 31일일 경우마다 모두 결과값이 다르다)

 

 

3) ADD_MONTHS() 함수

괄호 안에 추가될 월 수를 넣으면 계산된 값이 나온다. 컬럼값에 있는 날짜를 '번호'개월만큼 올린다.

ADD_MONTHS(컬럼값, 번호)
ADD_MONTHS('연/월/일', 문자-또는-숫자)
ADD_MONTHS(SYSDATE, 문자-또는-숫자)

 

 

4) NEXT_DAY() 함수

지정한 날짜(컬럼값)에서 얼마나 지나서 원하는 날짜(문자열)가 나오는지 출력

NEXT_DAY(컬럼값, 문자열)
NEXT_DAY(SYSDATE, 문자)

 

 

5) LAST_DAY() 함수

날짜를 기준으로 월말이 며칠인지 출력

LAST_DAY('연/월/일')
LAST_DAY(SYSDATE)

 

 

6) 날짜의 ROUND(), TRUNC() 함수

ROUND : 반올림 / TRUNC() : 버림

ROUND(SYSDATE)
TRUNC(SYSDATE)

 

 

 

---

 

 

 

4. 형 변환 함수(11g 기준)

문자열을 데이터나 숫자로 바꾸는 것

데이터 타입	설명
CHAR(n)	고정 길이의 문자를 저장합니다. 최대값은 2000bytes입니다.
VAR CHAR2(n)	변하는 길이의 문자를 저장합니다. 최대값은 4000bytes입니다.
NUMBER(p, s)	숫자값을 저장합니다. p는 전체 자리수로 1 ~ 38자리까지 가능하고 s는 소수점 이햐 자리수로 -84 ~ 127자리까지 가능합니다.
DATE	총 7Byte로 BC 4712년 1월 1일부터 AD 9999년 12월 31일까지의 날짜를 저장할 수 있습니다.
LONG	가변 길이의 문자를 저장하며 최대 2GB까지 저장할 수 있습니다
CLOB	가변 길이의 문자를 저장하며 최대 4GB까지 저장할 수 있습니다
BLOB	가변 길이의 바이너리 데이터를 최대 4GB까지 저장할 수 있습니다
RAW(n)	원시 이진 데이터로 최대 2000bytes까지 저장할 수 있습니다
LONG RAW(n)	원시 이진 데이터로 최대 2GB까지 저장할 수 있습니다
BFILE	외부 파일에 저장된 데이터로 최대 4GB까지 저장할 수 있습니다

 

 

1) 묵시적(자동) 형 변환과 명시적(수동) 형 변환

• 묵시적 형 변환 : 자동으로 컴퓨터가 알아서 해주는 것
• 명시적 형 변환 : 사람이 수동으로 맞춰주는 것

 

숫자처럼 생긴 문자만 변환이 가능하다.

SELECT 숫자 + '숫자'
FROM 테이블;

SELECT 숫자 + TO_NUMBER('숫자')
FROM 테이블;

 

 

명시적 형 변환

 

 

2) TO_CHAR 함수

TO_CHAR(원래-날짜, '원하는-모양')

• 'YYYY' : 연도를 4자리로 표현한다.  ex) 2014
• 'RRRR' : 2000년 이후에 Y2K 버그로 인해 등장한 날짜 표기법. 연도 4자리 표기법
• 'YY' : 연도를 끝의 2자리만 표시한다.  ex) 14
• 'RR' : 연도를 마지막 2자리만 표시한다.  ex) 14
• 'YEAR' : 연도의 영문 이름 전체를 표시한다.

[ 월 ]

• MM : 월을 숫자 2자리로 표현한다.  ex) 10
• MON : 유닉스용 오라클에서 월을 뜻하는 영어 3글자로 표시한다.  ex) OCT
  윈도우용 오라클일 경우 MONTH와 동일하다.
• MONTH : 월을 뜻하는 이름 전체를 표시한다.

[ 일 ]

• DD : 일을 숫자 2자리로 표시한다.  ex) 26
• DAY : 요일에 해당하는 명칭을 표시하는데 유닉스용 오라클에서는 영문으로, 윈도우용 오라클에서는 한글로 나온다.
• DDTH : 몇 번째 날인지를 표시한다.

[ 시간 ]

• HH24 : 하루를 24시간으로 표시한다.
• HH : 하루를 12시간으로 표시한다.
• 분 : MI로 표시한다.
• 초 : SS로 표시한다.

 

 

3) TO_CHAR 함수 (숫자형 → 문자형으로 변환하기)

종류	의미	사용 예	결과
9	9의 개수만큼 자리수	TO_CHAR(1234, '99999')	1234
0	빈자리를 0으로 채움	TO_CHAR(1234, '099999')	001234
$	$ 표시를 붙여서 표시	TO_CHAR(1234, '$9999')	$1234
.	소수점 이하를 표시	TO_CHAR(1234, '9999.99')	1234.00
,	천 단위 구분 기호를 표시	TO_CHAR(12345, '99,999')	12,345

 

 

4) TO_NUMBER() 함수

숫자가 아닌, 숫자처럼 생긴 문자를 숫자로 바꾸어 주는 함수.

TO_NUMBER('숫자처럼-생긴-문자')

# ex
SELECT TO_NUMBER('5') FROM emp;

 

 

5) TO_DATE() 함수

문자열을 넣으면 데이터로 바꾸어 준다.

TO_DATE('문자')

 

 

 

---

 

 

 

5. 일반 함수

 

1) NVL() 함수

NULL 값을 만나면 다른 값으로 치환해서 출력하는 함수

NVL(컬럼, 치환할-값)

 

* 치환할 값이 숫자일 경우

# 컬럼의 값이 null일 경우 null 대신 0으로 치환하라
NVL(컬럼, 0)

# 컬럼의 값이 null일 경우 null 대신 100으로 치환하라
NVL(컬럼, 100)

 

* 치환 값이 숫자일 경우

# 컬럼의 값이 null일 경우 '문자'로 치환하라
NVL(컬럼, '문자')

 

* 치환 값이 날짜일 경우

# 컬럼의 값이 null일 경우 '연/월/일'로 치환하라
NVL(컬럼, '연/월/일')

 

 

2) NVL2() 함수

NVL2(컬럼1, 컬럼2, 컬럼3)

 

 

3) DECODE() 함수

 일반 개발 언어 등에서 사용 중인 분기문인 if 문을 오라클 안으로 가져오는 함수이다. if 문의 역할을 해준다. 오라클에서만 사용되는 함수이다. if 문을 사용해야 하는 조건 문을 처리할 수 있다.

 

유형 1. A가 B일 경우 '1' 출력

DECODE(A, B, '1', null) # null은 생략 가능

 

유형 2. A가 B일 경우 '1' 출력,

아닐 경우 '2' 출력

DECODE(A, B, '1', '2')

 

유형 3. A가 B일 경우 '1'을 출력, A가 C일 경우 '2'를 출력,

둘 다 아닐 경우 '3' 출력

DECODE(A, B, '1', C, '2', '3')

 

유형 4.

A가 B일 경우 중 C가 D를 만족하면 '1' 출력,

C가 아닐 경우 NULL 출력

(DECODE 함수 안에 DECODE 함수가 중첩되는 경우)

DECODE(A, B, DECODE(C, D, '1', null)) # null 생략 가능

 

유형 5.

A가 B일 경우 중에서 C가 D를 만족하면 '1' 출력,

C가 D가 아닐 경우 '2' 출력

DECODE(A, B, DECODE(C, D, '1', '2'))

 

유형 6

A가 B일 경우 중에서 C가 D를 만족하면 '1' 출력,

C가 D가 아닐 경우 '2' 출력,

A가 B가 아닐 경우 '3' 출력

DECODE(A, B, DECODE(C, D, '1', '2'), '3')

 

 

4) CASE 문

CASE 조건 WHERE 결과1 THEN 출력1
	  [WHEN 결과2 THEN 출력2]
	  ELSE 출력3
END "컬럼명"

 

 

 

---

 

 

 

6. 정규식(Regular Expression) 함수로 다양한 조건 조회하기

10g부터 추가되었다. 특정 패턴을 찾는 것.

사용 기호	의미	사용 예
^ (캐럿)	해당 문자로 시작하는 line 출력	'^pattern'
$ (달러)	해당 문자로 끝나는 line 출력	'pattern$'
.	S로 시작하여 E로 끝나는 line ( . → 1 character )	'S....E'
*	'모든'이라는 뜻. 글자 수가 0일 수도 있음.	'[a-z]*'
[ ]	해당 문자에 해당하는 한 문자	'[Pp]attern'
[ ^ ]	해당 문자에 해당하지 않는 한 문자	'[^a-m]attern'

 

 

1) REGEXP_LIKE 함수

REGEXP_LIKE(text, '[첫문자-끝문자]')

# 공백이 있는 모든 행 출력
REGEXP_LIKE(text, '[[:space:]]')

 

 

2) REGEXP_REPLACE 함수

REGEXP_REPLACE (source_char, pattern
	    [, replace_string
            [, position
            [, occurence
            [, match_param]]]]
            )

 

 

3) REGEXP_SUBSTR 함수

REGEXP_SUBSTR('문자열1* *문자열2 $문자열3%문자열4', '[^ ]+[문자열]')

 

 

 

---

 

 

 

7. 11g에 추가된 정규식 함수

 

1) REGEXP_COUNT 함수

특정 문자의 개수를 세는 함수

REGEXP_COUNT(text, '문자')