5. 문자열 함수(1)

1.  함수의 정의
- input value(인수) 와 output value 와의 관계를 표현하는 객체
- 대체적으로 하나 이상의 input value(인수) 필요
- (인수가 없는 함수도 존재)
 ex) sysdate
- 반드시 output value return
- 사용자가 직접 정의하여 사용 가능(PL/SQL)
- from 절을 사용한 모든 select 문의 절에서 사용 가능\

 

 

2. 함수의 종류

1) 인수의 결과값의 매칭수에 따라
- 단일행 함수 : 한쌍의 인수에 대칭되는 하나의 결과값이 리턴되는 형태의 함수
  ex) upper('abcd')  -> 'ABCD'

 

- 다중행 함수(그룹함수) : 여러 행의 데이터가 하나의 결과로 리턴되는 형태의 함수
  ex) sum(sal), max(sal), min(sal), avg(sal), count(sal)

 

2) 인수나 결과값의 데이터 타입에 따라 
- 문자함수, 숫자함수, 날짜함수, 변환함수, 일반함수

 

 

 

3. 문자함수

 

1) 대소문자 치환 : upper, lower, initcap
 ** 사용법 : upper(대상), lower(대상), initcap(대상)
 
select upper('abcd'), lower('abcd'), initcap('abc abc') 
  from dual;
  
  
  

 

2) 문자열 추출 : substr
- 문자열의 일부를 추출하는 함수
- 리턴 결과는 항상 문자타입

 ** 사용법 : substr(원본대상, 시작위치[, 개수])
- 추출개수 생략시 끝까지 추출
- 시작위치 생략불가
- 시작위치 음수 가능(뒤에서부터의 위치 지정, 추출방향은 왼쪽에서 오른쪽)

 

select substr('abcdef', 3, 2),     -- cd 
       substr('abcdef', 3),        -- cdef  
       substr('abcdef', -3, 2)     -- de
  from dual; 

 

예제) student 테이블에서 모든 여학생의 이름, 학년, 주민번호 출력

sol1)

select name, grade, jumin
  from student
 where substr(JUMIN,7,1) = '2';  

--> JUMIN이 타입이 문자기 때문에 substr 한 결과도 문자이므로 결과값도 문자로 비교해야 빨라짐.
 
 

sol2)

select name, grade, jumin
  from student
 where jumin like '______2%';

 

결과는 다음과 같다. 

예제) professor 테이블에서 박씨 성의 교수에 대한 이름, 직금, 급여 출력
 
sol1)

 

select name, position, pay
  from professor
 where substr(name, 1, 1) = '박';

 

sol2)
select name, position, pay
  from professor
 where name like '박%';

 

 

결과는 다음과 같이 나타나는걸 알 수 있다.

 

 

3) 문자열 위치 : instr

- 문자열에서 일부 문자열의 위치를 찾을 때 사용
- 항상 숫자로 리턴

 

 ** 사용법 : instr(원본대상, 문자열[, 시작위치][, 발견횟수])
- 시작위치 생략가능(생략 시 1)
- 발견횟수 생략가능(생략 시 1)
- 시작위치 음수 가능(뒤에서부터의 위치 지정, 추출방향은 오른쪽에서 왼쪽)
- 찾는 문자열이 없을 경우 0 리턴

 

관련 예제로 학습해보자.

 

select 'a#b#c#d#e#', 
       instr('a#b#c#d#e#','#'),          -- 2
       instr('a#b#c#d#e#','#',5),       -- 6 (앞에서 5번째부터 처음 발견된 #의 위치)
       instr('a#b#c#d#e#','#',3,3),     -- 8 (앞에서 3번째부터 세번째로 발견된 #의 위치)
       instr('a#b#c#d#e#','#',-4)        -- 6 (뒤에서부터 4번째에서 시작해서 왼쪽 방향으로 처음 발견된 #의 위치)
 from dual;

 

방향에 주의해야 한다.

 

4) 문자열 길이 : length, lengthb

- 문자열의 길이를 리턴하는 함수
- length는 문자열의 수
- lengthb는 문자열의 크기(bytes) 리턴

 

** 사용법 : length(대상)

select length('abcd'), lengthb('abcd'),
       length(1000), lengthb(1000),
       length('한글'), lengthb('한글')         -- 한글 한 자당 2bytes(character set에 따라 다름)
       
  from dual;
 

 

 

5) 문자열 치환 : replace
- 문자열 치환 또는 삭제

** 사용법 : replace(대상, 찾을문자열[, 바꿀문자열])
- 바꿀문자열 생략 시 문자열 삭제
- 바꿀문자열에 빈문자열 전달 시 찾을문자열 삭제

 

예제1)

select 'abcba', 
       replace('abcba', 'ab', 'AB'),       
       replace('abcba', 'ab'),                
       replace('abcba', 'ab', '')             
 from dual;

 

결과)

ABbca

cba

cba

 

예제2) 마스킹  

student 테이블에서 4학년 학생의 이름, 학년, 주민번호 출력
-- 단, 주민번호 뒷자리는 마스킹처리(7510231901813 => 751923xxxxxxx)

 

sol) 

select name, grade, replace(jumin, substr(jumin,7) , 'XXXXXXX') as 마스킹1,
       substr(jumin,1,6)||'XXXXXXX' as 마스킹2 --이 방법도 가능
  from student
 where grade = 4 ;

 

 

다음 결과를 얻을 수 있다.