[Oracle SQL] 문자열 함수

고동이의 IT·2021년 10월 8일
0

Oracle SQL

목록 보기
9/31
post-thumbnail

함수(Function)

  • 시스템에 컴파일되어 제공되는 실행가능한 모듈
  • 문자열, 숫자, 날짜, 형변환, NULL처리, 집계함수가 제공

    1. 문자열 함수

    1) CONCAT(C1,C2)

    매개변수가 문자열 타입
    - 주어진 문자열 C1, C3를 결합하여 새로운 문자열 반환
    - '||' 연산자와 동일 기능

사용예) 회원 테이블에서 회원명과 주민번호를 출력하시오
단 주민번호는 'XXXXXX-XXXXXXX' 형식으로 출력하며 CONCAT 함수를 사용

   SELECT MEM_NAME AS 회원명,
              CONCAT(CONCAT(MEM_REGNO1,'-'), MEM_REGNO2) 
              AS 주민번호
         FROM MEMBER ; 
         --|| 쓰고말지 복잡해서 잘안씀


테이블 변경: ALTER

  • 테이블의 컬럼을 추가/삭제/변경
  • 테이블의 제약조건의 추가/변경/삭제
  • 테이블 이름 변경

1) 테이블 이름 변경

  • 테이블의 이름을 변경
  • 이름이 변경되어도 기본키와 외래키에는 영향이 없음
    (사용형식)
ALTER TABLE old_table_name RENAME TO new_table_name;

사용예) HR 계정의 EMPLOYEES 테이블을 EMP로, DEPARTMENTS를 DEPT로 변경하시오

    ALTER TABLE EMPLOYEES RENAME TO EMP;
      ALTER TABLE DEPARTMENTS RENAME TO DEPT;

2) 테이블의 컬럼을 추가/삭제/변경
(1)컬럼명 변경
ALTER TABLE 테이블명 RENAME COLUMN old_column_name
To new_colunm_name;

(2)컬럼속성 변경
ALTER TABLE 테이블명 MODIFY(컬럼명 데이터타입[(크기[BYTE|CHAR]) [DEFAULT 값])

(3)컬럼 삭제
ALTER TABLE 테이블명 DROP COLUMN 컬럼명;

(4)컬럼 추가
ALTER TABLE 테이블명 ADD(컬럼명 데이터타입[(크기[BYTE|CHAR]) [DEFAULT 값]);

사용예)사원테이블(EMP)에서 EMP_NAME 이라는 컬럼(VARCAHR2(80))을 추가하고
FIRST_NAME과 LAST_NAME컬럼의 값을 합쳐서 EMP_NAME컬럼에 값을 배정하시오. 단, 두이름을 합칠때 중간에 공백을 삽임하시오
또, 퇴직자 처리를 위해 RETIRE_DATE 컬럼(DATE)을 추가하고 기본값은 NULL를 배정하시오.

(컬럼추가)

ALTER TABLE EMP ADD (EMP_NAME VARCHAR2(80));
       ALTER TABLE EMP ADD (RETIRE_DATE DATE);
    -- EMP_NAME에 값을 배정
   UPDATE EMP 
      SET EMP_NAME=CONCAT(FIRST_NAME,CONCAT(' ', LAST_NAME));
      
  COMMIT;

2) LOWER(c), UPPER(c), INITCAP(c)

문자열이면 대소문자 구분.

- LOWER(c): 주어진 자료 c의 모든 문자를 소문자로 변환하여 반환
- UPPER(c): 주어진 자료 c의 모든 문자를 대문자로 변환하여 반환
- INITCAP(c): c에 포함된 단어의 첫 글자만 대문자로 변환하며 단어의 구분은 공백 --낙타식

사용예)회원테이블에서'D001'회원의 정보를 조회하시오
Alias는 회원명, 전화번호(핸드폰),직업이다

    SELECT MEM_NAME AS 회원명,
         MEM_HP AS "전화번호(핸드폰)",
         MEM_JOB AS 직업
    FROM MEMBER
   WHERE UPPER(MEM_ID)='D001';
   
   SELECT EMP_NAME, PHONE_NUMBER, PHONE_NUMBER, LOWER(EMAIL) || 
   '@' FROM EMP;
   
   SELECT EMP_NAME,
          LOWER(EMP_NAME),
          INITCAP (LOWER(EMP_NAME))
     FROM EMP;
     
     ```

3) LPAD(c1, n[,c2]), RPAD(c1, n[,c2])

  • 주어진 문자열 c1을 n만큼 확보된 기억장소에 저장 한 후 남는 기억장소(왼쪽(오른쪽))에 c2를 채움
  • c2가 생략되면 공백이 채워짐
  • c1의 크기보다 작은 공간이 지정되면 오류

사용예) 상품테이블에서 분류코드 'P102'에 속한 상품정보를 조회하시오
Alias는 상품코드, 상품명, 매입단가, 판매단가이며 매입단가, 판매단가는 10자리에 출력하고 남는 왼쪽공간에는 '*'를 출력하시오

SELECT PROD_ID AS 상품코드,
              PROD_NAME AS 상품명,
              LPAD(PROD_COST,10,'*') AS 매입단가,
              RPAD(PROD_PRICE,10,'*') AS 판매단가
         FROM PROD
        WHERE UPPER(PROD_LGU)='P102';
      사용예) 대전에 거주하는 회원을 조회하시오 
   Alias는 회원번호, 회원명, 주민번호, 주소이며, 
   주민등록번호는 '123456-2******' 형태로 출력
SELECT MEM_ID AS  회원번호,
              MEM_NAME AS 회원명,
              MEM_REGNO1||'-'||RPAD(LPAD(MEM_REGNO2,1),7,'*') 
              AS 주민번호, 
              MEM_ADD1||MEM_ADD2 AS 주소
         FROM MEMBER
        WHERE MEM_ADD1 LIKE '대전%';

4) LTRIM(c1[,c2]), RTRIM(c1,[,c2])

  • 주어진 문자열 c1에서 우측(좌측)에서 c2를 찾아 제거함, c2가 생략되면 공백을 제거
  • 단어 내부의 공백은 제거하지 못함(REPLACE함수로 제거)

** 사원테이블의 이름(EMP_NAME)의 데이터 타입을 CHAR(80)으로 변경

ALTER TABLE EMP MODIFY(EMP_NAME CHAR(80));
  ALTER TABLE EMP MODIFY(EMP_NAME VARCHAR2(80));
--CHAR을 VARCHR2로 변경했을때 공백이 축소되지않음 ㄱ-

 UPDATE EMP
    SET EMP_NAME=RTRIM(EMP_NAME); --공백제거
    
    COMMIT;

사용예)

SELECT LTRIM('나보기가 역겨워','보기'), 
-- C1, C2 시작글자다르면 못지움
              LTRIM('나보기가 역겨워','나보기'), 
              -- 같으면 지움
              LTRIM('      나보기가 역겨워      ')
         FROM DUAL; -- 가상으로 테이블 역할을 해주는것 DUAL

 -> 시스템관리자에서 제공해주는 테이블. 정확히는 SYS.DUAL임

5) TRIM(c1)

문자열 c1에 존재하는 무효의 공백(오른쪽 및 왼쪽)을 제거
단어내부에 들어있는 공백: 유효 공백

6) SUBSTR(c,m[,n])

가장많이 사용되는 문자열 함수

  • 주어진 문자열 c에 저장된 데이터에서 왼쪽부터 m번째 문자를 시작으로 n개의 문자를 추출하여 반환
  • n이 생략되면 m번째부터 나머지 모두를 반환
  • m이 0이면 1로 간주
  • m이 음수이면 오른쪽부터 처리

사용예)

 SELECT SUBSTR('DB Modeling and Oracle SQL', 4, 8),
             SUBSTR('DB Modeling and Oracle SQL', 4),
             SUBSTR('DB Modeling and Oracle SQL',-14, 8)
       FROM DUAL;

사용예) 회원테이블에서 주민번호를 이용하여 나이를 구하고 나이에 따른 연령대(10대, 30대, 40대...등)를 출력하는 sql작성 Alias는 회원번호, 회원명, 주민번호, 나이, 연령대

 SELECT  MEM_ID AS 회원번호,
                MEM_NAME AS 회원명,
                MEM_REGNO1||'-'||MEM_REGNO2 AS 주민번호,
                -- MEM_REGNO2 첫자리가 1,2면1900년 3,4면 2000년
                CASE WHEN SUBSTR(MEM_REGNO2,1,1) IN('1','2') THEN
                       2021-TO_NUMBER('19'||SUBSTR(MEM_REGNO1,1,2))
                ELSE
                        2021-TO_NUMBER('20'||SUBSTR(MEM_REGNO1,1,2)) 
                END AS 나이, --만나이
              CASE WHEN SUBSTR(MEM_REGNO2,1,1) IN('1','2') THEN
                TRUNC( 2021-TO_NUMBER('19'||SUBSTR(MEM_REGNO1,1,2)),-1)
                ||'대'
                ELSE
                 TRUNC( 2021-TO_NUMBER('20'||SUBSTR(MEM_REGNO1,1,2)),-1)
                 /|'대'
           END AS 연령대
      FROM MEMBER;

7)REPLACE(c1,C2[,C3])

  • 문자나 문자열을 치환하기 위한 함수
  • 주어진 문자열 c1에서 c2를 찿아 c3로 치환
  • c3이 생략되면 c2를 제거
  • (c3없이 c2가 공백이면) 주로 문자열 내부의 공백을 제거할때 사용

사용예)상품테이블의 상품명 중 '대우'를 찾아 '대덕'으로 치환하시오
Alias는 상품번호, 상품명, 거래처코드, 적정재고량이다.

SELECT PROD_ID AS 상품번호,
             PROD_NAME AS 상품명, --
             REPLACE(PROD_NAME, '대우','대덕') AS 상품명,
             PROD_BUYER AS 거래처코드,
             PROD_PROPERSTOCK AS 적정재고량
        FROM PROD
       WHERE PROD_NAME LIKE '%대우%'; 
       --"대우 인재 대우" 여도 "대덕 인재 대덕" 으로 바뀜 
   SELECT REPLACE('APPLE PERSIMON BANNA', 'N', 'y'), 
   -- 모든 N을 y로.
          REPLACE('APPLE PERSIMON BANNA', 'N'), 
          --N을 찾아서 모든 N을 제거. 공백이 들어오는것은 아님.
          REPLACE('APPLE PERSIMON BANNA', ' ')
          -- 공백을 모두 삭제
    FROM DUAL;

  
   

8) INSTR(c1,c2[,m[,n]])

  • 주어진 문자열 c1에서 c2문자열이 처음 나온 위치값(index)을 반환
  • m은 검색 시작위치를 1이외의 다른 값으로 지정할 때 사용
  • n은 출현횟수를 지정하여 검색할때 사용

사용예)

SELECT INSTR('무궁화 꽃이 피었습니다. 무궁화 꽃은...','화') AS COL1,
                 INSTR('무궁화 꽃이 피었습니다. 
                 무궁화 꽃은...','화',4) AS COL2,
                 INSTR('무궁화 꽃이 피었습니다. 
                 무궁화 꽃은...','화',1,2) AS COL3 

--첫번째값에서 찾기시작해서 2번째로 출현한 화의 위치
            FROM DUAL;
profile
삐약..뺙뺙

0개의 댓글