< 단일 행 함수 >
① 문자함수
- 대소문자 변환 함수
· LOWER(바꿀 문자열) : 소문자로 변환
· UPPPER(바꿀 문자열) : 대문자로 변환
· INITCAP(바꿀 문자열) : 맨 앞문자만 대문자
- 문자 조작 함수
· CONCAT(expr1, expr2) : 두 인수를 연결해서 하나의 문자열로 반환
select concat(first_name, last_name) as fullname
from employees;
(==)
select first_name || last_name as fullname
from employees;
· SUBSTR(expr1, 시작위치, 반환할 문자수) : 문자열 일부분을 반환
substr(주민번호, 1, 6) // 020618
substr(전화번호, 10) // 1234
substr('HelloWorld' , -1, 1) // d
substr('Kochhar', -3, 2) // ha
→ 반환할 문자수 생략됐을 때는 문자 끝까지 반환
→ 시작위치가 음수값이면 거꾸로 (맨뒷자리부터 시작)
→ 시작위치가 끝이라고해서 반환 방향이 바뀌는건 아님. 똑같이 정방향으로 진행
· LENGTH(expr1) : 문자열 길이 반환
LENGTH('HelloWorld') // 10
· INSTR(문자열, 지정문자) : 문자열로부터 특정 문자의 첫 번째 인덱스값 반환
INSTR('HELLOWORLD', 'W') // 6
select employee_id, last_name, instr(last_name,'a')
from employees;
→ 'a'가 두개이상이면 제일 처음 등장하는 문자 인덱스로 반환
· LPAD(반환할 문자열, 전체 자리수, 남는 공간을 채울 문자) : 전체자리수가 되도록 왼쪽부터 지정문자로 채움 (오른쪽 정렬)
· RPAD(반환할 문자열, 전체 자리수, 남는 공간을 채울 문자) : 전체자리수가 되도록 오른쪽부터 지정문자로 채움 (왼쪽 정렬)
LPAD(salary, 10, '*') // *****24000
RPAD(salary, 10, '*') // 24000*****
select lpad(last_name, 20, ' ') last_name, rpad(first_name, 20, ' ') first_name
from employees;
· REPLACE(문자열, 문자열 중 특정문자, 바꿀문자) : 문자열의 특정문자를 다른 문자로 교체
REPLACE('JACK and JUE', 'J', 'BL') // BLACK and BLUE
select employee_id, last_name, phone_number
from employees;
select employee_id, last_name, REPLACE(phone_number, '.', '-') phone
from employees;
· TRIM(특정 문자 FROM 문자열) : 문자열로부터 특정 문자가 접두어나 접미어에 있다면 삭제
TRIM('H' FROM 'HelloWorld') // elloWorld
TRIM('H' FROM 'HHelloHHHWorldHHHH') //elloHHHWorld
② 숫자함수
- ROUND(숫자, 소수점 자릿수) : 소수점 n번째자리까지 반올림
- TRUNC(숫자, 소수점 자릿수) : 소수점 n번째자리까지 남기고 버림
· 숫자 자리 인덱스
| ... | 1 | 2 | 3 | 4 | . | 5 | 6 | 7 | ... |
| ... | -3 | -2 | -1 | 0 | 1 | 2 | 3 | ... |
※ 일의 자리 0은 생략가능 !
- MOD( 숫자, 나눌 수) : 나머지 반환
ROUND(45.926, 2) // 45.93
TRUNC(45.926, 2) // 45.92
MOD(1600, 300) // 100
※ DUAL 테이블
- 함수나 계산 결과를 볼 때 사용하는 공용 테이블
- SYS 소유
- DUMMY라는 하나의 열과 값이 X인 하나의 행 포함
③ 날짜함수
- SYSDATE : 현재 데이터베이스 서버 날짜 및 시간을 반환
- 인수 없음 !
select sysdate from dual;
→ 기본 포맷이 'DD-MON-RR'
- 날짜를 사용한 산술연산도 가능
| 연산 | 결과 | 설명 |
| 날짜 + 숫자 | 날짜 | 날짜에 일 수를 더함 |
| 날짜 - 숫자 | 날짜 | 날짜에 일 수를 뻄 |
| 날짜 - 날짜 | 일 수 | 한 날짜를 다른 날짜에서 뺌 |
| 날짜 + 숫자/24 | 날짜 | 날짜에 시간 수를 더함 |
select last_name, (SYSDATE-hire_date)/7 as WEEKS
from employees
where department_id = 90;
- 날짜 조작 함수
· MONTHS_BETWEEN( 날짜1, 날짜2 ) : 두 날짜 간의 월 수
· ADD_MONTHS( 날짜, 더할 개월수 ) : 날짜에 월 추가
· NEXT_DAY( 날짜, 요일 ) : 날짜에 지정한 요일의 다음 날짜 반환
· LAST_DAY(날짜) : 월의 마지막 날
MONTHS_BETWEEN('01-SEP-95', '11-JAN-94'); // 19.6774194
ADD_MONTHS('31-JAN-96',1) // '29-FEB-96'
NEXT_DAY('12-NOV-21','FRIDAY') // 19-NOV-21'
LAST_DAY('12-NOV-21') // '30-NOV-21'
· ROUND( 날짜, 반올림할 기준 ) : 날짜 반올림
· TRUNC( 날짜, 버림할 기준 ) : 날짜 버림
SYSDATE = '25-JUL-03' 이라고 가정
ROUND(SYSDATE, 'MONTH') // 01-AUG-03
ROUND(SYSDATE, 'YEAR') // 01-JAN-04
TRUNC(SYSDATE, 'MONTH') // 01-JUL-03
TRUNC(SYSDATE, 'YEAR') // 01-JAN-03
→ ROUND(SYSDATE, 'MONTH') 면 DAY를 기준으로 '1~15' 일까지 버림 / '16~30(31)'일 까지 올림
→ ROUND(SYSDATE, 'YEAR') 면 MONTH를 기준으로 '1~6' 월까지 버림 / '7~12'월 까지 올림
round(to_date('25-JUN-21'), 'YEAR') // 01-JAN-21
round(to_date('25-JUL-21'), 'YEAR') // 01-JAN-22
④ 변환함수
| 요소 | 결과 |
| YYYY | 2020, 2021, ... |
| YEAR | TWENTY TWENTY-ONE, ... |
| YY / RR | 20, 21, ... |
| MM | 06, 07, 08, ... |
| MONTH | JUNE, JULY, AUGUST, ... |
| MON | JUN, JUL, AUG, ... |
| DY | MON, TUE, ... |
| DAY | MONDAY, TUESDAY, ... |
| DD | 01, 02, 03, ... |
| HH24:MI:SS [AM/am] | 15:45:32 [PM/pm] |
· 영어로 출력되는 함수는 요소가 대문자/소문자에 따라 결과도 대문자/소문자 구분함
( MON → JULY / mon → july / Mon → Jul / DY → FRI / dy → fri )
- TO_CHAR( DATE, '원하는 포맷' )
· DATE → CHAR
TO_CHAR(SYSDATE, 'MM/YY') // 11/21
· 포맷안에 문자열 넣으려면 큰 따옴표로 묶어서 추가 / 여러번 넣을 수 있음
select to_char(sysdate, 'YYYY-MM-DD "Time" HH24:MI') as today
from dual;
select to_char(sysdate, '"Today" YYYY-MM-DD "Time" HH24:MI') as now
from dual;
· ddsp : 날짜를 영어 철자로 표기
· ddspth : 날짜를 서수까지 표기
to_char(sysdate, 'ddsp') // fourteen
to_char(sysdate, 'ddspth') // fourteenth
· fm : 앞에 오는 0 없애줌 ( ex) 01 → 1 )
to_char(sysdate, 'fmDD') // 1,2,3,4,5,6,7,8,9,10, ...
- TO_CHAR( 숫자, '원하는 포맷' )
· NUMBER → CHAR
| 요소 | 결과 |
| 9 | 0 ~ 9 |
| 0 | 0이 표시되도록 강제 적용 |
| $ | 달러기호 |
| L | 원, 엔, ... |
| . | 소수점 |
| , | 천단위 표시 |
TO_CHAR(salary, '$999,999.00') // $4,200.00
→ 자리수 초과하는 경우 #으로 출력
