>> Oracle Functions
http://docs.oracle.com/cd/E11882_01/server.112/e26088/functions.htm#i1482196
> Numeric Functions
1) CEIL : 올림
SELECT CEIL(3.141592), CEIL(3.98), CEIL(3) FROM DUAL;
CEIL(3.141592) CEIL(3.98) CEIL(3)
-------------- ---------- -------
4 4 3
2) FLOOR : 내림
SELECT FLOOR(15.7), FLOOR(15.1), FLOOR(16) FROM DUAL;
FLOOR(15.7) FLOOR(15.1) FLOOR(16)
----------- ----------- ---------
15 15 16
3) MOD : 나머지
SELECT MOD(11,4) "Modulus" FROM DUAL;
Modulus
-------
3
4) NANVL : ?
5) ROUND : 반올림
SELECT ROUND(15.193,1), ROUND(15.193,2), ROUND(15.193), ROUND(15.193,-1) FROM DUAL;
ROUND(15.193,1) ROUND(15.193,2) ROUND(15.193) ROUND(15.193,-1)
--------------- --------------- ------------- ----------------
15.2 15.19 15 20
6) TRUNC : 버림
SELECT TRUNC(15.79,1),TRUNC(15.79,-1) FROM DUAL;
TRUNC(15.79,1) TRUNC(15.79,-1)
-------------- ---------------
15.7 10
> Character Functions Returning Character Values
1) CHR
SELECT CHR(67)||CHR(65)||CHR(84) "Dog" FROM DUAL;
Dog --- CAT
2) CONCAT : concatenation 연결
SELECT CONCAT(CONCAT(last_name, '''s job category is '), job_id) "Job" FROM employees WHERE employee_id = 200; SELECT last_name||'''s job category is '|| job_id AS "Job" FROM employees WHERE employee_id = 200;
Job -------------------------------- Whalen's job category is AD_ASST
3) INITCAP : 첫글자만 대문자로 나머지는 소문자
SELECT INITCAP('the soap') "Capitals"
FROM DUAL;
Capitals -------- The Soap
SELECT email, INITCAP(email) FROM employees;
EMAIL INITCAP(EMAIL) -------- -------------- AHUNOLD Ahunold BERNST Bernst CDAVIES Cdavies DLORENTZ Dlorentz EABEL Eabel EZLOTKEY Ezlotkey JTAYLOR Jtaylor JWHALEN Jwhalen KGRANT Kgrant KMOURGOS Kmourgos LDEHAAN Ldehaan MHARTSTE Mhartste NKOCHHAR Nkochhar PFAY Pfay PVARGAS Pvargas RMATOS Rmatos SHIGGINS Shiggins SKING Sking TRAJS Trajs WGIETZ Wgietz
4) LOWER : 모두 소문자로
SELECT LOWER('MR. SCOTT MCMILLAN') "Lowercase"
FROM DUAL;
5) UPPER : 모두 대문자로
SELECT last_name, UPPER(last_name) "Uppercase" FROM employees;
6) LPAD : left-padded to length (주어긴 길이에 남는공간을 정한 패턴으로 왼쪽부터 채워)
SELECT LPAD('Page 1',15,'*.') "LPAD example"
FROM DUAL;
LPAD example --------------- *.*.*.*.*Page 1
SELECT LPAD('Page 1',15,'0') "LPAD example"
FROM DUAL;
LPAD example --------------- 000000000Page 1
7) RPAD
SELECT last_name, salary, salary/1000/1, RPAD(' ', salary/1000/1, '*') "Salary"
FROM employees
WHERE department_id = 80
ORDER BY last_name, "Salary";
LAST_NAME SALARY SALARY/1000/1 Salary --------- ------ ------------- ----------- Abel 11000 11 ********** Taylor 8600 8.6 ******* Zlotkey 10500 10.5 *********
8) TRIM
SELECT employee_id, hire_date,
TO_CHAR(TRIM(LEADING 0 FROM hire_date))
FROM employees
WHERE department_id = 60
ORDER BY employee_id;
EMPLOYEE_ID HIRE_DATE TO_CHAR(TRIM(LEADING0FROMHIRE_DATE))
----------- --------------------- ------------------------------------
103 1990-01-03 00:00:00.0 90/01/03
104 1991-05-21 00:00:00.0 91/05/21
107 1999-02-07 00:00:00.0 99/02/07
select trim('H' from 'Hello WorldHHHHHH')
from DUAL;
TRIM('H'FROM'HELLOWORLDHHHHHH')
-------------------------------
ello World
9) LTRIM
SELECT LTRIM('<=====>BROWNING<=====>', '<>=') "LTRIM Example"
FROM DUAL;
LTRIM Example --------------- BROWNING<=====>
10) RTRIM
SELECT RTRIM('<=====>BROWNING<=====>', '>=<') "RTRIM Example"
FROM DUAL;
RTRIM Example --------------- <=====>BROWNING
TRIM은 한글자만 가능하나, LTRIM/RTRIM은 패턴이 가능해.
11) REPLACE
SELECT REPLACE('JACK and JUE','J','BL') "Changes"
FROM DUAL;
Changes -------------- BLACK and BLUE
12) SUBSTR
SELECT SUBSTR('ABCDEFG',3,4) "Substring" --3번째부터 4개만 남긴다
FROM DUAL;
Substring --------- CDEF
SELECT SUBSTR('ABCDEFGHI',-5,4) "Substring" --오른쪽 5번째부터 4개만 남긴다
FROM DUAL;
Substring --------- EFGH
> Character Functions Returning Number Values
1) ASCII
--L로 시작하는 사람 출력
SELECT last_name FROM employees WHERE ASCII(SUBSTR(last_name, 1, 1)) = 76 --76:L ORDER BY last_name;
LAST_NAME --------- Lorentz
> Datetime Functions
-. SYSDATE : 년월일시분초. 내부적으로 DATE type(7byte)사용
-. TO_CHAR (datetime) : 날짜를 문자열로
-. TO_DATE : 문자열을 날짜로
cf)
DATE type : 7byte - 년(2) 월(1) 일(1) 시(1) 분(1) 초(1byte)
TIMESTAMP type: 9byte - 년(2) 월(1) 일(1) 시(1) 분(1) 초(3byte)
SELECT TO_CHAR
(SYSDATE, 'YYYY-MM-DD HH24:MI:SS') "NOW", sysdate
FROM DUAL;
NOW SYSDATE ------------------- --------------------- 2012-06-10 16:47:50 2012-06-10 16:47:50.0
SELECT SYSDATE+10 FROM DUAL;
--날짜가 10일 더해져..
--백일 계산
SELECT TO_DATE('2013-01-01', 'YYYY-MM-DD') + 100
FROM DUAL;
--태어난지 몇일 됐지?
SELECT SYSDATE-TO_DATE('2004-05-21', 'YYYY-MM-DD')
FROM DUAL;
90년대 입사자구하기
SELECT last_name, TO_CHAR(hire_date, 'YYYY/MM/DD') AS hiredate
FROM employees
WHERE TO_DATE('1990-01-01', 'YYYY-MM-DD') <= hire_date
AND hire_date < TO_DATE('2000-01-01', 'YYYY-MM-DD');
LAST_NAME HIREDATE --------- ---------- De_Haan 1993/01/13 Hunold 1990/01/03 Ernst 1991/05/21 Lorentz 1999/02/07 Mourgos 1999/11/16 Rajs 1995/10/17 Davies 1997/01/29 Matos 1998/03/15 Vargas 1998/07/09 Abel 1996/05/11 Taylor 1998/03/24 Grant 1999/05/24 Hartstein 1996/02/17 Fay 1997/08/17 Higgins 1994/06/07 Gietz 1994/06/07
1990년 입사자를 구하기
SELECT last_name, TO_CHAR(hire_date, 'YYYY/MM/DD') AS hiredate FROM employees WHERE TO_CHAR(hire_date, 'YYYY') = '1990';
아주 좋지않은 쿼리야..
데이터가 많을 경우 모든 데이터를 변환한 다음에 비교해야잖아.
내부 컬럼에 함수값을 적용하는 것은 피해야 해!!
db엔 Abel이라고 있을 경우
select * from employees where last_name = 'ABEL'; --조회가 안되지.
select * from employees where UPPER(last_name) = 'ABEL'; --안좋아
select * from employees where last_name = initcap('ABEL'); --좋아
그렇기 때문에 문자열 입력할때도 대소문자등의 포멧을 맞추는게 속도에도 좋아.
들어온 데이터대로 반드시 넣어야 한다면 차라리 컬럼 하나더 만들어 넣는게 좋아.
SYSTIMESTAMP : TIMESTAMP type사용
SELECT SYSTIMESTAMP FROM DUAL;
SYSTIMESTAMP --------------------------------- 12/08/10 17:22:22.363000 +09:00
cf) RR date format
표기한 년도가 후반기(50~99년)고 현재년도가 전반기면 : 저장한 데이터는 이 전 세기
뭐 이런식의 정의가 있어.
아무튼 년도는 YYYY타입으로해.