SQL 2일차

문자처리함수

INSTR
        문자열로부터 특정 문자의 시작위치를 찾아서 반환
        
        INSTR(컬럼 | '문자열', '찾고자하는 문자', ['찾을 위치의 기작값, 순번'])
            -> 결과는 NUMBER

SELECT INSTR('AAABBCAAABBC', 'B') FROM DUAL;

찾을 위치의 시작 값: 1, 순번 : 1 -> 기본값

 

SELECT EMAIL, INSTR(EMAIL, '@') AS "@위치"
FROM EMPLOYEE;

(컬럼과 문자 응용)

 

    SUBSTR
        문자열에서 특정 문자열을 추출해서 반환
        
        [표현법]
        SUBSTR(컬럼 | '문자열', 추출 시작위치, [추출문자 갯수])

SELECT SUBSTR('SHOWMETHEMONEY', 7 ) FROM DUAL;

7번위치부터 끝까지

 

SELECT SUBSTR('SHOWMETHEMONEY', 1, 6 ) FROM DUAL;

1번위치부터 6개 추출

 

SELECT EMP_NAME, EMP_NO
FROM EMPLOYEE
WHERE SUBSTR(EMP_NO, 8, 1) IN ('2', '4');

사원들 중 여자 사원들만 EMP_NAME, EMP_NO을 조회(주민번호에서 2와 4가 성별 '여'를 이용)

 

함수는 중첩 사용할 수 있다.

SELECT EMP_NAME, EMAIL, SUBSTR(EMAIL, 1, INSTR(EMAIL, '@') -1)
FROM EMPLOYEE;

이메일의 아이디 부분만 추출

 

CONCAT
        문자열 두개 전달받아서 하나로 함친 후 반환
        
        [표현법]
        CONCAT(STR1, STR2)

SELECT CONCAT('가나다', 'ABC') FROM DUAL;

SELECT '가나다' || 'ABC' FROM DUAL;

둘 다 동일한 SQL문

 

REPLACE
        특정 문자열에서 특정 부분을 다른 부분으로 교체
        
        [표현법]
            REPLACE(문자열, 찾을 문자열, 치환할 문자열)

SELECT EMAIL, REPLACE(EMAIL, 'C##SERVER', 'kh') FROM EMPLOYEE;

 

날짜 처리함수

MONTHS_BETWEEN : 두 날짜 사이의 개월 수를 반환

SELECT EMP_NAME, HIRE_DATE, TRIM(SYSDATE - HIRE_DATE), CEIL(MONTHS_BETWEEN(SYSDATE, HIRE_DATE))
FROM EMPLOYEE;

사원들의 사원명, 입사일, 근무일수, 근무 개월 수

 

ADD_MONTHS : 특정 날짜에 개월수를 더한 값을 반환

SELECT ADD_MONTHS(SYSDATE, 7) FROM DUAL;

현재 날짜에 7개월을 더함

SELECT EMP_NAME, HIRE_DATE, ADD_MONTHS(HIRE_DATE, 3) FROM EMPLOYEE;

사원테이블에서 사원명, 입사일, 수습기간 종료일(입사일로부터 3개월 뒤) 조회

 

NEXT_DAY(DATE, 요일(문자 | 숫자)) : 특정 날짜 이후 가장 가까운 요일의 날짜를 반환

오라클 기준 1: 일요일 ~ 7: 토요일

SELECT NEXT_DAY(SYSDATE, 7) FROM DUAL;

 

LAST_DAY(DATE) : 해당 월의 마지막 날짜를 구해서 반환

SELECT LAST_DAY(SYSDATE) FROM DUAL;

 

EXTRACT : 특정 날짜로부터 년|월|일 값만 추출해서 반환
    
        [표현법]
        EXTRACT(YEAR FROM DATE) : 연도만 추출
        EXTRACT(MONTH FROM DATE) : 월만 추출
        EXTRACT(DAY FROM DATE) : 일만 추출

SELECT EMP_NAME,
        EXTRACT(YEAR FROM HIRE_DATE) AS 입사년도,
        EXTRACT(MONTH FROM HIRE_DATE) AS 입사월,
        EXTRACT(DAY FROM HIRE_DATE) AS 입사일
FROM EMPLOYEE;

사원의 사원명, 입사년도, 입사월, 입사일 조회

 

형변환 함수

TO_CHAR : 숫자타입 또는 날짜타입의 값을 문자타입으로 변환시켜주는 함수
        
        [표현법]
            TO_CHAR(숫자 | 문자, [포멧])

숫자 -> 문자

SELECT TO_CHAR(1234) FROM DUAL;

SELECT TO_CHAR(12, '99999') FROM DUAL;

SELECT TO_CHAR(12, '00000') FROM DUAL

두번째 문장은 9의 자리수 만큼 공간 확보

세번째 문장은 0의 자리수 만큼 공간 확보, 빈칸을 0으로 채움

SELECT TO_CHAR(1200000000, 'L9,999,999,999') FROM DUAL;

현재 설정된 나라의 로컬 화폐단위를 나타냄

 

날짜 -> 문자

SELECT TO_CHAR(SYSDATE, 'YYYY"년" MM"월" DD"일" DY HH24"시" MI"분" SS" 초"') FROM DUAL;

 

년도와 관련된 포맷

SELECT TO_CHAR(SYSDATE, 'YYYY'),
        TO_CHAR(SYSDATE, 'RRRR'),
        TO_CHAR(SYSDATE, 'YY'),
        TO_CHAR(SYSDATE, 'YEAR')
FROM DUAL;

SELECT TO_DATE('2025', 'YYYY'), --> 많이 사용
    TO_DATE('2025', 'RRRR'),
    TO_DATE('60', 'YY'),    -- YY는 항상 입력 그대로 해석 -> 현재 년도를 반영
    TO_DATE('60', 'RR')     -- 2자리 입력시 현재 세기를 기준으로 자동보정
FROM DUAL;

 

월과 관련된 포맷

SELECT TO_CHAR(SYSDATE, 'MM'), --> 많이 사용
    TO_CHAR(SYSDATE, 'MON'),
    TO_CHAR(SYSDATE, 'MONTH')
FROM DUAL;

 

일과 관련된 포맷

SELECT TO_CHAR(SYSDATE, 'DD'), --오늘 일자
    TO_CHAR(SYSDATE, 'DDD'), --오늘이 이번 년도의 몇번째 일수
    TO_CHAR(SYSDATE, 'D') --요일
FROM DUAL;

 

요일을 나타내는 포맷

SELECT TO_CHAR(SYSDATE, 'D'), --> 많이 사용
    TO_CHAR(SYSDATE, 'DAY'),
    TO_CHAR(SYSDATE, 'DY')
FROM DUAL;

 

TO_DATE : 숫자타입 또는 문자타입을 날짜타입으로 변경하는 함수
    [표현법]
        TO_DATE(숫자 | 문자, [포맷]) -> DATE

SELECT TO_DATE(20100101) FROM DUAL;

★작성시 주의사항: 0으로 시작하는 숫자는 오류가 발생하기 때문에, ' '을 사용하여 문자 취급해야 함

 

TO_NUMBER : 문자타입의 데이터를 숫자타입으로 변환해서 반환.
    
        [표현법]
        TO_NUMBER(문자, [포맷])

SELECT TO_NUMBER('03213432432') FROM DUAL;

첫번째 숫자인 0이 빠져 3213432432로 출력

 

SQL은 자동 변환 가능

SELECT '1000' + '5000' FROM DUAL; --문자열 -> 숫자로 자동형변환이 진행된다.

 

<선택함수>
        DECODE(비교하고자 하는 대상, 비교값1, 결과값1, 비교값2, 결과값2 ...)

SELECT EMP_ID, EMP_NAME, EMP_NO,
    DECODE(SUBSTR(EMP_NO, 8, 1), '1', '남', '2', '여', '3', '남', '4', '여', '잘못입력') AS 성별
FROM EMPLOYEE;

성별 구별 SQL문

 

SELECT EMP_NAME, JOB_CODE, SALARY AS 인상전,
        DECODE(JOB_CODE, 
                'J7', SALARY * 1.1, 
                'J6', SALARY * 1.15,
                'J5', SALARY * 1.2,
                SALARY * 1.05) AS 인상후
FROM EMPLOYEE;

직업의 이름, 기존 급여, 인상된 급여 조회(각 직급별로 차등인상)

 

CASE문
        SQL에서 조건에 따라 값을 분기하고 싶을 때 사용하는 구문
        
        [표현법]
        CASE 
            WHEN 조건1 THEN 결과1
            WHEN 조건2 THEN 결과2
            ...
            ELSE 기본결과
        END

SELECT EMP_NAME, SALARY,
        CASE 
            WHEN SALARY >= 5000000 THEN '시니어'
            WHEN SALARY >= 3500000 THEN '미들'
            ELSE '주니어'
        END AS 급여
FROM EMPLOYEE;

IF문가 같은 구문