2-1. SQL기본) 03 함수

(1) 문자 함수
1. CHR (ASCII 코드)
ASCII 코드는 총 128개 문자를 숫자로 표현할 수 있도록 정의한 코드
CHR 함수는 ASCII 코드를 인수로 입력했을 때 매핑되는 문자가 무엇인지 알려주는 코드
ex) CHR(65) → A

2. LOWER(문자열)
문자열을 소문자로 변환해주는 함수
ex) LOWER(‘JENNIE’) → jennie

3. UPPER(문자열)
문자열을 대문자로 변환해주는 함수
ex) UPPER(‘jennie’) → JENNIE

4. LTRIM(문자열, [특정문자])  *[]는 옵션
특정 문자를 따로 명시해주지 않으면, 문자열의 왼쪽 공백 제거
명시한 경우, 문자열을 왼쪽부터 한글자씩 특정 문자와 비교하여 특정 문자에 토함된 경우 제거 아니면 멈춤
ex) LTRIM(‘        JENNIE’) → JENNIE
ex) LTRIM(‘블랙핑크’, ‘블랙‘) → 핑크

5. RTRIM(문자열, [특정문자])  *[]는 옵션
특정 문자를 따로 명시해주지 않으면, 문자열의 오른쪽 공백 제거
명시한 경우, 문자열을 오른쪽부터 한글자씩 특정 문자와 비교하여 특정문자에 포함된 경우 제거 아니면 멈춤
ex) RTRIM(‘JENNIE        ’) → JENNIE
ex) RTRIM(‘블랙핑크’, ‘핑크‘) → 블랙

 

6. TRIM([위치], [특정문자], [FROM], 문자열)  *[]는 옵션
옵션이 하나도 없을 경우, 문자열의 왼쪽과 오른쪽 공백 제거
그렇지 않은 경우, 문자열을 위치 (LEADING/TRAILING/BOTH)로 지정된 곳부터 한글자씩 특정 문자와 비교하여 같으면 제거, 아니면 멈춤
특정 문자는 LTRIM, RTRIM과 다르게 한글자만 지정 가능
ex) TRIM(‘       JENNIE        ’) → JENNIE
ex) TRIM(LEADING  ‘블‘ FROM ‘블랙핑크’) → 랙핑크
ex) TRIM(TRAILING ‘크‘ FROM ‘블랙핑크’) → 블랙핑

7. SUBSTR(문자열, 시작점, [길이])  *[]는 옵션
문자열의 원하는 부분만 잘라서 반환해주는 함수
길이를 명시하지 않았을 경우, 문자열의 시작점부터 문자열의 끝까지 반환
ex) SUBSTR(‘블랙핑크제니’, 3 2) → 핑크
ex) SUBSTR(‘블랙핑크제니’, 3 4) → 핑크제니

8. LENGTH(문자열)
문자열의 길이 반환 함수
ex) LENGTH(‘JENNIE’) → 6

9. REPLACE(문자열, 변경 전 문자열 , [변경 후 문자열])  *[]는 옵션
문자열에서 변경 전 문자열을 찾아 변경 후 문자열로 바꾸어주는 함수
변경 후 문자열을 명시해주지 않으면 문자열에서 변경 전 문자열을 제거
ex) REPLACE(‘블랙핑크제니‘, ‘제니‘, ‘지수’) → 블랙핑크지수
ex) REPLACE(‘블랙핑크제니’, ‘제니‘) → 블랙핑크

10. LPAD(문자열, 길이, 문자)
문자열이 설정한 길이가 될 떄까지 왼쪽을 특정 문자로 채우는 함수
ex) LPAD(‘JENNIE’, 10, ‘V’) → VVVVJENNIE

---

(2) 숫자 함수
1. ABS(수)
수의 절대값을 반환하는 함수
ex) ABS(-1) → 1
ex) ABS(2) → 2

 

2. SIGN(수)
수의 부호를 반환하는 함수
ex) SIGN(-7) → -1
ex) SIGN(7) → 1

3. ROUND(수, [자릿수])  *[]는 옵션
수를 지정된 소수점 자릿수까지 반올림하여 반환하는 함수
자릿수 명시하지 않은 경우, 기본값은 0이며 반올림한 정수로 반환
자릿수가 음수인 경우, 지정된 정수부를 반올림하여 반환
ex) ROUND(163.76, 1) → 163.8
ex) ROUND(163.76, -2) → 200

4. TRUNC(수, [자릿수])  * []는 옵션
수를 지정된 소수점 자릿수까지 버림하여 반환해주는 함수
자릿수를 명시하지 않았을 경우, 기본값을 0이며 버림된 정수로 반환
자릿수가 음수인 경우, 지정된 정수부에서 버림하여 반환
ex) TRUNC(54.29, 1) → 54.2
ex) TRUNC(54.29, -1) → 50

5. CEIL(수)
소수점 이하의 수를 올림한 정수를 반환하는 함수
ex) CEIL(72.86) → 73
ex) CEIL(-33.4) → -33

6. FLOOR(수)
소수점 이하의 수를 버림한 정수를 반환하는 함수
ex) FLOOR(22.3) → 22
ex) FLOOR(-22.3) → -23

7. MOD(수1, 수2)
수1을 수2로 나눈 나머지를 반환하는 함수
단, 수2 = 0인 경우, 수1 반환
ex) MOD(15, 7) → 1
ex) MOD(15, -4) → 3
MOD 함수의 두 인자가 모두 음수이면, 나머지도 음수값 도출

---

(3) 날짜 함수
1. SYSDATE
현재의 년, 월, 일, 시, 분, 초 반환 함수
ex) SYSDATE → 2021-09-21 22:08:08

2. EXTRACT(특정 단위 FROM 날짜 데이터)
날짜 데이터에서 특정 단위 (YEAR, MONTH, DAY, HOUR, MINUTE, SECOND)만 출력하여 반환
ex) EXTRACT(YEAR FROM SYSDATE) → 2021
ex) EXTRACT(MONTH FROM SYSDATE) → 9

3. ADD_MONTHS(날짜 데이터, 특정 개월 수)
날짜 데이터에서 특정 개월수를 더한 날짜를 반환해주는 함수
날짜의 이전, 다음달에 기준 날짜의 일자가 존재하지 않으면, 해당 월의 마지막 일자가 반환
ex) ADD_MONTHS(TO_DATE(‘2021-12-31’, ‘YYYY-MM-DD’), -1) → 2021-11-30
ex) ADD_MONTHS(TO_DATE(‘2021-12-31’, ‘YYYY-MM-DD’), 1) → 2022-01-31

---

(4) 변환 함수
1. 명시적 형변환과 암시적 형변환
- 명시적 형변환
변환함수를 사용하여 데이터 유형 변환을 명시적으로 나타냄
- 암시적 형변환
데이터베이스가 내부적으로 알아서 데이터 유형을 변환함

2. 명시적 형변환에 쓰이는 함수
SQL Server(MSSQL)의 경우 CONVERT, CAST 함수 사용 가능

ㄱ. TO_NUMBER(문자열)
문자열을 숫자형으로 변환해주는 함수
ex) TO_NUMBER(‘1234’) → 1234
ex) TO_NUMBER(‘abcd’) → Error

ㄴ. TO_CHAR(수 or 날짜, [포맷])  *[]는 옵션
수나 날짜형의 데이터를 포맷 형식의 문자형으로 변환해주는 함수
ex) TO_CHAR(1234) → ‘1234’
ex) 현재 2021년 9월 21일 22시 33분 21초일 경우 → TO_CHAR(SYSDATE, ‘YYYYMMDDHH24MISS’) 

ㄷ. TO_DATE(문자열, 포맷)
포맷 형식의 문자형의 데이터를 날짜형으로 변환해주는 함수
- YYYY: 년
- MM: 월
- DD: 일
- HH: 시(12)
- HH24: 시(24)
- MI: 분
- SS: 초
ex) TO_DATE(‘20210602’, ‘YYYYMMDD’) → 2021-06-02

---

(5) NULL 관련 함수
1. NVL(인수1, 인수2)
인수1의 값이 NULL인 경우, 인수2 반환
인수1의 값이 NULL이 아닌 경우, 인수1 반환
SQL Server(MSSQL)의 경우 ISNULL(인수1, 인수2)
ex) NVL(REVIEW_SCORE, 0)

 

2. NULLIF(인수1, 인수2)
인수1과 인수2가 같으면 NULL반환
같지 않으면 인수1 반환
ex) NULLIF(REVIEW_SCORE, 0)
REVIEW_SCORE(칼럼) 데이터가 0일 경우 NULL반환
0이 아닐 경우 REVIEW_SCORE 값을 반환

3. COALESCE(이수1, 인수2, 인수3, ...)
NULL이 아닌 최초의 인수 반환 함수
ex) COALESCE(PHONE, EMAIL, FAX)

4. NVL2(인수1, 인수2, 인수3)
인수1이 NULL이 아닌 경우, 인수2 반환
NULL인 경우 인수 3 반환
ex) NVL2(REVIEW_SCORE, ‘리뷰 있음‘, ‘리뷰 없음‘)
REVIEW_SCORE(칼럼) 데이터가 NULL이 아닐 경우 ‘리뷰 있음‘ 반환
NULL일 경우, ‘리뷰 없음‘ 반환

---

(6) CASE
‘~이면 ~이고, ~이면 ~이다’
필요에 따라 여러 개로 늘릴 수 있다
같은 기능을 하는 함수로는 Oracle의 DECODE 함수가 있음
ex) 다음 구문은 모두 같은 결과 값을 반환  *[]는 옵션
1. CASE WHEN SUBWAY_LINE = ‘1’ THEN ‘BLUE’
          WHEN SUBWAY_LINE = ‘2’ THEN ‘GREEN’
          WHEN SUBWAY_LINE = ‘3’ THEN ‘ORANGE’
          [ELSE ‘GRAY’]
   END

 

2. CASE SUBWAY_LINE 
          WHEN ‘1’ THEN ‘BLUE’
          WHEN ‘2’ THEN ‘GREEN’
          WHEN ‘3’ THEN ‘ORANGE’
          [ELSE ‘GRAY’]
   END

 

3. DECODE(SUBWAY_LINE, ‘1’, ‘BLUE’, ‘2’, ‘GREEN’, ‘3’, ‘ORANGE’, [‘GRAY’])
CASE문에서는 ELSE 뒤의 값이 DEAULT 값이 되고 별도의 ELSE가 없는 경우 NULL값이 DEFAULT 값