#3 SQL 단일행 함수

단일행 함수:

        여러 건의 데이터를 한번에 하나씩 처리하는 함수

 

복수행 함수:

        여러 건의 데이터를 동시에 입력 받아 결과값 1건을 만들어 주는 함수 (그룹 함수)

 

1. 문자 함수

# INITCAP( 문자열 or 컬럼명 )

영어에서 입력 값의 첫 글자만 대문자로 반환하고 나머지는 전부 소문자로 출력하는 함수

- 중간에 공백이 있을 경우 공백 다음을 첫글자로 생각해서 대문자로 출력

SELECT INITCAP(name) initcap
FROM professor
WHERE deptno = 101;

--------------------

INITCAP
----------------------
Olivia Lauren
Audie Murphy

 

# LOWER(문자열/컬럼명)

입력 값을 전부 소문자로 변환

 

# UPPER(문자열/컬럼명)

입력 값을 전부 대문자로 변환

 

# LENGTH(문자열/컬럼)

입력된 문자열의 길이 값을 출력

** MIRROR -> length: 6

SELECT ename, LENGTH(ename)
FROM emp
WHERE LENGTH(ename) > LENGTH('&ename');

Enter value for ename: smith
-> 길이가 5보다 큰 것들만 출력

******* value 값 입력 받기

where length(ename) > Length('&ename');

-> Enter value for ename: smith

     => length(smith)= 5

ename의 길이가 5 보다 큰 것만 출력함

 

# LENGTHB(문자열/컬럼)

입력된 문자열의 길이의 바이트 값을 출력

- 바이트 단위로 계산

- LENGTH()와 영어일 경우 값은 동일함

  but, 한글일 경우 LENGTH()와 LENGTHB()는 다름

(한글은 일반적으로 1글자당 2바이트를 사용하기 때문에)

# CONCAT             ==         ||

두 문자열을 결합해서 출력

CONCAT('A','B')

|| 연산자와 동일

 

# SUBSTR

주어진 문자에서 특정 문자만 추출

SUBSTR('문자열' or 컬럼명, 시작 index , 부분 문자열의 길이)

- 시작위치

    1: 첫번째 문자

    -1: 뒤에서 첫번째 문자

 

- 부분 문자열의 길이

    시작위치를 포함한 문자열의 길이

SELECT name, SUBSTR(jumin, 3, 4) "Birthday", SUBSTR(jumin, 3, 4)-1 "Birthday - 1"
FROM student
WHERE deptno1 = 101;

 

SUBSTR(jumin, 3, 4)-1

문자열 -1 이지만 산술연산 처리 되어 ex) '1024' -1 => 1023

=> 묵시적 형변환

 

substr('abcde', 3, 2)

    => cd

substr('abcde', -3, 2)

    => cd

substr('abcde', -3, 4)

    => cde

# SUBSTRB

주어진 문자에서 특정 바이트만 추출

SUBSTRB(문자열, 시작위치, 추출할 바이트 수)

: 추출할 바이트 수를 지정

 

SUBSTRB('홍길동', 1, 2) -> 홍

# INSTR

주어진 문자에서 특정 문자의 위치 추출

INSTR(문자열/컬럼, '찾는 글자' [, 시작위치, 몇번째인지])

- 몇번째인지:

    기본값은 1

    찾는 글자가 여러개인 경우 그 중에서 몇번째 것의 위치를 추출할 것인지 지정

 

**적절한 위치를 찾지 못하면 조회 실패-> 0 을 반환

=========================================================

select 'A-B-C-D', instr('A-B-C-D','-',1,3)

from dual ;

- 'A-B-C-D' 문자열에서 A위치부터 탐색했을 때 '-' 문자가 3번째로 나오는 위치 => 6

 

select 'A-B-C-D', instr('A-B-C-D','-',3,1)

from dual ;

- 'A-B-C-D' 문자열에서 B위치부터 탐색했을 때 '-' 문자가 1번째로 나오는 위치 => 4

=========================================================

# INSTRB

주어진 문자에서 특정 문자의 위치 추출

// A-B-C-D 문자열에서 왼쪽부터 탐색했을 때 - 문자가 3번째로 나온 위치 값

SELECT 'A-B-C-D', INSTR('A-B-C-D', '-',1,3) "INSTR" FROM dual;

'A-B-C-      INSTR
------- ----------
A-B-C-D          6


// 문자열 오른쪽 끝에서부터 왼쪽방향으로 탐색했을 때, - 문자 위치 중 3번째 위치 값

SELECT 'A-B-C-D', INSTR('A-B-C-D', '-',-1,3) "INSTR" FROM dual;

'A-B-C-      INSTR
------- ----------
A-B-C-D          2

// 문자열 오른쪽 끝에서 6번째 위치한 곳부터 탐색해서 - 문자가 3번째로 나온 위치(INDEX)

SELECT 'A-B-C-D', INSTR('A-B-C-D', '-',-6,2) "INSTR" FROM dual;

'A-B-C-      INSTR
------- ----------
A-B-C-D          0	=> 발견되지 않음

 

# LPAD

주어진 문자열에서 왼쪽으로 특정 문자를 채움                              : Left Padding

 

LPAD('문자열' or 컬럼, 자리수, '채울문자')

- 왼쪽 빈 공간을 기호로 채움

- 주어진 칸보다 출력할 자리수가 적으면 남은 공간을 지정한 기호로 채움

SELECT id, LPAD(id, 10, '*')
FROM student
WHERE deptno1=201;

ID                   LPAD(ID,10,'*')
-------------------- --------------------
Moorew               *****Moorew
Culkin               ****Culkin
Snipes               ****Snipes

-> 출력할 자리수가 적으면 왼쪽에 남은 공간을 지정한 기호로 채움

 

LPAD(id, 9, '1234')

    => 채울 문자를 문자열로 주면 주어진 문자열에서 빈공간에 들어갈 만큼만 채움

123CLARK

12345KING

# RPAD

: 주어진 문자열에서 오른쪽으로 특정 문자를 채움

RPAD('문자열' or 컬럼명, 자리수, '채울문자')

 

 

# LTRIM

: 주어진 문자열에서 왼쪽의 특정 문자를 삭제

LTRIM('문자열' or 컬럼명, '제거할 문자')

** 대소문자를 구분, 왼쪽끝에 위치한 지정한 문자(문자열)를 제거

*** 왼쪽 끝에 있는 문자와  제거할 문자가 같은 경우에만 지워짐

 

# RTRIM

주어진 문자열에서 오른쪽 끝에 위치한 특정 문자를 삭제

RTRIM('문자열'or 컬럼명, '제거할 문자')

 

 

# REPLACE

주어진 문자열에서 A를 B로 치환

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

SELECT ename, REPLACE(ename, SUBSTR(ename, 1, 2), '**') replace
FROM emp
WHERE deptno=10;

// ename 에서 앞에 두글자를 ** 로 교체

2. 숫자 관련 함수

- Round(숫자, 출력할 소수점의 자리수)

- Trunc(숫자, 버릴 자리 수)

    자리 수가 음수이면 정수부에서 반올림 된다.

- MOD(121, 10) == 121%10==1

- CEIL(숫자) : 천장함수, 가장 가까운 큰 정수를 구함

- FLOOR(숫자) : 바닥함수, 가장 가까운 작은 정수를 구함

- POWER(A, B) : A의 B제곱

 

3. 날짜 관련 함수

- SYSDATE

시스템의 현재 시간

 

- MONTHS_BETWEEN( A, B )

두 날짜 사이의 개월 수

정수 > 두 날짜가 같은 날짜이거나 둘다 월말인 경우

소수 > 한달을 31일로 계산 (윤달 계산 안함)

 

- ADD_MONTHS(날짜, 더해질 개월 수)

날짜에 지정한 개월 수만큼 더함

 

- NEXT_DAY(날짜, 요일)

날짜를 기준으로 돌아오는 요일의 날짜를 출력

 

- LAST_DAY(날짜)

주어진 날짜가 속한 달의 마지막 날짜를 출력

 

- ROUND(): 주어진 날짜를 반올림

- TRUNC(): 주어진 날짜를 버림

 

4. 형변환 함수

1) TO_CHAR()

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

[연도]

'YYYY' , 'RRRR' : 연도를 4자리로 표현

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

'YEAR' : 연도의 영문 이름 전체를 표시

 

[월]

'MM' 숫자 2자리로 표현

'MON', 'MONTH' : 월을 뜻하는 이름을 표시

[일]

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

'DAY': 요일의 명칭

'DDTH': 몇번째 날인지 표시

[시간]

'HH24': 하루를 24시간으로 표시

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

분: 'MI'

초: 'SS'

 

- TO_CHAR(숫자, '원하는 모양')

'9999': 9의 개수만큼 자리수를 채움

'0999': 4자리 중 빈자리를 0으로 채움

'$999': $를 붙여서 표시

'999.99': . 소수점 이하를 표시

'99,999': , 천단위 구분기호를 표시

 

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

'5' -> 5

'A' -> ERROR

 

3) TO_DATE('문자')

문자에 맞는 날짜 타입으로 변환

 

5. 일반 함수

1) NVL(컬럼, 치환할 값)

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

 

2) NVL2(col1, col2, col3)

col1이 NULL이 아니면 col2, NULL이면 col3로 치환

 

3) DECODE()

= IF 문과 같은 역할

오라클에서만 사용되는 함수

(1) DECODE( A, B, '1', NULL)   == DECODE(A, B, '1')

A가 B일 경우 '1'을 출력

 

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

A가 B일 경우 '1'을 출력하고, 아니면 '2'를 출력

 

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

A가 B일 경우 '1'을 출력하고, A == C일 경우 '2'를 출력하고 둘다 아닌 경우 '3'을 출력한다

 

(4) DECODE( A, B, DECODE( C, D, '1', null ))

A가 B일 경우 중에서 C가 D를 만족하면 '1'을 출력하고 C!=D이면 NULL을 출력

 

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

A가 B일 경우 중에서 C가 D를 만족하면 '1'을 출력하고 C!=D 이면 '2'를 출력

 

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

A가 B일 경우 중에서 C가 D를 만족하면 '1'을 출력하고 C!=D이면 '2'를 출력, A!=B이면 '3'을 출력

 

4) CASE 문

CASE 표현식 내부는 콤마가 사용되지 않는다.

 

(1) DECODE와 동일하게 == 조건으로 사용되는 경우

-> 이 경우 DECODE가 더 많이 사용됨

CASE 조건 WHEN 결과1 THEN 출력1

               [WHEN 결과2 THEN 출력2,,,]

               ELSE 출력3

END "새컬럼명"

 

(2) 비교 조건이 '='이 아닌 경우

WHEN 뒤에 비교 구문을 작성

 

CASE WHEN 비교조건1 THEN 결과1 [WHEN ,, THEN ..]

END "새 컬럼명"

 

*WHEN은 순차적으로 판단, 맞는 조건이 발견되면 이후는 판단하지 않음

 

6. 정규식 함수

^ : 해당 문자로 시작하는 line 출력

$ : 해당 문자로 끝나는 line 출력

. : 한 글자

* : 0개 이상의 모든 글자

[ ] : 괄호 안에 있는 문자 중 한문자

[^ ] : 괄호 안에 있는 문자를 제외한 문자중 하나

{n} : 중괄호 앞에 있는 패턴이 n번 매칭됨 (중괄호 앞에 있는 패턴이 연속적으로 n번이상 나오는 행)

{n, m} : 앞의 패턴을 n ~ m 번 반복

( ) : 그룹

      그룹 번호 -> \1, \2,,,

+ : 바로 직전의 문자를 한번 이상 반복

? : 바로 직전의 패턴이 있거나 없을수도 있다.

 

- 특수문자 검색

) -> \) 

* -> \*

. -> \.

? -> \?

 

! -> '!'

- 키워드

[[:space:]] : 탭, 공백문자 하나를 조회

[[:upper:]] : == [A-Z]

[[:alpha:]] : 소문자, 대문자

[[:alnum:]] : 알파벳(대소문자) 이나 숫자

[[:digit:]] : 숫자

# REGEXP_LIKE

주어진 문자열에서 특정 패턴과 매칭되는 결과를 검색해내는 함수

where regexp_like(text, '[a-z]')

-> 소문자를 하나라도 포함하는 행을 검색

 

where regexp_like(text, '[a-z] ')

-> 소문자 뒤에 공백이 있는 행

abc_123

abc_ _123  ->  c_ 부분이 표현식에 매칭 된 것

 

'[a-z] [0-9]'

-> 소문자 한글자, 공백 한칸, 숫자 한글자가 오는 행

 

'[[:space:]]'

-> 공백이 있는 모든 행을 출력

 

'[A-Z]{2}'

-> 대문자가 연속적으로 2글자 이상 있는 행

 

'[A-Z][0-9]{2}' 

-> 영어 대문자와 숫자가 연속적으로 오되 대문자가 연속적으로 3글자 있는 행

 

'[[:upper:]]'

-> 대문자가 들어가는 모든 행을 출력

 

'^[A-Za-z]'

-> 첫 시작이 대문자나 소문자인 행

 

'^[a-z] | ^[0-9]'

-> 소문자나 숫자로 시작하는 모든 행

 

'^[^a-z]'

-> 소문자로 시작하지 않는 행

 

'^M(a|o)'

-> 첫번째 글자가 M으로 시작하고 두 번째 글자에 a나 o가 오는 문자열

 

*** 소문자가 있는 행을 제거하기

'[^a-z]'

-> 소문자가 아닌 문자가 하나라도 있으면 해당됨

- 소문자가 한글자도 포함되지 않은 행을 출력

where not regexp_like(text, '[a-z]')

-> T/F 반환, 소문자가 한글자라도 존재하면 false

 

'^[0-9]{2}\)[0-9]{4}'

-> 숫자 2번, ) 뒤에 숫자 4자리 연속

 

**

'...r.' -> r 앞에 3글자 이상, 뒤에 한자리 이상이 있는 문자열

'^...r' -> 앞에서부터 4번째 자리에 r이 있는 행

 

'^172\.16\.168\.'

-> 172.16.168.xx 의 ip를 출력하고 싶은 경우

 

*************

where not regexp_like(text, '[a-zA-Z]')

-> 알파벳을 포함하지 않은 행만 출력

 

where not regexp_like(text, '[0-9]')

-> 숫자를 포함하지 않은 행만 출력

 

# REGEXP_REPLACE

주어진 문자열에서 특정 패턴을 찾아 치환

문법 : regexp_replace( source_char, pattern,

[교체할 문자열,

[문자열 내 검색을 시작할 위치,

[몇번째에 매칭된 것을 교체할 것인지(0-> 매칭되는 모든 문자열을 교체),,,]]])

 

regexp_replace(text, '([0-9])', '\1-*')

-> \1: 첫번째 그룹 - 에 해당하는 숫자를 찾아서 숫자 뒤에 -* 를 추가

 

regexp_replace(ip, '\.', '')

-> . 이 매칭되는 모든 부분을 삭제하고 출력

 

regexp_replace(ip, '\.', '/',1,1)

-> 첫번째 . 만 / 로 변경

 

**********************

- 공백을 제거하는 방법

regexp_replace('aaa bbb', '( ){1}', '')

-> 공백문자가 한번이상 나타나면 제거

 

20141023 -> 2014-10-23

regexp_replace('20141023', '([[:digit:]]{4})([[:digit:]]{2})([[:digit:]]{2})', '\1-\2-\3')

-> 첫번째 그룹 - 두번째 그룹 - 세번째 그룹

 

# REGEXP_SUBSTR

주어진 문자열에서 특정 패턴을 찾아 반환 -> 매칭된 부분을 되돌려 줌

 

regexp_substr('ABC* *DEF $GHI %JKL', '[^ ]+[DEF]')

-> *DEF : 공백이 아닌 문자열들& F, 첫글자가 공백이 아닌 문자가 하나 이상 나온 후, 그 후에 D, E, F 중 한 문자가 오는 것

regexp_substr('ABC* DEF $GHI', '[^ ]+[DEF]')

-> DEF : 공백이 아닌 문자가 하나 이상 나온 후(DE) D,E,F 중 한 문자 오는 것

 

regexp_substr('http://num1.naver.com', '/([[:alnum:]]+\.?){3,4}?')

-> /로 시작하는 문자열을 반환

    [[:alnum:]]+\.? : 문자 뒤에 . 이 있을수도 있고 없을수도 있다.

 

regexp_substr(email, '@([[:alnum:]]+\.?){3,4}?')

-> @ 로 시작하여 alnum이 한번이상 반복되고 . 이 있을수도 있고 없을수도 있는 그룹이 3번에서 4번 반복되는 패턴

 

regexp_substr('sys/oracle@racdb : 1521 : racdb', '[^:]+',1, 3)

-> : 기호를 기준으로 3번째의 문자열을 출력, racdb

 

regexp_substr('sys/oracle@racdb : 1521 : racdb', '[^/:]+',1,2)

-> /, : 기호를 기준으로 2번째의 문자열을 출력

(/, : 이 아닌 문자가 한번이상 반복되는 문자열)

# REGEXP_COUNT

주어진 문자열에서 특정패턴의 횟수를 반환

regexp_count(text, 'A')

-> 패턴에 매칭되는 횟수 반환, text 컬럼에서 A의 개수

 

regexp_count(text, 'C', 3)

-> 검색 위치를 3으로 지정, 3번째 문자 이후부터 'C'가 나오는 개수를 세는 예제

 

regexp_count(text, 'c',1, 'i')

-> 'i' 옵션을 주어 대소문자 구분 없이 c, C 가 몇개 나오는지 출력

 

'.' -> 모든 것

'\.' -> dot. 점을 검색 

 

# REGEXP_INSTR

주어진 문자열에서 특정패턴의 시작 위치를 반환