1. 문자 함수
- 대소문자 변환
upper() 대문자
lower() 소문자
initcap() 첫글자 대문자, 나머지 소문자
select 'Apple', upper('Apple) from dual;
ex. 'scott' 사원의 사번, 이름 출력
select eno, ename from employee
where lower(ename)='scott';
- length()
-영문, 수, 특수문자(1 byte) 또는 한글의 길이 구함
select length('Apple'), length('사과')
from dual;
=> 5 2 출력
- lengthB()
-한글 2byte, 인코딩 방식에 따라 달라짐
-UTF-8: 한글 1글자 3byte
select lengthB('Apple'), lengthB('사과') from daul;
=> 5 6 출력
- concat
- ||
-두 문자열을 하나의 문자열로 연결
-반드시 2개의 문자열만 연결 가능
-매개변수=인수=인자=argument
select 'Apple', '사과',
concat('Appple', '사과') from dual;
select 'Apple', '사과',
concat('Appple', '사과')
- substr(대상, 시작 index(위치), 추출할 개수)
-시작위치가 양수이면 앞에서, 음수이면 뒤에서
-index는 0이 아닌 1부터 시작
-index에서 공백도 체크
select substr('apple mania',7,5), -- mania
substr('apple mania',-11,5) -- apple
from dual;
- substrB(기존문자열, 시작 index, 추출 할 바이트 수)
select substr('사과매니아', 1,2) --사과
substrB('사과매니아', 1,3), --'사' , 1부터 시작해서 3바이트를 추출
substrB('사과매니아', 4,3), --'과' , 4부터 시작해서 3바이트를 추출
substrb('사과매니아',1,6) --'사과' , 1부터 시작해서 6바이트를 추출
from dual;
- LPAD (left padding) (대상, 자릿수, 빈 자리를 채울 기호)
- RPAD (right padding)
-LPAD : 칼럼/대상 문자열을 명시된 자릿수에서 오른쪽에 나타내고,
남은 왼쪽 자리를 특정 기호로 채움
- LPAD
: 10자리를 마련 후 salary는 오른쪽, 남은 왼쪽 자리를 '*'로 채움
select salary,LPAD(salary,10,'*')
from employee;- RPAD
: 10자리를 마련 후 salary는 왼쪽, 남은 오른쪽 자리를 '*'로 채움
select salary,RPAD(salary,10,'*')
from employee;
- LTRIM / RTRIM : 공백 제거
* LTRIM (' 문자열') : 문자열의 '왼쪽' 공백 제거
* RTRIM ('문자열 ') : 문자열의 '오른쪽' 공백 제거
* TRIM (' 문자열 ') : 문자열의 '양쪽' 공백 제거
- TRIM ( '특정 문자' from '대상 문자열' )
-대상 문자열에서 특정 문자가 첫 번째 글자이거나 마지막 글자이면 잘라내고 남은 문자열만 반환
select 'Oracle mania',
TRIM('O' FROM 'Oracle mania')
from dual; => racle mania 출력2. 숫자 함수
- round (대상, 자릿수) : 반올림하여 자릿수까지 표시
- trunc (대상, 자릿수) : 내림하여 자릿수까지 표시
- mod (수1,수2) : 수1을 수2로 나눈 나머지
-자릿수를 생략하면 0으로 인식
-2(백의자리) -1(십의자리) 0(일의자리) . 1 2 3
1) round
select 98.7654,
round(98.7654), -- 99
round(98.7654,0), -- 99 출력 / 일의 자리까지 표시. 소수 첫째 자리에서 반올림
round(98.7654,2), -- 98.77 출력 / 소수 둘째 자리까지 표시. 소수 셋째 자리에서 반올림
round(98.7654,-1) -- 100 출력 / 십의 자리까지 표시. 일의 자리에서 반올림
from dual;
2) trunc
select 98.7654,
trunc(98.7654), -- 98
trunc(98.7654,0), -- 98
trunc(98.7654,2), -- 98.76
trunc(98.7654,-1) -- 90
from dual;
3) mod
select MOD(10,3) -- 1
from dual;
3. 날짜 함수
- sysdate : 현재 날짜와 시간
select sysdate from dual;- date + 수 = 날짜에서 수만큼 '이후 날짜'
- date - 수 = 날짜에서 수만큼 '이전 날짜'
- date - date = 일수
- date + 수/24 = 날짜 + 시간
select sysdate-1 as 어제,
sysdate 오늘,
sysdate+1 as "내 일"
from dual;- ROUND , TRUNC 활용
1) MONTH, MON, MM, RM
select hiredate,
trunc(hiredate,'Month') --'월'과 '일'을 01로 초기화
from employee;
2)
select sysdate,
trunc(sysdate), --시간 잘라냄
trunc(sysdate, 'dd'), --시간 잘라냄(윗줄과 동일한 결과)
trunc(sysdate, 'hh24'), --분,초 잘라냄
trunc(sysdate, 'mi'), --초 잘라냄
trunc(sysdate, 'Year'), --월(01)과 일(01)을 초기화
trunc(sysdate, 'Month'), --일(01)을 초기화
trunc(sysdate, 'Day') --요일(해당 날짜에서 지난 일요일로) 초기화,
며칠인지 알 수 있음
from dual;
- monthS_between (날짜1, 날짜2)
-날짜1과 날짜2 사이의 개월 수 구하기
-(날짜1-날짜2)
select ename, sysdate, hiredate,
months_between(sysdate, hiredate), TRUNC(months_between(sysdate, hiredate)),
months_between(hiredate, sysdate), TRUNC(months_between(hiredate, sysdate))
from employee;
- add_monthS (날짜, 더할 개월 수)
-특정 개월 수를 더한 날짜
select ename, hiredate,
add_monthS(hiredate, 3), add_monthS(hiredate, -3)
from employee;
- next_day (날짜, '수요일')
-해당 날짜를 기준으로 최초로 도래하는 요일에 해당하는 날짜 반환
select sysdate,
next_day('2021-10-26', '수요일'),
next_day(sysdate, 4) --일요일(1), 월요일(2).....토요일(7)
요일 대신 숫자로 기입 가능
from dual;
- last_day (날짜)
-해당 날짜가 속한 달의 마지막 날 반환
-2월에 사용하면 효과적
select sysdate, last_day(sysdate)
from dual;
select ename, hiredate, last_day(hiredate)
from employee;4. 형 변환 함수
- to_char ( number/date , 'format' )
-날짜/숫자를 문자로 변환
-형식 모델 format은 작은따옴표로 묶어서 대소문자 구분
-format
1) 날짜
YYYY : 연도 표현 (4자리)
YY : 연도 (2자리)
MM : 월을 숫자로 표현
MON : 월을 알파벳으로 표현
DAY : 요일 표현
DY : 요일을 약어로 표현
2) 시간
AM / PM : 오전(AM), 오후 시각 표시
A.M / P.M : 오전(A.M), 오후 시각 표시
HH / HH12 : 시간(1~12) 표시
HH24 : 24시간으로 표현(0~23)
MI : 분 표현
SS : 초 표현
3) 숫자
0 : 자릿수 표현, 자릿수가 맞지 않을 경우 0으로 채운다
9 : 자릿수 표현, 자릿수가 맞지 않으도 채우지 않는다
L : 각 지역별 통화 기호를 앞에 표시
-----------------------------------------------------ex)
1. 날짜
select ename,hiredate,
to_char(hiredate, 'YY-MM'), --입사년도와 월만 출력
to_char(hiredate, 'YYYY/MM/DD DAY'), --입사일과 요일 출력
'YY-MM' 출력값 => 80-12
'YYYY/MM/DD DAY' 출력값 => 1980/12/17 수요일
2. 시간
select TO_CHAR(SYSDATE, 'YYYY/MM/DD, HH24:MI:SS')
from dual;
출력값 => 2021/12/22, 14:34:42
3 . 숫자
select ename, to_char(salary, 'L999,999')
from employee;
출력값 => ₩800
₩1,800 ...
- to_date ( 'char' , 'format' )
-특정 데이터를 날짜형으로 변환
-오라클 기본 날짜 형식 YY/MM/DD
* 특정 날짜에 입사한 사원 검색
select ename, hiredate
from employee
where hiredate=to_date(19810220, 'YYYYMMDD');
- to_number
-특정 데이터를 숫자형으로 변환
*숫자 형태의 문자 빼기 -> 수치 형태로 변환 후 빼기
select to_number('100,000' , '999,999')
-to_number('50,000' , '999,999')
from dual;5 . 일반 함수
- NVL (null을 포함하는 칼럼/표현식 , null 대체값)
-null을 0또는 다른 값으로 변환
select ename, salary, commission,
NVL(commission,0),
salary*12+NVL(commission,0)
from employee;
- NVL2 (1,2,3)
-1을 검사하여 결과가 null이면 2를 반환하고 null이 아니면3을 반환
- NULLIF (1, 2)
-1과 2를 비교하여 동일한 경우 null 반환,
동일하지 않으면 1 반환
- COALESCE (1, 2, 3, 4,...,10)
-인수 중 null이 아닌 첫 번째 인수를 반환
-1이 null이 아니면 1을 반환,
1이 null이고 2가 null이 아니면 2를 반환,
1~9가 null이고 10이 null이 아니면 10을 반환
-모두 null이면 0출력
- DECODE
-switch문과 같은 기능
-조건과 일치하는 값을 반환
-일치하는 값이 없거나 null인 경우 기본 결과 반환
-기본 결과가 명시되지 않았을 경우 null 반환
* 형식
decode(표현식, 조건1, 결과1,
조건2, 결과2,
조건3, 결과3,
기본결과n
)
- case
-if else와 같은 기능
-조건과 일치하는 값을 반환
-일치하는 값이 없거나 null인 경우 결과n 반환
-기본 결과가 명시되지 않았을 경우 null 반환
* 형식
case expr
when 조건1 then 결과1
when 조건2 then 결과2
when 조건3 then 결과3
else 결과
end
