[SQL&PL/SQL] 3.Single-Row Functions

1. Character Functions

LOWER('String') --> string
; 소문자로 변환
UPPER('String') --> STRING
; 대문자로 변환
INITCAP('string') --> String
; 첫글자만 대문자이고 나머지글자는 소문자로 변환
CONCAT('Good','String') --> GoodString
; ||와 같은 역할을 한다.
SUBSTR('String',1,3) --> Str
; 1번째부터 3개의 문자를 리턴한다.
LENGTH('String') --> 6
; 문자열의 길이를 리턴한다.
INSTR('String','r') --> 3
; 문자열에 'r'이 몇번째 위치하고 있나를 리턴한다.
LPAD('String',10,'*') --> ****String
; 10자리수중에 모자란 자리수를 '*'로 왼쪽에 채운다.(문자,숫자 가능!!!)
RPAD('String',10,'*') --> String****
; 10자리수중에 모자란 자리수를 '*'로 오른쪽에 채운다.(문자,숫자 가능!!!)
LTRIM(' String') --> 'String'
; 문자열의 왼쪽 공백을 버린다.
RTRIM('String ') --> 'String'
; 문자령의 오른쪽 공백을 버린다.

2. Number Functions

ROUND(45.926, 2) --> 45.93
; 소수점 두자리수까지 보여주면서 반올림한다.
TRUNC(45.926, 2) --> 45.92
; 소수점 두자리까지만 보여주고 나머지는 버린다.
MOD(1600,300) --> 100
; 1600을 300으로 나누고 나머지를 리턴한다.

* ROUND예제
SELECT ROUND(45.923,2), ROUND(45.923,0), ROUND(45.923,-1) FROM DUAL
==> 45.92 46 50

* TRUNC예제
SELECT TRUNC(45.923,2), TRUNC(45.923), TRUNC(45.923,-1) FROM DUAL
==> 45.92 45 40

* SYSTEM 날짜를 갖고 오는 방법.
SELECT sysdate FROM dual

3. Date 계산

date + number : date에 number만큼 후의 날자를 보여준다.
date - number : date에 number만큼 전의 날자를 보여준다.
date1 - date2 : date1에서 date2 까지의 총 일수를 보여준다.

4. Date Functions

MONTHS_BETWEEN('01-SEP-95','11-JAN-94') --> 19.6774194
; 두날짜 사이의 일수를 보여준다.
ADD_MONTHS('11-JAN-94', 6) --> 11-JUL-94
; 날짜에 6개월을 더한 날자를 보여준다.
NEXT_DAY('01-SEP-95','FRIDAY') --> '08-SEP-95'
; 해당일 다음에 오는 FRIDAY의 일자를 보여준다.
('SUNDAY'는 1, 'MONDAY'는 2...이런식으로 숫자를 써줘도 된다.)
LAST_DAY('01-SEP-95') --> '30-SEP-95'
; 해당월의 마지막날자를 보여준다.
ROUND('25-JUL-95','MONTH') --> 01-AUG-95
ROUND('25-JUL-95','YEAR') --> 01-JAN-96
TRUNC('25-JUL-95','MONTH') --> 01-JUL-95
TRUNC('25-JUL-95','YEAR') --> 01-JAN-95

5. Conversion Functions

TO_CHAR(date,'format') : date를 format에 맞게 문자열로 변환한다.
- Date Format Elements

SYYYY --> Signed 연도 : 1000 B.C = -1000

YYYY --> 1999 (년출력)

YYY --> 세 자리로 표현한 연도 수 : 1998년 = 998

YY --> 1998년 = 98

Y --> 1998년 = 8
YEAR --> 영문으로 쓰여진 연도 : 1998 = nineteen ninety-nine (년출력)
MM --> 12 (월출력)

MON --> 세 자리 영문 달 이름 : 8월 =  AUG

MONTH --> DECEMBER (월출력)
DY --> MON (월출력) : 일요일 = SUN
D --> 요일을 숫자로 리턴한다.(일요일은 1, 월요일은 2...)
DAY --> MONDAY (요일출력)
CC --> 20 (몇 세기인지를 보여준다.)
DD --> 07 (일출력)
WW --> 그 해의 몇 번째 주인가를 리턴한다. 한 해의 지나간 주일 수 : 46
W --> 그 달의 몇 번째 주인가를 리턴한다. 한 달 기준의 주일 수 : 3
DDD --> 그 해의 총 몇 일째인가를 리턴한다.

RM --> ROMAN 달 수 :12월 =XXII

RR --> 현재 날짜의 연도를 두 자리로 표현

Q --> 4분기 : 3

HH --> 12시간으로 표현되는 시간 : 오후 3시 =3

HH12 --> HH12와 동일

HH24 --> 24시간으로 표현되는 시간 : 오후 3시 =15

MI --> 분

SS --> 초

SSSSS --> 자정이후의 초로 계산된 시간, 항상 0 ~ 86399의 범위를 가진다 : 43000

A.M. or AM --> 오전 표시

P.M. or PM --> 오후 표시

B.C. or BC --> 기원전의 표시

A.D. or AD --> 기원후의 표시

 

* Element들을 소문자로 쓰면 소문자로 나오고 대문자로 쓰면 대문자로 출력된다.

 

 

fm : Day 또는 Month 앞에 붙이는 Prefix이다.

      fm을 사용하지 않으면 모든 달의 값은 같은 길이를 가진다.

 

SQL> select date2 "Original Form"  TO_CHAR(date2,'fmMonth DD, "in, year" YYYY')  FORMATTED

         from Date_Example1;

 

Original           FORMATTED

.............................................................................................

03-MAR-98     March 3, in, year 1998

01-MAY-98     MAY 1, in , year 1998

 

 

TH : 날짜 데이터의 뒷자리에 올 수 있으며 DDTH 또는 DDth 형식으로 쓰여 24th 또는 24TH와 같이

      구성한다

 

SQL> select TO_CHAR( date2, 'Month DDTH, YYYY')

         from Date_Example1;

 

TO_CHAR(DATE2,'MONTHDDTH,YYYY')

...........................................................................

March            03RD, 1998

May               01ST, 1998

 

 

SP : 날짜 데이터의 뒷자리에 오며 날짜가 문자로서 표현되도록 한다.

       날짜가 3일인 데이터의 DDSP는 THREE , DdSP는 Three , ddsp는 three로 변환

 

SQL> select TO_CHAR(date2,'fmMonth "의" Ddsp') FORMATTED

          from Date_Example1;

 

FORMATTED

....................................................................

,,,

Third of March, 1998, at 12:0

First of May, 1998, at 11:15

TO_CHAR(number,'format') : number를 format에 맞게 문자열로 변환한다.
- Number Format Elements
9 : 999,999 --> 1,234
0 : 099999 --> 001234
L : L99,999 --> $1,234 (NLS_CURRENCY에 설정되어있는 값이 L로 보여진다. $,\,원...)

$ : $99,999 --> $1,234 ($ 사인 선행 값)

B : B99,999 -- > 0을 공백대신 삽입한다

MI : 9999MI --> 음수에 의 끝부분에 "-" 사인을 추가한다

S : S9999 --> 양수에 "+" 사인을 음수에 "-" 사인을 삽입한다

PR : 9999PR --> 음수의 끝부분에 algle bracket을 삽입한다.

D : 99D99 --> 소숫점을 입력한다.

G : 9G999 --> 숫자의 단위를 나누는 그룹 분리 문자를 입력한다.

V : 999V99 --> 10V15는 10의 15승을 의미한다

EEEE : 9.999EEEE --> 숫자를 과학용 표기 형식으로 출력한다.

RN : RN --> 대분자 Roman 숫자 또는 소문자 Roman 숫자를 반환한다. 그 값은 0~3999이다.

 

TO_NUMBER(char) : 숫자형태의 문자열을 숫자로 변한한다.
TO_DATE(char,'format') : 날자형태의 문자열을 format에 맞게 날자형식으로 변환한다.

6. NVL Funcion

; 값이 null일 때 설정값을 보여준다.
NVL(number_column, 0) : null일 때 0을 보여준다.
NVL(date_column, '01-JAN-95') : null일 때 '01-JAN-95'를 보여준다.
NVL(character_column, 'Empty') : null일 때 'Empty'를 보여준다.

* column Type과 표현식의 type이 반드시 일치해야 한다.

7. DECODE Function

; CASE or IF-THEN-ELSE 형식과 비슷하다.

SELECT DECODE(job,'ANALYST', sal * 1.1, 'CLERK', sal * 1.15, 'MANAGER', sal * 1.20, sal) FROM emp

--->

IF job = 'ANALYST' THEN sal = sal * 1.1 IF job = 'CLERK' THEN sal = sal * 1.15 IF job = 'MANAGER' THEN sal = sal * 1.20 ELSE sal = sal

숫자 처리 함수 ................................. ABS(n) : 절대값을 계산하는 함수 CEIL(n) : 주어진 값보다는 크지만 가장 근접하는 최소값을 구함 COS(n), EXP(n), SIN(n), TAN(n) FLOOR(n) : 주어진 값보다 작거나 같은 최대 정수값을 구함 LN(n) : 주어진 값의 자연 로그값을 반환 LOG(m,n) : 베이스 m의 n로그 값을 반환한다 (m>2, n>0) POWER(m,n) : m의 n승의 값을 계산한다. SIGN(n) : n<0이면 -1, n=0이면 0을, n>0이면 1을 리턴 SORT(n) : n값의 스퀘어 루트를 계산한다

 

문자열 처리함수(문자열을 반환)
...............................
CHAR(n) : 보통 n은 숫자 즉 ASCII n값의 문자를 반환
NLS_INITCAP(char, ['nlsparams'])
NLS_LOWER(char, ['nlsparams'])
NLS_UPPER(char, ['nlsparams'])
REPLACE(char,search_string,[replace string])
-->주어진 문자열 char중에 search_string을 찾아
replace_string으로 대치시키킴
SOUNDEX(char)
--> char의 발음을 분석하여 발음에 가장 가까운 문자를
찾아 반환한다. 정확한 값을 모르는 문자 데이터의
검색에 유용함
select emp_name from employee
where SOUNDEX(emp_name) = SOUNDEX('박기원');

EMP_NAME
..................................
박직원

SUBSTRB(char, m, [n]) : SUBSTR과 동일(바이트 단위)
TRANSLATE(char, from, to)
--> from에 나타나 있는 문자를 char에서 찾으면 to에 지정
된 문자로 바꾸어 준다

문자열 처리함수(숫자를 반환)
...............................
ASCII(char) : 문자를 입력 받아 문자가 해당하는 ASCII
값을 반환한다.
INSTR(char1,char2,[n[m]])
-->char1문자열의 n번째 문자로부터 m번째 나오는 char2
문자열을 찾는다.
INSTRB(char1,char2,[n,[m]])
-->n값과 반환값이 바이트로 표현(INSTR과 동일)
LENGTH(char) : char문자열의 길이 반환
LENGTHB(char) : char문자열의 바이트 수를 반환

기타 함수
...............................
UID, USER : 현재 유저와 유일한 ID번호를 반환

SELECT USER, UID FROM DUAL;
USER UID
.........................
SCOTT 8

trim('O' from 'Oracle') 문자열 중에서 'O'라는 문자를 잘라낸 나머지 문자를 결과로서나타냄