[SQL] 변환함수, NULL 관련함수, CASE

(1) 변환 함수

1. 명시적 형변환과 암시적 형변환

-- 데이터베이스에서 데이터 유형에 대한 형변환을 할 수 있는 방법은 두 가지가 있다.
-- 명시적 형변환 : 변환 함수를 사용하여 데이터 유형을 명시적으로 나타냄
-- 암시적 형변환 : 데이터베이스가 내부적으로 알아서 데이터 유형을 변환함
-- 예를 들어 조건절에서 VARCHAR 유형의 BIRTHDAY 컬럼을 숫자와 비교할 경우
-- 데이터베이스는 오류를 뱉지 않고 내부적으로 BIRRTHDAY 컬럼을 NUMBER형으로 변환하게 된다.

2. 명시적 형변환에 쓰이는 함수

-- SQL SERVER의 경우 CONVERT나 CAST 함수를 사용할 수 있다.

ㄱ. TO_NUMBER(문자열)

-- 문자열을 숫자형으로 변환해주는 함수

 

SELECT TO_NUMBER('1234') FROM DUAL;
SELECT TO_NUMBER('ABC') FROM DUAL; -- 오류발생

ㄴ. TO_CHAR(수 OR 날짜, [포맷]) *[]은 옵션

-- 수나 날짜형의 데이터를 포맷 형식의 문자형으로 변환해주는 함수

 

SELECT TO_CHAR(1234) FROM DUAL;
SELECT TO_CHAR(SYSDATE, 'YYYYMMDD HH24MISS') FROM DUAL;

ㄷ. TO_DATE(문자열, 포맷)

-- 포맷 형식의 문자형의 데이터를 날짜형으로 변환해주는 함수이다.

 

SELECT TO_DATE('20240628', 'YYYYMMDD') FROM DUAL;

(2) NULL 관련 함수

1. NVL(인수1, 인수2)

-- 인수1의 값이 NULL일 경우 인수2를 반환하고 NULL이 아닐 경우 인수1을 반환해주는 함수
-- SQL SERVER의 경우 ISNULL(인수1, 인수2)

 

SELECT * FROM TB_SAMPLE7;

SELECT MEMBER_NO
       ,NVL(REVIEW_SCORE, 0) AS REVIEW_SCORE
FROM TB_SAMPLE7;

 

 

2. NULLIF(인수1, 인수2)

-- 인수1과 인수2가 같으면 NULL을 반환하고 같지 않으면 인수1을 반환해주는 함수
-- REVIEW_SCORE(컬럼) 데이터가 0일 경우 NULL을 반환하고, 0이 아닐경우 REVIEW_SCORE 값을 반환

 

SELECT * FROM TB_SAMPLE7;

SELECT MEMBER_NO
      ,NULLIF(REVIEW_SCORE, 0) AS REVIEW_SCORE
      ,REVIEW
FROM TB_SAMPLE7
WHERE PRODUCT_ID = '100001';

 

3. COALESCE(인수1, 인수2, 인수3 ...)

-- NULL이 아닌 최초의 인수를 반환해주는 함수이다.

 

SELECT * FROM TB_SAMPLE8;
SELECT NAME
       ,COALESCE(PHONE, EMAIL, FAX) AS CONTACT
FROM TB_SAMPLE8;

 

 

4. NVL2(인수1, 인수2, 인수3)

-- 인수1이 NULL이 아닌 경우 인수2를 반환하고 NULL인 경우 인수3을 반환하는 함수

 

SELECT * FROM TB_SAMPLE7;
SELECT MEMBER_NO
      ,NVL2(REVIEW, '리뷰있음', '리뷰없음') AS REVIEW_CHECK
FROM TB_SAMPLE7;

(3) CASE

 

-- CASE는 함수와 성격이 같기는 하지만 표현 방식이 함수라기보다는 구문에 가깝다.
-- 문장으로는 '~이면 ~이고, ~이면 ~이다' 식으로 표현되는 구문
-- 필요에 따라 각 CASE를 여러 개로 늘릴 수도 있다.
-- 같은 기능을 하는 함수로는 ORACLE의 DECODE함수가 있다.
-- 다음 구문은 모든 같은 결과값을 반환한다. *[]은 옵션

 

SELECT * FROM TB_SAMPLE9;

 

-- 아래 SQL 코드는 모두 같은 결과를 나타냅니다.

SELECT SUBWAY_LINE,
        CASE WHEN SUBWAY_LINE = 1 THEN 'BLUE'
             WHEN SUBWAY_LINE = 2 THEN 'GREEN'
             WHEN SUBWAY_LINE = 3 THEN 'ORANGE'
             ELSE 'GRAY'
        END AS LINE_COLOR
FROM TB_SAMPLE9;

SELECT SUBWAY_LINE,
        CASE SUBWAY_LINE
            WHEN 1 THEN 'BLUE'
            WHEN 2 THEN 'GREEN'
            WHEN 3 THEN 'ORANGE'
            ELSE 'GRAY'
        END AS LINE_COLOR
FROM TB_SAMPLE9;

SELECT SUBWAY_LINE,
        DECODE(SUBWAY_LINE, 1, 'BLUE', 2, 'GREEN', 3, 'ORANGE', 'GRAY') AS LINE_COLOR
FROM TB_SAMPLE9;

-- TIP) CASE 문에서 ELSE 뒤의 값이 DEFAULT 값이 되고 별도의 ELSE가 없을 경우 NULL이 DEFAULT가 된다.

 

연습문제) 다음 SQL의 결과는?

 

SELECT 
    CASE WHEN COL1 = 'C' THEN SUBSTR(COL2, 2, 1)
         WHEN COL1 = '가' THEN 'C'
         WHEN COL1 = '1' THEN '10'
         ELSE 'B'
    END AS RESULT
FROM TB_SAMPLE10;