[Oracle - SQL Developer] String Function : 문자열 함수

 

 

 

 

 

 

 

---

 

 

String Function 문자열 함수

 

upper(), lower(), initcap()
- varchar2 upper(컬럼명)
- varchar2 lower(컬럼명)
- varchar2 initcap(컬럼명)

 

select first_name, upper(first_name), lower(first_name) from employees;
select initcap('abc') from dual;
 
 
-- first_name에 'DE'가 포함된 직원
select first_name from employees
    where upper(first_name) like '%DE%';

 

 

 

 

 

---

 

 

 

SUBSTR()

 

- 문자열 추출 함수
- varchar2 substr(컬럼명, 시작위치, 가져올 문자개수)
- varchar2 substr(컬럼명, 시작위치)
- ** SQL ONe-based Index 사용. 첨자를 1부터 시작한다.

 

select
    title,
    substr(title, 3, 4)
from tblTodo;
 
select
    first_name || last_name,
    substr(first_name || last_name, 3, 4),
    substr(first_name || last_name, 3)
from employees;
 
 
-- 남자 직원(1900년대생 + 2000년대생)
select count(*) from tblInsa where ssn like '%-1%' or ssn like'%-3%';
select count(*) from tblInsa where substr(ssn,8, 1) in ('1', '3', '5', '7', '9');-- 8번째 자리에서 1글자
select count(*) from tblInsa where substr(ssn,8, 1) in ('2', '4');-- 8번째 자리에서 1글자
 
-- 직원명, 태어난 년도를 가져오시오. -> 모두 1900년대생이라고 가정
select
    name,
    '19' || substr(ssn, 1, 2) as birthyear
from tblInsa;
 
 
-- 서울에 사는 여자 직원 중 80년대생 몇 명?
select count(*) from tblInsa
    where city = '서울' and substr(ssn, 8, 1) = '2' and substr(ssn, 1, 1) = '8';
 
-- 직원들의 성(family name)이 어떤 것이 있는 지 추출해라
select
    distinct substr(name, 1, 1)
from tblInsa;
 
-- 김, 이, 박, 최, 정 -> 몇 명?
select
    count(case
        when substr(name, 1, 1) = '김' then name
    end) as 김,
    count(case
        when substr(name, 1, 1) = '이' then name
    end) as 이,
    count(case
        when substr(name, 1, 1) = '박' then name
    end) as 박,
    count(case
        when substr(name, 1, 1) = '최' then name
    end) as 최,
    count(case
        when substr(name, 1, 1) = '정' then name
    end) as 정,
    count(case
        when substr(name, 1, 1) not in ('김', '이', '박', '최', '정') then name
    end) as 기타
from tblInsa;

 

 

 

 

---

 

 

 

LENGTH()

 

- 문자열 길이(문자수 반환)
- number length(컬럼명)

 

-- 컬럼 리스트에서 사용
select name, length(name) from tblCountry;
 
-- 조건절에서 사용
select name from tblCountry where length(name) > 3;
select name from tblCountry where length(name) between 4 and 6;
select name from tblCountry where mod(length(name),2) =1; -- 이름이 홀수인 것들
 
-- 정렬에서 사용
select name from tblCountry order by length(name) asc, name asc;
 
-- 남자 -> 여자
select * from tblInsa order by substr(ssn, 8, 1) asc, ssn asc; -- 남자나이순 -> 여자 나이순 
 
 
-- substr() + length() + case
select -- 같은 너비로 만들고 싶음 
    title,
    case
        when length(title) >= 8 then substr(title, 1, 8) || '..' -- 자르기 + 사용자 이해를 위해 .. 을 붙여주기
        when length(title) < 8 then title
    end
from tblTodo;

 

 

 

---

 

 

INSTR()

 

- 검색 함수(= indexOf())
- 검색어의 위치를 반환
- number instr(컬럼명, 검색어)
- number instr(컬럼명, 검색어, 시작위치)

 

select
    '안녕하세요. 삐옥이님',
    instr('안녕하세요. 삐옥이님', '삐옥이') as c2, -- 8
    instr('안녕하세요. 삐옥이님', '아무개') as c3, -- 0
    instr('안녕하세요. 삐옥이님 반갑습니다. 삐옥이님', '삐오기') as c4, -- 8
    instr('안녕하세요. 삐옥이님 반갑습니다. 삐옥이님', '삐옥이', 11) as c5, -- 20
    instr('안녕하세요. 삐옥이님 반갑습니다. 삐옥이님', '삐옥이', 
        instr('안녕하세요. 삐옥이님 반갑습니다. 삐옥이님', '삐옥이') + length('')) as c6  
from dual;
 
 
select * from tblInsa where tel like '010%';
select * from tblInsa where substr(tel, 1, 3) = '010';
select * from tblInsa where instr(tel, '010') = 1;
 
select * from tblTodo order by instr(title, '하');

 

 

 

---

 

 

LPAD(), RPAD()

 

- left padding, right padding
- varchar2 lpad(컬럼명, 개수, 문자)
- varchar2 rpad(컬럼명, 개수, 문자)

 

select
    'a',
    lpad('a', 5, 'b'),
    '1',
    lpad('1', 3, '0'),
    rpad('a', 5, 'b')
from dual;

 

 

 

---

 

 

 

TRIM(), LTRIM(), RTRIM()

 

- varchar2 trim(컬럼명)

 

select
    '     삐옥이     ',
    trim('     삐옥이     '),
    ltrim('     삐옥이     '),
    rtrim('          ')
from dual;

 

 

 

---

 

 

REPLACE()

 

- 문자열 치환
- varchar2 replace(컬렴명, 찾을 문자열, 바꿀 문자열)

 

select 
    replace('홍길동', '홍', '김'), 
    replace('홍길동', '이', '김') --그대로 홍길동 반환     
from dual;
 
-- 직원명, 주민번호, 성별(남자|여자)
select name, ssn, substr(ssn, 8, 1) from tblInsa;
 
select
    name, ssn,
    case
        when substr(ssn, 8, 1) = '1' then '남자'
        when substr(ssn, 8, 1) = '2' then '여자' 
    end
from tblInsa;
 
 
select
    name, ssn,
    replace(replace(substr(ssn, 8, 1), '1', '남자'), '2', '여자') -- = replace(aaa, '2', '여자')
from tblInsa;
 
select
    name,
    continent,
    replace(replace(replace(replace(replace(continent, 'AS', '아시아'), 'SA', '아메리카'), 'EU', '유럽'), 'AF', '아프리카'), 'AU', '호주'),
    case
        when continent = 'AS' then '아시아'
        when continent = 'SA' then '아메리카'
        when continent = 'EU' then '유럽'
        when continent = 'AF' then '아프리카'
        when continent = 'AU' then '호주'
    end
from tblCountry;

 

 

 

---

 

 

DECODE()

 

- 문자열 치환
- replace(), case 유사
- varchar2 decode(컬럼명, 찾을 문자열, 바꿀 문자열 [, 찾을 문자열, 바꿀 문자열] x N)
- 못찾으면 null을 반환

 

-- decode() 함수가 찾지 못했을 때 null을 반환하는 특성이 case문과 유사
--  > case를 응용하던 구문에 decode()를 사용할 수 있다.
 
select * from tblComedian;
 
select
    count(case
        when gender = 'm' then 1
    end),
    count(case
        when gender = 'f' then 1
    end)
from tblComedian;
 
 
select
    count(decode(gender, 'm', 1)),
    count(decode(gender, 'f', 1))
from tblComedian;
 

 

 

 

 

 

 

---