13-15강 ) 문자열, 날짜, CASE문

Reference : SQL 첫걸음

 

 

13강 ) 문자열 연산

 

1. 문자열 결합

문자열 결합 연산자

연산자/함수	연산	데이터베이스
+	문자열 결합	SQL Server
||	문자열 결합	Oracle, DB2, PotgreSQL
CONCAT	문자열 결함	MySQL

• 문자열을 결합하는 연산자는 데이터베이스 제품마다 방언이 있다.
• 문자열 결합은 2개의 열 데이터를 모아 1개의 열로 처리하고 싶은 경우에 사용한다.

 

ex) 문자열형인 unit 열과 INTEGER 형의 quantity열을 CONCAT 함수를 이용해 결합.

SELECT CONCAT(quantity, unit) FROM sample35;

문자열로 결합한 결과는 문자열형이 된다.

 

 

2. SUBSTRING 함수

• 문자의 일부분을 계산해서 반환
• 예) 연월일을 YYYYMMDD 와 같은 형식의 문자열로 데이터로 저장한 경우 따로 추출
  • SUBSTRING('20140125001', 1, 4) → '2014'

 

3. TRIM 함수

• 문자열의 앞뒤로 여분의 스페이스가 있을 경우 제거
• 고정길이 문자열형에 대해 많이 사용
• 인수를 지정함으로써 스페이스 이외의 문자를 제거할 수 있다.
  • TRIM('ABC    ') → 'ABC'

 

 

4. CHARACTER_LENGTH 함수

• 문자열의 길이를 계산해 반환 (문자 단위)
• VARCHAR 형의 길이 계산 가능.
• OBJECT_LENGTH 함수는 문자열의 길이를 바이트 단위로 계산해 반환
• EUC-KR, UTF-8 등의 인코딩 방식에 따라 한 문자가 몇 바이트인지 쓰이는 문자세트마다 다른 것을 알 수 있다.
• 따라서 바이트 단위로 길이를 계산하는 OBJECT_LENGTH 함수 사용시 주의해야한다.

★ 문자열 데이터의 길이는 문자 세트에 따라 다르다!

 

 

 

14강 ) 날짜 연산

날짜, 시간 데이터를 저장하는 방법은 데이터베이스 제품에 따라 다르다.

 

1. SQL에서의 날짜

 

시스템 날짜

표준 SQL에서는 'CURRENT_TIMESTAMP' 라는 함수로 실행했을 때의 기준으로 시간을 표시한다.

SELECT CURRENT_TIMESTAMP;

 

날짜 서식

날짜 데이터는 서식을 지정할 수 있으며, 날짜 서식은 국가별로 다르다.

한국에서는 보통 연월일을 슬래시나 하이픈으로 구분해 표기

• YYYY/MM/DD
• YYYY-MM-DD
• 25 Jan 2014  (미국식)

Oracle의 경우 TO_CHAR 함수를 이용해 문자열 데이터로 출력 가능하다.

 

 

2. 날짜의 덧셈과 뺄셈

날짜시간형 데이터는 기간형 수치 데이터와 덧셈 및 뺄셈을 할 수 있다.

SELECT CURRENT_DATE + INTERVAL 1 DAY;

 

CURRENT_DATE는 시스템 날짜의 날짜만 확인하는 함수다. INTERVAL 1 DAY는 '1일 후'라는 의미의 기간형 상수이다.

 

 

날짜형 간의 뺄셈

두 날짜 사이에 차이가 얼마나 발생하는지 계산할 수 있다.

MySQL 에서는 DATEDIFF('2014-02-28', '2014-01-01') 로 계산할 수 있다.

 

 

 

15강 ) CASE문으로 데이터 변환하기

 

1. CASE문

사용자 정의 함수를 작성하지 않고도 CASE 문으로 처리 가능

CASE WHEN 조건식1 THEN 식1
[WHEN 조건식2 THEN 식2 ...]
[ELSE 식3]
END

• WHEN 에는 참과 거짓을 반환하는 조건식을 기술
• 조건식이 참이 되는 경우에는 THEN 절에 기술한 식 처리.
• WHEN 절의 조건식을 차례로 평가하다가 가장 먼저 조건을 만족한 WHEN ㅓㅈㄹ과 대응하는 THEN 절 식의 처리결과를 CASE문의 결괏값으로 반환.
• ELSE 절은 생략 가능하며 생략했을 경우 'ELSE NULL'로 간주된다.

 

SELECT a, CASE WHEN a IS NULL THEN 0 ELSE a END "a(null=0)" FROM sample37;

결과) CASE로 NULL값 0으로 변환하여 a(null=0) 열 출력

a	a(null=0)
1	1
2	2
NULL	0

 

COALESCE

위의 예제처럼 NULL값을 변환하는 경우는 COALESCE 함수를 구현하면 더 쉽다.

SELECT a, COALESCE(a,0) FROM sample37;

여러개의 인수를 지정할 수 있으며, 주어진 인수 가운데 NULL이 아닌 값에 대해서는 가장 먼저 지정된 인수의 값을 반환한다.

 

 

2. 또 하나의 CASE문

• 문자화하는 것을 '디코드' 라 부르고
• 수치화 하는 것을 '인코드' 라 부른다.

 

디코드를 CASE문으로 처리한 경우 예시

WHEN a=1 THEN '여자'
WHEN a=2 THEN '남자'

 

 

검색CASE : 위에서 설명한 것들

 

단순 CASE : 'CASE 식 WHEN 식 THEN 식 ...' 구문

• 식1의 값이 WHEN의 식2의 값과 동일한지 비교하고, 값이 같다면 식3의 값이 CASE문 전체의 결괏값이 된다.
• 값이 같지 않다면 그 뒤에 기술한 WHEN 절과 비교

성별 문자열을 디코딩하는 예시

(1) 검색 CASE

SELECT a AS "코드",
CASE
	WHEN a=1 THEN '여자'
    WHEN a=2 THEN '남자'
    ELSE '미지정'
ELSE AS "성별" FROM sample37;

 

(2) 단순 CASE

SELECT a AS "코드",
CASE a
	WHEN 1 THEN '여자'
    WHEN 2 THEN '남자'
    ELSE '미지정'
END AS "성별" FROM sample37;

단순 case를 사용한 경우 CASE문에서 비교할 항목인 'a'를 따로 지정하므로 WHEN에는 1,2처럼 비교할 값만 기술한다.

 

 

3. CASE를 사용할 경우 주의 사항

 

ELSE

• ELSE를 생략하면 ELSE NULL이 되는 것에 주의하자.
• ELSE를 생략하면 상정한 것 이외의 데이터가 왔을 때 ULL이 반환되므로, CASE문의 ELSE는 생략하지 않는 편이 좋다.

 

WHEN에 NULL 지정하기

• 단순 CASE문에서는 WHEN 절에 NULL 사용하면 =으로 비교하므로 정상적으로 처리되지 않는다.
• 따라서 NULL 값인지 아닌지를 판정하기 위해서는 IS NULL을 사용해야 한다.
• CASE문 특성상 = 연산자로 비교하는 만큼, NULL 값인지 판정하려면 검색 CASE문을 사용해야 한다.

★ 단순 CASE문으로는 NULL 값을 비교할 수 없다!

 

 

DECODE NVL

• Oracle에서는 DECODE 함수가 내장되어 있으며, CASE문과 같은 용도로 사용할 수 있다.
• NULL 값을 반환하는 Oracle의 NVL함수, SQLServer의 ISNULL 함수 등이 있다.
• 위의 함수들은 특정 데이터베이스에 국한된 함수이므로 NULL값을 변환할 때는 표준 SQL로 규정되어 있는 COALESCE 함수를 사용하는 것이 좋다.