단일행 함수

함수

 : 어떤 입력 값을 받아서 정해준 루틴에 의해 작업을 하고 결과값을 만들어서 출력해주는 것

 

단일행 함수 

 : 한 번에 하나씩 처리하는 함수

 

데이터 종류에 따른 구분

 

문자함수

SELECT 문자함수(컬럼명) FROM 테이블명;

함수명	의미	사용 예
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

 

숫자 함수

SELECT 숫자함수(컬럼명) FROM 테이블명;

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

 

날짜 함수

 : 날짜 + 숫자 = 날짜

 : 날짜 - 숫자 = 날짜

 : 날짜 - 날짜 = 숫자

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

 

SYSDATE 함수

SELECT SYSDATE FROM 테이블명;

Dummy TALBE dual

 

MONTHS_BETWEEN 함수

 : 작은 날짜를 먼저 쓰면 음수가 나옴

 : 윤일(2월 29일)까지 있는 달은 구분하지 못함

 : 1일 또는 그 달의 마지막일로 조회하는 것이 아니면 소수점 자리까지 나옴( 문제 발생할 수도 있음 )

SELECT MONTHS_BETWEEN('날짜1', '날짜2') FROM 테이블명;

 

ADD_MONTHS 함수

SELECT ADD_MONTHS('날짜', 증가시킬 달) FROM 테이블명;

 

NEXT_DAY 함수

 : 유닉스에서는 요일 입력을 'MON'과 같이 해야함 (유닉스가 아니라 테스트 못해봤습니다..)

 : 윈도우에서는 '월' 혹은 '월요일' 같이 입력해야함

 : 윤달 계산 가능

SELECT NEXT_DAY('날짜', '요일') FROM 테이블명;

 

LAST_DAY 함수

: 윤달도 계산 해줌

SELECT LAST_DAY('날짜') FROM 테이블명;

 

날짜 ROUND( ), TRUNC( ) 함수

SELECT ROUND('날짜') FROM 테이블명;
SELECT TRUNC('날짜') FROM 테이블명;

 

변환 함수

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

 

묵시적 형 변환

 : 오라클이 자동으로 형 변환을 시킴

숫자 + 숫자 형태의 문자

 

명시적 형 변환

 : 사람이 수동으로 지정해 주어야 함

숫자 + 형변환된 숫자 형태의 문자

TO_CHAR 함수

SELECT TO_CHAR('변환할 문자') FROM 테이블명;

 : 날짜 → 문자로 형 변환 (2020-09-01 오후 04시 58분 59초 기준)

형식	설명	표시 예
YYYY	연도를 4자리로 표현	2020
RRRR	2000년 이후에 Y2K 버그로 인해 등장한 날짜 표기법. 연도 4자리 표기법	2020
YY	연도의 끝의 2자리만 표시	20
RR	연도의 마지막 2자리만 표시	20
YEAR	연도의 영문 이름 전체를 표시	TWENTY TWENTY
MM	월을 숫자 2자리	09
MON	유닉스용 오라클에서 월을 뜻하는 영어 3글자로 표시 윈도우용 오라클일 경우는 MONTH와 동일	유닉스 : SEP 윈도우 : 9월
MONTH	월을 뜻하는 이름 전체를 표시합니다	유닉스 : SEPTEMBER 윈도우 :9월
DD	일을 숫자 2자리로 표시	01
DAY	요일에 해당하는 명칭을 표시 유닉스는 영문, 위도우는 한글	유닉스 : TUESDAY 윈도우 : 화요일
DDTH	몇 번째 날인지를 표시	01ST
HH24	하루를 24시간으로 표시	2020-09-01 : 16 : 48 : 59
HH	하루를 12시간으로 표시	2020-09-01 : 04 : 48 : 59
분	MI로 표시	(YYYY-MM_DD:HH:MI:SS)
초	SS로 표시

 

 : 숫자형 → 문자형으로 변환

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

 

TO_NUMBER 함수

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

SELECT TO_NUMBER('문자') FROM 테이블명;

 

ASCII 함수

 : 아스키 코드 값으로 출력해주는 함수

SELECT ASCII('문자') FROM 테이블명;

 

TO_DATE 함수

 : 날짜가 아닌 날짜처럼 생긴 문자를 날짜로 바꿔주는 함수

 : 유닉스와 윈도우는 날짜 형태가 다르기 때문에 유닉스에서는 설정 변경 해줘야함

 

일반 함수

SELECT 일반함수() FROM 테이블명;

종류	설명	형식
NVL	NULL 값을 만나면 다른 값으로 치환해서 출력하는 함수	NVL(컬럼명, 치환할 값)
NVL2	NVL 함수의 확장으로 NULL 값이 아닐 경우 출력할 값을 지정하는 함수.	NVL2(컬럼명, NULL값이 아닐 때, NULL값일 때)
DECODE	일반 개발 언어 등에서 사용 중인 분기문인 IF문을 오라클 SQL 안으로 가져온 함수	아래에서 설명
CASE	일반 개발 언어 등에서 사용 중인 CASE문과 비슷 DECODE는 equals 값을 처리하는데 주로 사용 즉, 크거나 작은 조건을 처리할 경우는 불편함	CASE 조건 WHEN 결과1 (추가조건) THEN 출력1               WHEN 결과2 (추가조건) THEN 출력2               ...              ELSE 출력3              END '컬럼명'

DECODE

 : false 값에 null을 입력하는 경우 생략 가능

 : 조건에 대하여 true 일 때와 false 일 때 출력 값을 정해줌

DECODE(컬럼명, 조건, true 출력 값, false 출력 값)

 

 

 : 조건1이 true 일 때 조건1의 true 출력 값을 출력

 : 조건1은 false이고 조건2가 true 일 때 조건2의 true 출력값을 출력

 : 둘다 false 일 때 false 출력값을 출력함

DECODE(컬럼명, 조건1, 조건1 true 출력값, 조건2, 조건2 true 출력값, false 출력값)

 

: 조건1이 false 일 때 false 출력값1을 출력 ( false 출력값1을 null로 설정할 시 생략 가능)

 : 조건1을 만족하면서 조건2를 만족하면 true 출력값, 아닐 땐 false 출력값2

DECODE(컬럼명1, 조건1, DECODE(컬럼명2, 조건2, true 출력 값, false 출력값2), false 출력값1)

 

정규식 함수

 : Regular Expression

SELECT 컬럼명 FROM 테이블명 WHERE REGEXP_정규식함수명(정규식);

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

 

t_reg 테이블

CREATE TABLE t_reg
( text varchar2(10)) ;

INSERT ALL
INTO t_reg VALUES('ABC123')
INTO t_reg VALUES('ABC 123')
INTO t_reg VALUES('ABC  123')
INTO t_reg VALUES('abc 123')
INTO t_reg VALUES('abc  123')
INTO t_reg VALUES('a1b2c3')
INTO t_reg VALUES('aabbcc123')
INTO t_reg VALUES('?/!@#$*&')
INTO t_reg VALUES('\~*().,')
INTO t_reg VALUES(123123)
INTO t_reg VALUES('123abc')
INTO t_reg VALUES('abc')
SELECT  *  FROM dual ;

REGEXP_LIKE 함수

 : like 함수처럼 특정 패턴과 매칭되는 결과를 검색해내는 함수

 

 : 영문자가 들어가 있는 행만 출력

 : 소문자로 시작하고 공백을 포함하는 경우 찾기

 : 연속적인 글자 수 지정하기

 : 특정 위치를 지정하여 출력 (첫 글자가 소문자거나 숫자일 경우)

 : 특정 위치를 지정하여 출력 (첫 글자가 숫자가 아닐 경우)

 : 특수문자 찾기 ('*')

 : 문자가 대문자를 포함하는 값 찾기 (소문자는 lower)

 

REGEXP_REPLACE 함수

 : REPLACE 함수를 확장한 개념

 : 주어진 문자열에서 특정 패턴을 찾아서 주어진 다른 모양으로 치환하는 함수

 

: 모든 숫자를 특수 문자 기호로 변경

 : 특정 패턴을 찾아서 패턴을 변경

 : 문자 가운데 공백 제거

( ) 괄호 안에있는 값(띄어쓰기)이 {1,} 1번이상 반복인 곳을 찾아서 ''값으로 치환해라.

 

 

REGEXP_SUBSTR 함수

 : 첫 글자가 공백이 아닌 부분부터 해당 문자까지 출력

 

REGEXP_COUNT 함수

 : 11g에서 추가된 정규식 함수

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

 

정규 표현식(구문 클래스)

구문 클래스	의미
[[:digit:]]	숫자
[[:alpha:]]	문자
[[:alnum:]]	문자 또는 숫자
[[:upper:]]	대문자
[[:lower:]]	소문자
[[:space:]]	공백 문자
[[:xdigit:]]	16진수 숫자
[[:cntrl:]]	제어 문자
[[:ascii:]]	ASCII 문자