2. SQL 단일행 함수

[기타SQL]/oracle SQL

2. SQL 단일행 함수

JJ* 2014. 8. 24. 18:59

1. 문자 함수

1) INITCAP(문자열 또는 칼럼명) : 첫글자만 대문자로 출력하고 나머지는 전부 소문자로 출력 (중간에 공백이 있을 경우 그 다음 글자는 대문자로 바꿔줌 )

2) LOWER (문자열 또는 칼럼명) : 전부 소문자로 변경하여 출력

3) UPPER (문자열 또는 칼럼명) : 전부 대문자로 변경하여 출력

4) LENGTH(문자열 또는 칼럼명) : 입력된 문자열의 길이를 문자수로 계산

5) LENGTHB(문자열 또는 칼럼명) : 입력된 문자열의 길이를 byte수로 계산 (한글을 저장할 경우 1글자당 2byte 사용)

6) CONCAT(문자열1, 문자열2) : ||연산자와 동일하며 ||연산자를 더 많이 씀

7) ★★SUBSTR('문자열'또는칼럼명 , x번째자리부터 , x개) : 특정길이의 문자를 골라낼 때

- 시작위치의 숫자를 '-'로 주면, 뒤에서부터 오른쪽자릿수를 계산한다.

8) ★SUBSTRB('문자열'또는칼럼명, X번째자리부터, x byte) : 특정길이의 문자를 골라낼 때.

9) ★INSTR('문자열'또는칼럼명, 찾는글자, x번째자리부터, x번째) :특정글자의 위치를 찾아줌. (x번째 안쓰면 기본값1)

10) LPAD('문자열'또는칼럼명, 자리수, '채울문자') : 데이터를 오른쪽에 두고, 왼쪽에 빈자리가 있을 경우 특정기호나 문자로 채움.

11) RPAD('문자열'또는칼럼명, 자리수, '채울문자') : 데이터를 왼쪽에 두고, 오른쪽에 빈자리가 있을 경우 특정기호나 문자로 채움.

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

13) ★★REPLACE('문자열'또는칼럼명,'문자1을','문자2로') : 문자열이나 칼럼에서 문자1을 문자2로 바꾸어 출력

2. 숫자함수

1) ROUND(숫자, 소수점기준으로 출력원하는 자리수) : 반올림

2) TRUNC(숫자, 소수점기준으로 버림을 원하는 자리수) : 버림

3) MOD(숫자, 를나눌숫자) : 나머지값

4) CEIL(숫자) : 주어진 숫자의 가장 가까운 큰 정수를 구함 (여러 데이터들을 하나의 기준으로 묶을 때)

5) FLOOR(숫자) : 주어진 숫자의 가장 가까운 작은 정수를 구함

6) POWER(숫자1의, 숫자2의 승수)

3. 날짜 함수

1) SYSDATE 함수 : 시스템의 현재 날짜와 시간. (절대로 서버의 시간은 함부로 바꾸면 안된다.)

> select sysdate from dual;

2) MONTHS_BETWEEN('큰 날짜' , '작은 날짜' ) : 두 날짜 사이의 개월 수 출력 (큰날짜 먼저써야 양수가 나옴)

- 윤달을 구분하지 못한다.

* 유닉스용 오라클날짜형식(일-월-년)에서 윈도용날짜형식(년-월-일)로 바꾸기.

>ALTER SESSION SET NLS_DATE_FORMAT='YY/MM/DD' ;

- MONTHS_BETWEEN을 쓰는지, 그냥 1개월을 31일로 나눌지(회사입장에서좋음)에 따라 엄청난 돈의 차이가 날 수 있으니 조심.

3) ADD_MONTH(날짜, 추가를원하는개월수)

4) NEXT_DAY(날짜, '요일') : 주어진 날짜를 기준으로 돌아오는 가장 최근 요일의 날짜를 반환

- 윈도용 오라클에서는 '수' 처럼 한글로 써야하고, 리눅스용 오라클에서는 'WED' 처럼 영어로 써야한다.

5) LAST_DAY(날짜) : 주어진 날짜가 속한 달의 마지막날짜 출력.

6) ROUND(날짜) : 주어진 날짜가 낮12:00:00 을 넘어설 경우 다음 날짜로 출력하고, 이 시간이 안될 경우 당일로 출력.

7) TRUNC(날짜) : 주어진 날짜를 무조건 당일로 처리

(*) SYSDATE 써먹는 예

SQL> create table jumun ::테이블생성
2 (no number,
3 name varchar2(10),
4 rdate date default sysdate); ::사용자가 날짜를 따로 입력하지않으면, 현재시간으로 날짜를 출력함.

Table created.

SQL> insert into jumun(no, name) ::주문들어오기시작.
2 values(1, 'AAA');

1 row created.

SQL> insert into jumun (no, name)
2 values(2, 'BBB');

1 row created.

SQL> insert into jumun(no, name)
2 values(3,'CCC');

1 row created.

SQL> select * from jumun;

        NO NAME       RDATE
---------- ---------- ------------
         1 AAA        29-SEP-14
         2 BBB        29-SEP-14
         3 CCC        29-SEP-14

SQL> alter session set nls_date_format='yyyy-mm-dd:HH24:MI:SS'; :: 날짜형태변환.

Session altered.

SQL> select * from jumun;

        NO NAME       RDATE
------- --------- -------------------
         1    AAA       2014-09-29:17:54:34           :: 현재시간으로 출력되었음.
         2   BBB        2014-09-29:17:54:46
         3   CCC        2014-09-29:17:55:00

4. 형변환함수

1) 묵시적 형 변환 (자동)

2) 명시적 형 변환 (수동)

날짜 : date -> 9999.12.31 까지

숫자 : number(전체p, 소수점이하s) _ ex) number(5,2) : 123.54 => 최대38자리수까지.

문자 : char(고정길이) :최대 2000byte까지 가능.

Carchar2 (가변길이) : 최대 4000byte까지 가능.

3) TO_CHAR(원래날짜, '원하는모양') : 날짜를 문자로 형 변환

- 년도

YYYY : 연도를 4자리로 표현

RRRR : 2000년 이후에 등장한 연도 4자리 표기법

( 주민번호 앞자리가 140101일경우, 2014년생인지 1914년생인지.. => 무조건2000년이후로 따짐.)

YY : 연도를 끝의 2자리만 표시

RR : 연도를 끝의 2자리만 표시

YEAR : 연도의 영문이름 전체

- 월

MM : 월을 숫자 2자리로 표현

MON : 유닉스용에서 영어 3글자로 표시 (윈도용 오라클에서는 월을 뜻하는 이름 전체 표시)

MONTH : 월을 뜻하는 이름 전체 표시

- 일

DD : 일을 숫자 2자리로 표시

DAY : 리눅스용에서는 영문으로 명칭 표시, 윈도용에서는 한글로 표시

DDTH : 몇번째 날인지 표시 (26TH)

- 시간

HH24 : 하루를 24시간으로 표시(보통 이걸로 씀)

HH : 하루를 12시간으로 표시

MI : 분

SS : 초

4) TO_CHAR (숫자형 -> 문자형으로 변환)

5) TO_NUMBER('숫자처럼생긴문자') : 숫자처럼 생긴 문자를 숫자로 바꾸어 주는 함수

6) TO_DATE('날짜처럼생긴문자') : 날짜처럼 생긴 문자를 날짜로 바꾸어 주는 함수

5. 일반함수 ★★★

1) NVL(칼럼, 치환할 값) : NULL값을 다른 값으로 치환해서 출력 (두개가 데이터타입이 같아야 된다.)

2) NVL2(COL1, COL2, COL3) : COL1의 값이 NULL이 아니면 COL2, NULL이면 COL3출력

3) DECODE함수 ★★★★

DECODE(A,B,'1')	A가 B일 경우 '1'을 출력하고, 아니면 NULL을 출력
DECODE(A,B,'1','2')	A가 B일 경우 '1'을 출력하고, 아닐 경우 '2'를 출력
DECODE(A,B,'1',C,'2','3')	A가 B일경우 '1'을 출력하고, C일경우 '2'를 출력하고, 둘 다 아닐 경우 '3'을 출력.★★
DECODE(A,B,DECODE(C,D,'1'))	A가 B일 경우에서, C가 D를 만족하면 '1'출력하고, C가 아닐경우 NULL출력 (중첩)
DECODE(A,B,DECODE(C,D,'1','2'))	A가 B일 경우 중에서, C가 D를 만족하면 '1'출력하고, D가 아닐경우 '2'출력하고, A가 B 아닐 경우 NULL출력 (중첩)
DECODE(A,B,DECODE(C,D,'1','2'),'3')	A가 B일 경우 중에서, C가 D를 만족하면 '1' 출력하고, D가 아닐경우 '2'출력하고, A가 B 아닐경우 '3'을 출력(중첩)

답)

SQL> select name, jumin, decode(substr(jumin,7,1),1, '남자','여자')"성별"
2 from student
3 where deptno1=101;

답)

SQL> select name, tel, decode(substr(tel, 1,instr(tel, ')')-1), '02', '서울',
2 '031', '경기', '051' , '부산', '경남') "지역"
3 from student
4 where deptno1=101;

4) CASE문 (콤마붙이지 말 것.)

CASE (조건) WHEN 결과1 THEN 출력1

WHEN 결과2 THEN 출력2

ELSE 출력3

END "칼럼명"

예1) '='조건으로 사용되는 경우 (DECODE문이 더 사용하기 편리하다.)

예2) '='조건이 아닌 경우

답)

1 select empno, ename, sal,
2     case when sal between 1 and 1000 then 'level1'
3             when sal between 1001 and 2000 then 'level2'
4             when sal between 2001 and 3000 then 'level3'
5             when sal between 3001 and 4000 then 'level4'
6             else 'level5'
7     end "LEVEL"
8 from emp

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

ex) 시작이 숫자가 아닌 : ^[^0-9]

1) ★REGEXP_LIKE(칼럼, '패턴조건') : 특정패턴과 매칭되는 결과를 검색

: 대소문자를 한번에 찾아서 출력

: 소문자로 시작, 공백한칸 포함, 숫자로 끝

: 소문자먼저, 그다음 숫자 붙어있는 것?

: 소문자와공백과숫자를 포함한 모든것.

: 소문자나 숫자를 포함한 모든것.

: 공백이 있는 데이터 모두 찾고 싶은 경우

: 영어 대문자와 숫자 함께 나오되 ,각각 3글자이상 오는 경우 출력

: 숫자먼저 영어대문자와 함께나오되, 각각 3글자 이상 오는 경우 출력(검색결과 없음)

: 시작되는 문자를 숫자나 대문자로 지정 (^)

: 연결연산자(|)사용, 소문자로시작하거나 숫자로시작

: 대소문자(알파벳)으로 끝나는 행 (뒤에 $)

: 시작이 소문자가 아닌

: '~가 들어간 모든행 제거'하고 싶은 경우에는 그것이 들어가 있는 모든 행을 출력한 후 NOT연산자로 제거한다.

: 시작이 숫자두자리로 시작하고, 문자 ')'을 포함하고 국번이 연속 4자리 나오는값.

: 시작이 3글자, 네번째가 v

: 1이나 7이나 2 세글자로 시작하고, 온점포함, 1이나 6 두글자, 온점포함, 1이나 6이나 8 세글자

: 특정조건을 제외한 결과 (NOT)

: 소문자가 들어있는 모든행('*'위치 주의) (*의 위치가 뒤쪽에 있으면 그냥 모든행이 출력된다.)

: 소문자가 들어있는 모든 행

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

-첫번째 인수 : 원본데이터

-두번째 인수 : 찾고자하는 패턴

-세번째 인수 : 변환하고자하는 형태

-네번째 인수 : 검색 시작위치

-다섯번째 인수 : 패턴과 일치하는 횟수 (n번째 발생하는 문자열)

-여섯번째 인수 : 검색 옵션

- c : 대소문자 구분해서 검색

- i : 대소문자 구분하지 않고 검색

-m : 검색 조건을 여러줄로 줄 수 있음.

** tip. [[:문자클래스:]] : alpha, blank, cntrl, digit, graph, lower, print, space, upper, xdigit

: 숫자[0-9]까지 한글자를 찾아, 그걸 \1로 받고, 그 뒤에'-*'을 추가

: 숫자 [0-9]까지 1개이상의 숫자를 찾아, 그걸 \1 첫번째로 받고, 그 뒤에 -* 추가.

: .(DOT)를 모두 삭제하고 출력.

: 찾고싶은 글자(공백)이 1개 이상 오면, 공백없이 출력 ★

a{4} : 4번반복 -> aaaa

a{2} : 2번 이상 반복 -> aa, aaa, aaaa, aaaaa ...

a{3,5} : 3번반복부터~ 5번반복까지 -> aaa,aaaa,aaaaa

ab{2,3} : 뒤에있는 b가 2번반복부터~3번반복까지 -> abb, abbb

: 공백이 두칸 이상이면 공백없애는데, 위에껀 공백이 한칸이라 적용안됨.

: id를 사용자에게 입력받을 때(&), 처음과 중간에 공백포함해서 id를 입력하면, 모든 공백 없애도록.

: id를 공백을 모두 제거하고, 영어는 소문자로 변환해서 조회 ★★

: 20120324의 형태로 이루어진 데이터를 2012:03:24 형태로 변형

숫자4글자 연속 , 숫자 2글자연속, 숫자2글자연속 을 -> \1:\2:\3 형태로 받아씀.

[ tip] set verify on/off

명령어나 pl/sql에서 &를 이용한 치환 변수등을 사용할 때 치환되기 전 후의 자세한 값을 표시할 것인지의 여부를 결정한다. 기본값은 ON이다.

-set verify on 상태라면 &를 사용하여 입력을 받은 후 결과 출력시 입력 전과 입력 후를 구별하여 출력한다.

-set veriify off 는 입력후만 출력하게 바꿀 수 있다.

3)REGEXP_INSTR : 특정 패턴이 출현하는 첫 위치값 반환.

: aa bb cc dd ee에서 첫 시작이 공백이 아니고, 공백을 기준으로 1번글자('aa')부터 검사해서 3번째위치('cc')가 출현되는 자리를 찾음.

: abc ade abc adf에서 첫시작이 공백이 아니고, 공백을 기준으로 분류해, a나 b나 c가 들어가는 단어중 두번째 단어의 첫위치표시.

4)REGEXP_SUBSTR : 특정패턴에서 주어진 문자를 추출해 내는 함수

: 첫글자가 공백이 아니고, 공백을 기준으로, 그 후에 def가 나오는 부분 추출

:첫 글자가 공백이 아니고, 공백을 기준으로, 그 후에 콤마로 구분되는 문자열 출력

: regexp_substr 으로 /www.abc.net 까지 검색 한후, 거기에서 '/'를 LTRIM으로 삭제함.

-> ([[:alnum:]]+\.?) 이 3~4개 필드가 나오도록 함.

: regexp_substr 으로 @abc.net 까지 검색 한후, 거기에서 '@'를 LTRIM으로 삭제함.

-> ([[:alnum:]]+\.?) 이 3~4개 필드가 나오도록 함.

: 기호 ' : '으로 시작하지않고, 그 기호':'를 기준으로 3번째 문자열 출력.

: 기호 ' : '으로 시작하지 않고, 그 기호 ' : '를 기준으로 2번째 문자열 출력.

: 기호 '/'로 시작하지 않고, 그 기호 '/'를 기준으로 2번째 문자열 출력.

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

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

문법 : regexp_count(칼럼, '검색할문자', x번째이후부터, 옵션)

: 3번째 문자 이후부터 소문자 a가 나오는 개수 세는 예제. : 소문자i옵션으로 대소문자 구분 없이 a가 몇개 나오는지 센다.

: 어떤 방법으로 검색하든 동일한 결과가 나옴.

2) 11g REGEXP_SUBSTR 추가기능 ( Sub Expression 사용하기)

: 위함수 수행하면 결과가 '.abc.' 이런식으로 나오는데, 거기서 첫번째 출력하라고 했으므로 .(점)만 나옴.

: 2로 변경하면 .abc.에서 영어부분이 출력됨. (3을 입력하면 점만 출력됨)

http://ko.wikipedia.org/wiki/%EC%A0%95%EA%B7%9C_%ED%91%9C%ED%98%84%EC%8B%9D