DAY-21. Oracle 문자열 함수2

🔐 2022-03-30

데이터베이스

❗ 수업을 듣고, 개인이 공부한 내용을 적은 것 이기에 오류가 많을 수도 있음

문자열 함수

자바에서의 메서드(method) = 오라클에서는 함수(fucntion)

❗ sql에서 인덱스는 1부터 시작한다 명심

• 단일 행 함수 : 각 행마다 반복적으로 적용돼서 입력받은 행의 개수만큼 결과를 반환
  • length() → 데이터의 길이값 반환 , 글자 하나하나 세서 갯수를 출력
    • ex) select emp_name, length(email) from employee;
• 그룹 함수 : 특정한 행들의 집합으로 그룹이 형성되어 ⇒ 그룹당 1개의 결과를 반환
  • ex) sum → select sum(salary) from employee;
• 문자형 함수 : 주어진 컬럼/문자열에 대한 길이(byte)로 반환해주는 함수
  • ex) lengthB() : 한글 3바이트 -> 이름 3글자 이기에 -> 9byte

---

1️⃣ 문자 찾기 -> instr

instr(컬럼/문자열, 찾고자 하는 문자열(열), 시작 인덱스, 검색된 문자(열)의 순번)

=> 특정 문자열에서 찾고자 하는 문자열의 위치를 찾아주는 함수

dual 테이블 : 오라클에서 제공해주는 연산, 함수 실행 용도로 사용하는 특수한 테이블

• 예제)

    1. select instr('Hello Wolrld Hi High', 'H', 1, 1) from dual;
   - 1번인덱스 부터 찾기 시작하는데 1번째로 H가 나오는 위치를 알려준다 // 1

    2. select instr('Hello Wolrld Hi High', 'H', 1, 2) from dual;
   - 1번인덱스 부터 찾기 시작하는데 2번째로 H가 나오는 위치를 알려준다 // 14

    3. select instr('Hello Wolrld Hi High', 'H', 2, 2) from dual;
    - 2번인덱스 부터 찾기 시작하는데 2번째로 H가 나오는 위치를 알려준다 // 17

    4. select instr('Hello Wolrld Hi High', 'H', -1, 1) from dual;
    - 1은 맨마직 h부터 찾기 시작하는데, 1번째로 나오는 H는 High의 H

    // 17 , 시작 인덱스가 -면 뒤에서 부터 탐색

• 실습)

    1. 문제 : employee의 테이블에서 email안에서 @ 찾기
    - select email, instr(email, '@', 1, 1) from employee;

---

2️⃣ 공백채우기 -> lapd,rpad

lpad,rpad(대상이 되는 컬럼/문자열, 반환할 길이, 덧붙일 문자(열))

• 주어진 컬럼/문자열을 대상으로 해서 임의의 문자열을 왼쪽/오른쪽에 덧붙여서 길이N의 문자열을 반환하는 함수

   1. select lpad(email, 20, '#') from employee;
   - 20개의 길이를 반환 이메일은 20개 보다 못함

   -> 남는 길이에 #를 삽입 해준다

   -> lpad여서 왼쪽에 삽입(left pad)

    2. select rpad(email, 20, '#') from employee;

---

3️⃣ 문자 자르기-> trim

• ltrim,rtrim(대상이 되는 컬럼/문자열, 제거하고 싶은 문자(열))
  • 주어진 컬럼/문자열을 대상으로 제거하고 싶은 문자를 제거한 뒤 나머지를 반환하는 함수
    • ltrim : 제거하고 싶은 문자열이 왼쪽(문자열의 시작)에 위치 해있을 때 -> 즉 제거 하고싶은게 문자열의 시작에 없으면 효과x
    • rtrim : 문자열의 오른쪽(문자열의 끝)에 제거하고 싶은 문자열이 위치;
• 예제)

    1. 문제 1. Hello My JAVA 문자열을 Hello My가 출력되게 하여라
    - select rtrim('Hello My JAVA','JAVA') from dual;

    2. 문제 2. Hello My JAVA 문자열을 My JAVA가 출력되게 하여라
    - select ltrim('Hello My JAVA', 'Hello') from dual;

    3. 문제 3. DEPARTMENT 테이블에서 DEPT_TITLE을 출력하여라

    (이때, 마지막 부 글자를 빼고 출력되도록 하여라 / ex) 회계관리부 -> 회계관리)
    - select rtrim(dept_title,'부') from department;

    4. 문제 4.'982341678934509hello89798739273402'에서 앞뒤 모든 숫자를 제거하세요.
    - select rtrim(ltrim('982341678934509hello89798739273402','0123456789'),'0123456789') from dual;
    - 숫자 같은 경우 0123456789라고 해도 0123456789에 해당되는 숫자는 제거해줌
    - ltrim에서 제거된 결과값을 바로 rtrim으로 제거

---

4️⃣ 원하는 부분만 출력 -> substr

substr(대상이 되는 컬럼/문자열, 문자열을 잘라낼 위치,반환할 문자의 개수) : 대상이 되는 컬럼/문자열에서 시작위치로부터 제시한 문자의 개수만큼 문자(열)을 잘라서 반환해주는 함수

• 예제)

    1. select substr('HappyHappyDay', 1,5) from dual;
    - 1번 인덱스부터 5개를 잘라내겠다 // happy

    2. select substr('HappyHappyDay', 6,8) from dual;
    - 6번 인덱스부터 8개를 잘라내겠다 // HappyDay

    3. select substr('HappyHappyDay', 7,2) from dual;
    - 7번 인덱스부터 2개를 잘라내겠다 // ap

    4. select substr('HappyHappyDay', -7,3) from dual;
    - 7번 인덱스부터(a)부터 3개를 잘라내겠다 // app

    5. employee 테이블에서 사원명 조회 -> 성만 조회
    - select substr(emp_name,1,1) from employee;

• 실습)
  • employee 남자 사원의 사원 번호, 사원명, 주민번호, 연봉을 조회 ,주민번호의 뒷자리의 6자리를 모두 *로 표시하시오

     풀이 1번
     select emp_id "사원 번호", rpad(rtrim(emp_no,'0123456789'),14,'#') "사원 번호",

     emp_name "사원 이름", salary "연봉" from employee where emp_no like '%-1%';


    =>남자의 주민버호는 뒷자리가 1이므로 -1 앞뒤는 어떠한 숫자가 와도 되니,

    -1 앞 뒤를 %%로 묶어준다

    => (주민번호 뒷자리를 다없애준다 -> trim 사용 -> 주민번호를 #으로 채워준다 -> rpad사용)

    풀이 2번
    select emp_id "사원 번호", substr(emp_no,1,8) || '******' "사원 번호",

    emp_name "사원 이름", salary "연봉" from employee where substr(emp_no,8,1) in ('1','3');

---

5️⃣ 문자열 합치기 -> concat

• concat(대사문자열1, 대상문자열2) : 두개의 문자열을 하나로 합친 후 반환해주는 함수

• 예제)

    1. select concat('ABCD', '가나다라') from dual; -- ABCD가나다라 출력

    2. select 'ABCD' "||" '가나다라' from dual; -- ABCD가나다라 출력

    3. select concat(concat('ABCD', '가나다라'),'안녕하세요') from dual;

     -- ABCD가나다라안녕하세요

    4. select 'ABCD' "||" '가나다라' "||" '안녕하세요' from dual;

---

7️⃣ 대문자 , 소문자, 첫글자 대문자

• lower(대상 문자열/컬럼) / upper(대상 문자열/컬럼) / initcap(대상 문자열/컬럼)
  • lower -> 대상 문자열을 모두 소문자로 변환해주는 함수
  • upper -> 대상 문자열을 모두 대문자로 변환해주는 함수
  • initcap -> 단어의 첫 글자만 대문자로 변환해주는 함수
• 예제)

    1. select lower('HAPPY')from dual; -- happy 출력

    2. select upper('happy')from dual; -- HAPPY 출력

    3. select initcap('happy') from dual; -- Happy 출력

---

8️⃣ 문자(열) 변경 replace

• replace(대상이 되는 문자열 / 컬럼 ,’변경되상이 되는 문자(열), ‘변경할 문자(열)’)

• 대상이 되는 문자열/컬럼을 기준으로 변경대상이 되는 문자(열)를 찾아서 변경할 문자(열)로 바꿔주는 함수

• 예제)

    1. select replace('Hello Hi High','Hi','Ho') from dual;

     --> Hello Ho Hogh 출력 -- Hi를 Ho로 변경

    2. select replace('Hello Hi High','Hi','') from dual;

    --> Hello gh -- Hi를 공백으로 바꿔주면 된다.