[SQL] 3. 함수 - 1

함수

1. SQL 함수 개요

- 함수 : 데이터를 조작하기 위해 사용됨.

- 특징 :

1) 데이터 계산 수행

2) 개별적인 데이터 항목 수정

3) 행의 그룹에 대해 결과 조작

4) 출력을 위한 날짜와 숫자 형식 설정

5) 열의 데이터 타입 반환

2. SQL 함수의 두 가지 유형

* 단일행 함수

- 오직 단일행에서만 적용 가능하고 행별로 하나의 결과를 반환

 

* 다중행 함수

- 복수의 행을 조작하여 행의 그룹 당 하나의 결과를 반환

 

3. 단일 행 함수

function_name(column | expression, [arg1, arg2, ...])

- 데이터 값 조작

- 인수(argument)를 받고 하나의 결과 리턴

- 리턴될 각각의 행에 적용

- 행별로 하나의 결과 리턴

- 데이터 타입 수정 가능

- 중첩(nested)될 수 있다.

- 종류 : 문자/숫자/날짜/변환/General 함수

 

문자 함수

select initcap('helloworld') from dual;

1. LOWER, INITCAP, UPPER

select last_name, lower(last_name), initcap(last_name), upper(last_name)
from employees;

select last_name, lower(last_name), initcap(last_name), upper(last_name)
from employees
where lower(last_name)='austin';

2. LENGTH, INSTR

select first_name, length(first_name),instr(first_name,'a')
from employees;

3. SUBSTR, CONCAT

select first_name, substr(first_name,1,3), concat(first_name, last_name)
from employees;

4. LPAD, RPAD

select rpad(first_name,10,'-') as name, lpad(salary,10,'*')as sal
from employees;

5. LTRIM, RTRIM

select ltrim('JavaSpecialist','Java') from dual;
select ltrim(' JavaSpecialist') from dual;
select trim( 'JavaSpecialist ') from dual;

6. REPLACE, TRANSLATE

select replace('JavaSpecialist','Java','BigData') from dual;
select replace('Java Specialist',' ','') from dual;
select translate('javaspecialist','abcdefghijklmnopqrstuvwxyz','defghijklmnopqrstuvwxyzabc') from dual;

7. 실전 문제

select rpad(substr(first_name,1,3),length(first_name),'*') as name,
lpad(salary,10,'*') as salary 
from employees
where lower(job_id) = 'it_prog';

 

정규표현식 함수

함수 명칭	설명
REGEXP_LIKE	패턴에 이리하는 문자열을 포함하는 행 찾기 LONG 지원X
REGEXP_INSTR	지정한 조건을 만족하는 부분의 최초의 위치를 돌려줌.
REGEXP_SUBSTR	지정한 정규 표현을 만족하는 부분 문자열 반환
REGEXP_REPLACE	지정한 정규 표현을 만족하는 부분을, 지정한 다른 문자열로 치환

1. 정규표현식

- 횟수 지정 메타 문자

메타 문자	기능
?	0회 또는 1회
*	0회 이상
+	1회 이상
{n}	n회 이상
{m,}	m회 이상
{m,n}	m회 이상 n회 이하

- 메타 문자

메타 문자	기능
.	문자
[]	문자들
[^ ]	부정
^	처음
$	끝
()	그룹 묶기

- 탈출 문자

탈출 문자	기능
\n	n번째 패턴
\w	"-"와 영숫자
\W	\w 반대
\s	공백
\S	공백 제외
\d	숫자
\D	숫자 제외

- POSIX 문자클래스

문자 클래스	설명

 

2. REGEXP_LIKE 함수

REGEXP_LIKE(source_string, pattern[, match_parameter])

Create table test_regexp (col1 varchar2(10));
insert into test_regexp values('ABCDE01234');
insert into test_regexp values('01234ABCDE');
insert into test_regexp values('abcde01234');
insert into test_regexp values('01234abcde');
insert into test_regexp values('1-234-5678');
insert into test_regexp values('234-567890');

select * from test_regexp
where regexp_like(col1,'[0-9][a-z]');

select * from test_regexp
where regexp_like(col1,'[0-9]{3}-[0-9]{4}$');
select * from test_regexp
where regexp_like(col1,'[[:digit:]]{3}-[[:digit:]]{4}$');
select * from test_regexp
where regexp_like(col1,'^[0-9]{3}-[0-9]{4}');

 

create table qa_master (qa_no varchar(10));

alter table qa_master add constraint qa_no_chk check
(regexp_like(qa_no,
'^[[:alpha:]]{2}-[[:digit:]]{2}-[[:digit:]]{4}$'));

insert into qa_master values('QA-01-0001');
insert into qa_master values('00-01-0001'); --오류

 

3. REGEXP_INSTR 함수

REGEXP_INSTR(source_string, pattern [, start_position
[, occurrence [ , return_option [, match_parameter]]]])

insert into test_regexp values('@!=)(9&%$#');
insert into test_regexp values('자바3');

select col1,regexp_instr(col1,'[0-9]') as data1,
regexp_instr(col1,'%') as data2 from test_regexp;

4. REGEXP_SUBSTR 함수

REGEXP_SUBSTR(source_string, pattern [, start_position [, occurrence [, match_parameter]]])

select col1, regexp_substr(col1, '[C-Z]+') from test_regexp;

5. REGEXP_REPLACE 함수

regexp_replace(source_string, pattern [, replace_string [, start_position [, occurrence [, match_parameter]]]])

select col1, regexp_replace(col1,'[0-2]+','*')
from test_regexp;

 

6. 실전 문제

select first_name, phone_number
from employees
where regexp_like(phone_number,'[0-9]{3}.[0-9]{3}.[0-9]{4}$');

 

select first_name, 
regexp_replace(phone_number, '[0-9]{4}$','****') as phone,
regexp_substr(phone_number, '[0-9]{4}$') as phone2
from employees;

숫자 함수

1. ROUND, TRUNC

select round(45.923,2), round(45.923,0), round(45.923,-1) from dual;

select trunc(45.923,2), trunc(45.923), trunc(45.923,-1) from dual;

 

날짜 함수

select sysdate from dual;

1. 날짜의 연산

- 날짜에서 숫자를 더하거나 빼 날짜 결과를 반환

- 날짜 사이의 일(day) 수를 알기 위해 두 개의 날짜를 뺀다

- 시간을 24로 나누어 날짜에 더한다.

select first_name, (sysdate-hire_date)/7 as "weeks"
from employees
where department_id=60;

2. 날짜 함수

select first_name, sysdate, hire_date, months_between(sysdate, hire_date) as workmonth
from employees
where first_name='Diana';

select first_name, hire_Date, add_months(hire_date, 100)
from employees
where first_name='Diana';

select sysdate, next_day(sysdate,'월')
from dual;

select sysdate, last_day(sysdate) from dual;

select sysdate, round(sysdate), trunc(sysdate) from dual;

select sysdate, round(sysdate), trunc(sysdate) from dual;
select trunc(sysdate, 'Month') from dual;
select trunc(sysdate, 'Year') from dual;
select trunc(to_date('17/03/16'), 'Month') from dual;
select round(to_date('17/03/16'), 'Month') from dual;
select trunc(to_date('17/03/16'), 'Day') from dual;