오라클(SQL)문자열 함수(SUBSTR, REPLACE, INSTR, (LENGTH, LENGTHB))

문자열 함수(SUBSTR, REPLACE, INSTR, (LENGTH, LENGTHB))

테이블 다운로드

SUBSTR   
    -원본 문자열에서 정의된 길이만큼의 부분문자열을 추출함
    -결과도 문자열임
    -날짜에는 사용하지마라
    (사용형식)
    SUBSTR(C,N[,LEN])
    .주어진 문자열 C에서 N번째에서 LEN갯수만큼의 문자열을 추출하여 반환
    .N이 0또는 1이면 첫 번째 글자부터 처리
    .N이 음수 이면 윗자리부터 처리
    .LEN이 생략되면 N자리 이후 모든 문자열 반환
    
사용예)장바구니테이블에서 2005년 7월 판매자료를 조회하시오
    ALIAS는 판매일자, 구매자, 상품명, 수량

SELECT TO_DATE(SUBSTR(CART_NO,1,8))AS 판매일자, -- TO_DATE 날자형식으로 변환해준다.
       CART_MEMBER AS 구매자, 
       PROD_NAME AS 상품명, 
       CART_QTY AS 수량
  FROM CART A, PROD B--이름이 다를경우 CART , PROD  생략해도 된다.생략하면 WHERE에서도 생략해야한다.
 WHERE A.CART_PROD=B.PROD_ID--조인조건
   AND SUBSTR(CART_NO,1,6)='200507';

사용예)사원테이블(EMPLOYEES)에서 판매관련 직책(SALES : SA_XXX)을
      부여받은 사원정보를 조회하시오
      ALIAS는 사원번호, 사원명,부서명, 직책코드, 급여이다.

SELECT A.EMPLOYEE_ID AS 사원번호, 
       A.EMP_NAME AS 사원명,
       B.DEPARTMENT_NAME AS 부서명, 
       A.JOB_ID AS 직책코드, 
       A.SALARY AS 급여
  FROM EMPLOYEES A, DEPARTMENTS B
 WHERE A.DEPARTMENT_ID=B.DEPARTMENT_ID
   AND SUBSTR(JOB_ID,1,2)='SA'
 ORDER BY 5 DESC;--SELECT에서의 컬럼번호를 말하는 숫자이다.

 문제1)회원테이블에서 김씨성을 가진 회원정보를 출력하시오
      ALIAS는 회원번호, 회원명, 성별, 마일리지이다.

SELECT MEM_ID AS 회원번호,
       MEM_NAME AS 회원명,
       CASE WHEN SUBSTR(MEM_REGNO2,1,1) IN('1','3')THEN'남자'--=이아닌 THEN으로 사용한다.
       ELSE '여성' END AS 성별,
       MEM_MILEAGE AS 마일리지
  FROM MEMBER
 WHERE SUBSTR(MEM_NAME,1,1)='김';
 --WHERE MEM_NAME LIKE '김%';--%는 (?%) ?로 시작하는 것을 찾아라

REPLACE
    -특정문자열을 찾아 다른 문자열로 대치시킴
    (사용형식)
    REPLACE(C1,C2[C3])
    .주어진 문자열 C1에서 C2문자열을 모두 찾아 C3문자열로 대치
    .C3가 생략되면 찾은 문자열을 제거함(문자열 내부의 공백제거에 사용)

사용예)

SELECT ' ILPOSTINO BOYHOOD',
       TRIM(' ILPOSTINO BOYHOOD '),--문자 양끝에만 지운다.
       REPLACE(' ILPOSTINO, BOYHOOD',' '),--문자에 있는 모든것을 지운다.
       REPLACE(' ILPOSTINO, BOYHOOD',' ','*')
  FROM DUAL;

문제)사원테이블에서 전화번호 출력형식을 XXX-XXX-XXXX으로 출력하시오
    단 입사일이 2005년 이후 입사한 사원을 대상으로 한다.
    ALIAS는 사원번호, 사원명, 전화번호, 입사일, 퇴사일이다.
    비고는 '재직중','퇴사'중 하나를 출력할 것

SELECT EMPLOYEE_ID AS 사원번호,
       EMP_NAME AS 사원명,
       REPLACE(PHONE_NUMBER,'.','-') AS 전화번호,
       CASE WHEN RETIRE_DATE IS NULL THEN '재직'--NULL을 비교할때는NULL을 써야한다.
            ELSE '퇴직' END AS 비고
  FROM EMPLOYEES
 WHERE HIER_DATE >= TO_DATE('20050101');

INSTR
    -문자열에서 특정 문자열의 위치값(INDEX)을 반환
    (사용형식)
    INSTR(C1,C2[,M[,N]])
    .주어진 문자열 C1에서 C2문자열이 처음 출현한 위치 반환
    .M은 검사할 특정위치를 지정할때 사용되는 INDEX
    .N은 반복되어 표현된 문자열 중 반복된 횟수를 측정하여 검색할 때 사용

사용예)

SELECT EMP_NAME,
       INSTR(EMP_NAME,'o'),
       INSTR(EMP_NAME,'o',3),
       INSTR(EMP_NAME,'o',3,2)--3번에서 2번째 O를 찾아라
  FROM EMPLOYEES
 WHERE DEPARTMENT_ID=50;

LENGTH, LENGTHB
    -문자열의 길이나 문자열 내의 문자의 수를 반환
(사용형식)

LENGTH(C),LENGTHB(C)
    .LENGTH(C):C문자열 내의 문자의 수
    .LENGTHB(C):C문자열의 길이(BYTE수)
    .한글 한 글자는 3BYTE로 표현