Oracle(2022.02.11)-문자열 함수('||',CONCAT,LOWER, UPPER, INITCAP,LPAD, RPAD,LTRIM, RTRIM,TRIM,SUBSTR)

함수(FUNCTION)

  -미리 작성되어 컴파일 한 후 저장된 프로그램 모듈
  -사용자의 호출에 의하여 실행되고 결과를 호출문의 위치에 반환
  -단일행함수
    . 하나의 결과를 반환하는 함수
    . 문자열,숫자,날짜,형변환,NULL처리함수 등이 있음
    .함수는 중첩(Nested) 사용이 가능
    .SELECT절, WHERE절, ORDER BY 절 등에서 사용
  -복수행 함수
    . 그룹화된 자료에 대하여 그룹별로 처리하여 결과 반환
    .GROUP BY 절 사용
    .SUM, AVG, COUNT, MAX, MIN  --중복해서 사용할 수 없음 EX)SUM(AVG)
 
  1.문자열 함수
    1)'||'
    .문자열 연산자
    .두 문자열을 결합하여 새로운 문자열을 반환
    .JAVA의 문자열 연산자 '+'와 동일 기능
   

---

    2)CONCAT
    .문자열 결합 함수
    .'||'와 같은 기능제공
    (사용형식)
    CONCAT(c1,c2)
    - 주어진 문자열과 c1과 c2를 결합하여 새로운 문자열 반환
        
**문제를 풀기위해 먼저 MEMBER테이블의 자료를 다음과 같이 수정하시오

 회원번호: 'h001'
  주민번호1: '740728'    =>'020728'
  주민번호2: '1455822'   =>'3455822'
  생년원일: 1967/03/01   =>2002/03/01
  UPDATE MEMBER
    SET MEM_REGNO1='020728',
        MEM_REGNO2='3455822',
        MEM_BIR=TO_DATE('20020301')
    WHERE MEM_ID='h001';
  
  회원번호: 'm001'
  주민번호1: '750315'    => '000315'
  주민번호2: '2555555'   =>'4555622'
  생년원일: 1975/03/15   =>2000/03/15
  UPDATE MEMBER
    SET MEM_REGNO1='000315',
        MEM_REGNO2='4555622',
        MEM_BIR=TO_DATE('20020315')
    WHERE MEM_ID='m001';
  
  회원번호: 'r001'
  주민번호1: '770120'    => '030120'
  주민번호2: '2382532'   =>'4382532'
  생년원일: 1976/11/26   =>2003/11/26
   UPDATE MEMBER
    SET MEM_REGNO1='030120',
        MEM_REGNO2='4382532',
        MEM_BIR=TO_DATE('20031126')
    WHERE MEM_ID='r001';
  COMMIT;

사용예)회원테이블에서 1995년 이후 출생된 회원정보를 조회하시오.
      Alias는 회원번호, 회원명, 주민등록번호,직업이다.
      단, 주민등록번호는 XXXXXX-XXXXXXX형식으로 출력하시오
  SELECT MEM_ID AS 회원번호, 
    MEM_NAME AS 회원명, 
    MEM_REGNO1||'-'|| MEM_REGNO2 AS 주민등록번호,
    MEM_JOB AS 직업
   FROM MEMBER
 WHERE EXTRACT(YEAR FROM MEM_BIR)>=1995;

EXTRACT:입력된 날짜에서 추출하고자 하는 연도, 월, 시간,분 초등을 반환하는 형식

구문형식

EXTRACT(보고자하는 정보 FROM 입력날짜)

보고자하는 정보: YEAR, MONTH, DAY, HOUR, MINUTE, SEOCND

사용예) CONCAT 함수 사용
 SELECT MEM_ID AS 회원번호, 
    MEM_NAME AS 회원명, 
    CONCAT(CONCAT(MEM_REGNO1,'-'),MEM_REGNO2) AS 주민등록번호,
    MEM_JOB AS 직업
   FROM MEMBER
 WHERE EXTRACT(YEAR FROM MEM_BIR)>=1995;

---

  3)LOWER, UPPER, INITCAP
  -LOWER(c1): c1에 포함된 대문자를 소문자로 변환
  -UPPER(c1): c1에 포함된 소문자를 대문자로 변환
  -INITCAP(c1): c1에 포함된 단어의 시작글자만 대문자로 변환

                    ex) soon TOFU  결과: Soon Tofu

사용예) 상품테이블에서 분류코드 'p202'에 속한 상품정보를 조회하시오
         Alias는 상품코드, 상품명,분류코드,거래처코드
      SELECT PROD_ID AS 상품코드, 
             PROD_NAME AS 상품명,
             PROD_LGU AS 분류코드,
             PROD_BUYER AS 거래처코드
     FROM PROD
     WHERE LOWER(PROD_LGU)='p202';

                                   :

                            데이터 생략

사용예)2005년 회원 'A001'회원의 구매정보를 조회하시오
         Alias는 회원명,상품명,구매수량,구매금액합계
SELECT (SELECT MEM_NAME
           FROM MEMBER
          WHERE UPPER(MEM_ID) = 'A001') AS 회원명,
        C.PNAME AS 상품명,
        C.SQTY AS 구매수량,
        C.SAMT AS 구매금액합계
      FROM(SELECT A.CART_PROD AS PID,
                  B.PROD_NAME AS PNAME,
                  SUM(A.CART_QTY) AS SQTY,
                  SUM(A.CART_QTY*B.PROD_PRICE) AS SAMT
          FROM CART A, PROD B
         WHERE A.CART_NO LIKE '2005%'
         AND UPPER(A.CART_MEMBER)='A001'
         AND A.CART_PROD = B.PROD_ID
          GROUP BY A.CART_PROD, B.PROD_NAME) C;

 

---

 4)LPAD, RPAD
  -주어진 크기의 공간 왼쪽(오른쪽)부터 자료를 저장한 후 남는 기억공간 오른쪽(왼쪽)에 
   정의된 특정문자열을 삽입하여 출력
  (사용형식)
  LPAD(c1, n[,c2]), RPAD(c1, n[,c2])

   .LPAD(C1,n [,c2]) :주어진 문자열 c1을 n 크기의 기억공간에 오른쪽부터 저장한 후
    남는 왼쪽에 c2문자열을 삽입
    c2가 생략되면 공백이 삽입
    수표보호문자등으로 이용되었음
    
    .RPAD(C1,n [,c2]) :주어진 문자열 c1을 n 크기의 기억공간에 왼쪽부터 저장한 후
    남는 오른쪽에 c2문자열을 삽입
    c2가 생략되면 공백이 삽입

사용예)상품테이블에서 상품의 판매가격을 다음 규칙대로 출력하시오.
       출력은 상품코드,상품명,판매가이며 판매가는 9자리에 출력하되 남는 왼쪽공간은 '*'
       출력할것 
       
       SELECT PROD_ID AS 상품코드,
              RPAD(PROD_NAME,22) AS 상품명,
              LPAD(TO_CHAR(PROD_PRICE),9,'*') AS 판매가
         FROM PROD;

   

                              :

                        데이터생략

 SELECT RPAD(PROD_ID,15)||RPAD(PROD_NAME,30)||LPAD(TO_CHAR(PROD_PRICE),9,'*')
         FROM PROD;
          -->RPAD(PROD_ID,15) 15숫자 뒤에 생략되었므로 5칸공백 문자열(왼->오정렬),숫자(오른쪽정렬)

                               :

                        데이터생략

---

 5)LTRIM, RTRIM 사용빈도수:**
   -왼쪽(오른쪽)에 존재하는 특정 문자열(또는 공백)을 제거하기 위함 함수
   -단어 내부의 공백은 제거하지 못함
   (사용형식)
    LTRIM(c1[,c2]), RTRIM(c1,[,c2])
    .LTRIM(c1[,c2]: 주어진 문자열 c1에서 왼쪽 시작이 c2로 시작되면 c2부분을 제거
     c2 가 생략되면 왼쪽 공백을 제거 
    .RTRIM(c1[,c2]: 주어진 문자열 c1에서 오른쪽 시작이 c2로 시작되면 c2부분을 제거
     c2 가 생략되면 오른쪽 공백을 제거 

 

** ALTER TABLE MEMBER MODIFY(MEM_NAME CHAR(80));
뒤에가 다 공백으로..

ALTER 하기 전 원래 데이터

ALTER하고 난후 NAME은 CHAR형으로 바뀜

 SELECT * 
   FROM MEMBER
  WHERE MEM_NAME='김은대';   -->원칙적으로는 작동안됨 WHY? 뒤에 공백이 있기 때문에 
  
   SELECT * 
   FROM MEMBER
  WHERE RTRIM(MEM_NAME)='김은대';

**ALTER TABLE MEMBER MODIFY(MEM_NAME VARCHAR2(80));  

-->만약 COMMIT을 했다가 고치려고했다 근데 다시                                                                                           안돌아감 왜? 이미 빈공간도 데이터로 취급을 했기                                                                                         때문이다. 그래서 UPDATE문을 써준다.
**UPDATE MEMBER 
        SET MEM_NAME= RTRIM(MEM_NAME);
    SELECT * FROM MEMBER;

    COMMIT;

SELECT LTRIM('APAPALE PERSIMMON','A'),
          LTRIM('APAPALE PERSIMMON','APP'), -->AP AP A삭제
          LTRIM('APAPALE PERSIMMON','PA'),  -->검토 정확한자료는 아님
          RTRIM('APAPMON PERSIMMON','MON'), -->MOM M삭제
          RTRIM('APAPMON PERSIMMON','O')    -->중간에 있어서 O가 삭제안됨
        FROM DUAL;   
-->DUAL:시스템이 제공해주는 빈공간 테이블명 즉 가상 테이블이며 테이블이 꼭 필요하지만 없을                                         경우 사용

---

6)TRIM 사용빈도수:***
    -단어 왼쪽과 오른쪽에 존재하는 모든 공백 제거
    -단어 내부 공백은 제거하지 못함
    (사용형식)
    TRIM(c1)
      

사용예)오늘이 2005년 7월 28일 경우 장바구니번호를 생성하시오. 
SELECT *FROM CART
     WHERE CART_NO LIKE '20050728%' 
     ORDER BY 2;

SELECT MAX(TO_NUMBER(SUBSTR(CART_NO,9)))+1  -->문자열 숫자로 그리고 큰것을 출력
       FROM CART
    WHERE SUBSTR(CART_NO,1,8)= TO_CHAR(SYSDATE,'YYYYMMDD');

SELECT TO_CHAR(SYSDATE,'YYYYMMDD')||TRIM(TO_CHAR(MAX(TO_NUMBER(SUBSTR(CART_NO,9)))+1 ,'00000')) 
     -->문자열 숫자로 그리고 큰것을 출력 9자리 이후 숫자로 바꿔서 1을해 다음카트번호가 되어야하므로 가운데 빈칸이 발생: 어쩔 수 없는 시스템오류
      FROM CART     
    WHERE SUBSTR(CART_NO,1,8)= TO_CHAR(SYSDATE,'YYYYMMDD');

TRIM하기 전

 TRIM한 후

  -->빈칸을 없애는 편법 CART_NO를 숫자로 바꿔서 제일 큰것을 찾고+1을 해줌
 SELECT MAX(CART_NO)+1
   FROM CART
   WHERE SUBSTR(CART_NO,1,8)= TO_CHAR(SYSDATE,'YYYYMMDD');

---

   7)SUBSTR 사용빈도:*****
   -부분문자열을 추출(결과의 데이터 타입도 문자열)
   -가장 널리 사용되는 문자열 함수
   (사용형식)
   SUBSTR(c1,m[,n])
   .주어진 문자열 c1에서 m번째 문자부터 n개의 문자를 추출하여 반환
   .n이 생략되면 m번째 부터 나머지 모든 문자열 추출
   .m이 0이면 1로 간주
   .m이 음수이면 오른쪽부터 처리

사용예)
   SELECT SUBSTR('대한민국은 민주 공화국이다',2,5),
          SUBSTR('대한민국은 민주 공화국이다',2),
          SUBSTR('대한민국은 민주 공화국이다',-2,5) -->'이다'에서부터 5글자 뒤에는 없으므로 '이다'만 나온다.
    FROM DUAL;

사용예) 회원테이블에서 회원들의 성씨를 조회하시오(중복하지 말것)
     

  -->빈칸을 없애는 편법 CART_NO를 숫자로 바꿔서 제일 큰것을 찾고+1을 해줌
 SELECT MAX(CART_NO)+1
   FROM CART
   WHERE SUBSTR(CART_NO,1,8)= TO_CHAR(SYSDATE,'YYYYMMDD');

사용예) 장바구니테이블에서 2005년 6월 판매현황을 조회하시오
           Alias는 날짜, 제품코드, 판매수량

SELECT TO_DATE(SUBSTR(CART_NO,1,8)) AS 날짜,
          CART_PROD AS 제품코드, 
          CART_QTY AS 판매수량
         FROM CART
         WHERE SUBSTR(CART_NO,1,6)='200506'
         ORDER BY 1;