[SQL] 함수 - 단일 행 함수 (1)

함수 - 단일 행 함수 (1)

모두의 SQL을 읽고 배운 것을 복습하기 위해 정리해서 기록합니다.
해당 도서는 Oracle 데이터베이스를 기준으로 하고 있습니다.

0. 단일 행 함수

데이터를 다루다 보면 행에 저장된 정보들을 가공하거나 데이터 타입을 변환해야 하는 경우들도 종종 있습니다.
단일 행 함수는 데이터베이스 시스템의 함수 중에서도 각각의 행에 대해 각각 적용되는 함수들을 의미합니다.

데이터베이스 함수는 [함수이름(매개변수)]의 형태로 사용합니다.
    예) SUBSTRING('안녕하세요', 0, 2) -> '안녕'

호출하고자 하는 함수의 이름을 적고 소괄호 안에 변경하고자 하는 데이터를 매개변수로 집어넣으면 함수의 실행 결과가 반환됩니다.

 

---

 

1. 문자 타입 함수

데이터 조작과 가공에서 가장 많이 쓰이는 문자 타입 함수를 먼저 살펴보겠습니다.
모든 함수는 매개변수 값으로서 직접 문자열을 적어 넣을 수도 있고 컬럼 명을 사용할 수도 있습니다.

    예) [CONCAT('Hello', 'World)] 또는 [CONCAT(column_01, column02)]

 

LOWER : 대문자를 소문자로 변환

LOWER함수는 매개변수로 주어진 문자열을 모두 소문자로 바꾸어줍니다
[LOWER('HELLO')] 와 같은 형태로 사용할 수 있으며 결과값은 [hello]입니다.

UPPER : 소문자를 대문자로 변환

소문자로 바꿔주는 함수가 있으니 당연 그 반대도 있습니다. UPPER 함수는 소문자를 대문자로 변환해 줍니다.
[UPPER('one two three')] 와 같은 형태로 사용하며 결괏값은 [ONE TWO THREE]입니다.

INITCAP : 문자열의 첫머리를 변환

이 함수는 매개변수로 주어진 문자열의 첫 글자(INIT : 첫 글자, 이니셜)를 대문자로 바꿔줍니다(CAP : 모자를 씌우듯이)
[INITCAP('aBCDeFu')]처럼 사용하며[Abcdefu]라는 문자열을 반환합니다.

SUBSTRING : 문자열 자르기

변경할 문자열과 자릿수를 매개변수로 받아 문자열을 잘라내어 반환하는 함수입니다.
SUBSTRING 함수는 가장 많이 사용되는 문자열 함수 중 하나인데 3개 내지 2개의 매개변수를 가집니다.
[SUBSTRING('문자열', 처음부터 이만큼 자를게요)] 또는
[SUBSTRING('문자열', 여기서부터 , 이만큼 자를게요)] 와 같은 형태로 사용합니다.

[SUBSTRING('abcdefg', 3)] 과 같이 문자열과 숫자 하나를 입력받으면 반환되는 문자열은 [abc]입니다.
[SUBSTRING('abcdefg', 2, 4)] 와 같이 문자열과 숫자 두 개를 입력받으면 반환되는 문자열은 [cdef]입니다.
첫 번째 문자의 인덱스는 0이기 때문에 2를 입력했다면 세 번째 글자부터 잘라내게 됩니다.

REPLACE : 문자열 치환

매개변수로 입력받은 문자열에서 특정 문자열을 목적 문자열로 치환하여 반환하는 함수입니다.
세 개의 매개변수를 가지며 다음과 같은 형태로 사용합니다.
[REPLACE('문자열', '이 문자를', '이걸로 바꿔주세요')]
[REPLACE('ooxxox', 'x', 'o')]처럼 함수를 호출했다면 반환되는 문자열은 [oooooo]입니다.

CONCAT : 문자열 이어 붙이기

정말 자주 쓰이는 함수입니다. ||연산자와 같은 역할을 하지만 두 개 이상의 문자열을 이어 붙이려면
함수를 중첩하여 사용해야 하기 때문에 가독성 면에서는 조금 불리합니다😅
[CONCAT('원래 문자열에', '이 문자열을 붙일게요')] 와 같은 형태로 사용합니다.
[CONCAT('Hello', CONCAT(' ', 'World!'))] 와 같은 형태로 사용할 수 있으며
[Hello World!]라는 결과를 반환합니다.

LENGTH : 문자열의 길이 반환

[LENGTH('length')] 와 같은 형태로 사용하며 반환되는 값은 숫자타입으로서 [6]입니다.

INSTR : 특정 문자열의 위치 반환

문자열 안에서 특정 문자의 '가장 앞선 인덱스'를 반환받고자 할 때 사용할 수 있습니다.
[INSTR('coffee', 'e')]처럼 사용했다면 'e'가 가장 먼저 등장하는 위치으로서 [5]를 반환합니다.

숫자 타입의 매개변수를 추가적으로 2개까지 부여할 수 있는데,
검색을 시작할 위치와 몇 번째 등장하는 값을 찾을 것인지를 정할 수 있습니다.
[INSTR('In Out In Out In Out', 'O', 5, 2)]처럼 사용할 수 있으며
해당 문자열에서 2번째 'O'를 5번 인덱스부터 검색한다는 의미입니다. 반환값은 [18]입니다.

LPAD, RPAD : 문자열에 자릿수 채워 넣기

앞글자의 L과 R은 각각 Left와 Right를 의미하며 매개변수로 받은 문자열에 특정 문자로 문자 길이를 채워줍니다.
[LPAD('ABCD', 10, '@')] 또는 [RPAD('AB', 5, '0')]처럼 사용할 수 있으며
반환값은 각각 [@@@@@@ABCD], [AB000]입니다. 

이 친구 역시 중첩하여 사용할 수 있습니다. [RPAD(LPAD('AB', 5, '0'), 8, '1')]처럼 사용할 수 있습니다.
가독성은 정말 좋지 않으며 반환값은[000 AB111]입니다.

LTRIM, RTRIM : 문자열 삭제하기

LTRIM은 문자열의 좌측부터, RTRIM은 문자열의 우측부터 연산을 시작합니다.
문자열에서 특정한 문자를 지워줍니다. 지정한 문자가 아닌 다른 값을 만나게 되면 실행을 중단합니다.

[LTRIM('FFTTTH', 'F')] 와 같은 형태로 사용할 수 있으며 반환값은 [TTTH]입니다.
두 번째 매개변수가 없다면 문자열에서 공백을 없애줍니다.

TRIM : 문자열 양 끝의 공백 제거

매개변수로 받은 문자열 좌측 우측에 존재하는 공백을 지워줍니다.
단, 문자열 중간에 존재하는 띄어쓰기와 같은 공백은 삭제되지 않습니다.
[TRIM('    Republic of Korea     ')] 와 같은 형태로 호출하게 되며 반환값은 [Republic of Korea]입니다.

 

---

 

2. 숫자 타입 함수

기본적인 사칙연산을 수행하기 위한 산술연산자 외에도  제곱근을 구하거나 반올림을 하는 등

조금 더 복잡한 숫자연산이 필요할 때 주로 쓰이는 숫자함수를 알아보도록 하겠습니다.

 

ROUND : 반올림

매개변수로 숫자 두 개를 받아줍니다. 첫 번째 숫자는 반올림하고자 하는 숫자이고 두 번째 숫자는 자릿수를 뜻합니다.
[ROURND(125.5, 0)] 과 같이 사용할 수 있으며 반환값은[126]입니다.

소수점 이하 첫 째 자리에서 반올림하고자 한다면 0을, 소수점 이하 두 번째 자리에서 반올림하고자 한다면 1을...
이런 식으로 소수점 이하에서 더 정밀한 반올림을 할수록 숫자가 양수로 증가합니다.

1의 자리에서 반올림은 -1, 10의 자리에서 반올림은 -2를 주는 식으로
정수 범위에서의 반올림은 자릿수가 증가할수록 자릿값 숫자가 음수로 증가합니다.

TRUNC : 버림

숫자를 깎아냅니다. ROUND 함수와 마찬가지로 두 개의 매개변수를 넘겨받습니다.
첫 번째 매개변수와 두 번째 매개변수의 의미도 ROUND 함수와 동일합니다.

ROUND함수가 반올림이라면 TRUNC 함수는 버림이라는 차이가 있습니다.
[ROURND(125.5, 0)] 과 같이 사용한다면 반환값은 0.5를 버려서 [125]입니다.

 MOD : 나누기 후 나머지를 구함

MOD 함수는 조금 신기합니다. 두 개의 매개변수를 받아서 "첫 번째 값이 0이 되거나,
첫 번째 값이 두 번째 값보다 작아질 때까지"  첫 번째 값에서 두 번째 값을 뺀 나머지를 구합니다.

예를 들어보겠습니다. [MOD(14, 4)] 의 경우 [14-4-4-4 = 2]라는 결과가 나옵니다.

(사실 잘 아시는 많은 분들은 '어? 그냥 나눠서 나머지 구하는 거 아닌가요?'라고 말씀하실 수 있습니다.
 하지만 나눗셈도 결국 뺄셈의 연속이거니와 소수를 반환하지 않는 작동 방식을 봤을 땐
 단순한 '나눗셈 연산'보다는 '뺄셈연산의 연속'으로 보는 것이 타당하다는 생각입니다. )

CEIL, FLOOR : 올림, 내림

각각 올림과 내림입니다. ROUND, MOD 함수와 완전히 같은 매개변수를 받습니다.
[CEIL(125.5, 0)] 의 경우 [16]을, [FLOOR(125.5, 0)]의 경우 [15]를 반환합니다.

SIGN : 매개변수가 음수인지 양수인지 판단

물론 우리가 봤을 땐  숫자에 음수 부호(-)가 붙어있으면 누가 봐도 음수라고 판단하지만,
값이 음수인 경우, 양수인 경우에 따라 다른 논리적용을 해야 할 때가 있습니다 ㅎㅎ
SIGN 함수는 매개변수 한 개를 받아서 양수일 경우 [1]을, 음수일 경우[-1]을, 매개변수가 0일 경우 0을 반환합니다.

POWER : 거듭제곱을 반환함

POWER 함수는 매개변수 두 개를 받아서 첫 번째 매개변수를 두 번째 매개변수만큼 제곱한 값을 출력합니다.
[POWER(2, 11)] 과 같이 쓸 수 있으며 반환값은 2의 11 제곱인 [2048]입니다.

SQRT : 제곱근을 반환함

처음 보시는 분들은 갑자기 이게 뭐야?.. 싶으실 수 있지만 Squre Root의 약자로서...
매개변수로 넘겨받는 값의 제곱근을 출력합니다. [SQRT(64)] 의 경우 [8]을 반환하겠죠?

숫자 자릿값 복습하기

저는 개인적으로 ROUND, MOD, 등 숫자의 자릿값을 받는 함수들이 너무 어려웠습니다...😥
이 글을 읽고 계신 분들 중에서도 분명 이 내용을 어려워하실 분들이 계실 것 같아서
한 번만 더 짚고 넘어가 보도록 하겠습니다.

 1   2   3   4  o  5   6   7   8 <-소수점은 잘 보이라고 알파벳 'o'로 대체하였습니다.
-4  -3  -2  -1     0   1   2   3 <-매개변수로 넘겨주는 자리의 값입니다.
자릿값을 넘겨주는 자릿수부터 0이 됩니다.

 

---

 

3. 날짜 타입 함수

날짜 타입 함수는 DATE 형식의 값을 반환하며 날짜의 계산이나 숫자를 날짜 형식으로 변환하는 등의 기능을 제공합니다.

 

 DATE + 임의의 숫자

날짜에 숫자만큼의 일수를 더하여 반환합니다.
[SYSDATE + 2]와 같은 형식으로 사용할 수 있으며 오늘 연월일의 다음다음 날의 날짜를 반환합니다.

DATE - 임의의 숫자

날짜에다가 숫자만큼의 일수를 빼고 반환합니다.
날짜 더하기와 비슷합니다 ㅎㅎ

DATE01 - DATE02

앞의 날짜에서 뒤의 날짜를 뺀 만큼의 '일수'를 반환합니다. 

DATE + 시간

날짜에 시간을 더 한 만큼의 결괏값을 반환합니다.

MONTHS_BETWEEN : 월 계산

두 개의 날짜타입의 매개변수를 받아서 두 날짜 사이의 개월 수를 계산합니다

ADD_MONTHS : 월 더하기

날짜타입 변수와 숫자타입의 변수 한 개씩을 매개변수로 받아서 숫자값만큼의 월을 날짜에 더합니다.

NEXT_DAY : 다음 요일의 날짜 구하기

날짜타입변수로부터 돌아오는 요일에 대한 날짜를 반환합니다.
[NEXT_DAY(2022/02/22, 3)] 과 같은 형식으로 사용할 수 있으며
0은 일요일, 1은 월요일, 2는 화요일... 등을 의미합니다.
[NEXT_DAY(2022/02/22, '화요일')] 과 같은 형식으로도 사용할 수 있습니다.

 LAST_DAY : 돌아오는 월의 마지막 날짜 구하기

[LAST_DAY(날짜)] 와 같은 형식으로 사용됩니다. 해당 날짜가 속한 달의 가장 마지막 날짜를 반환합니다.

ROUND, TRUNC : 날짜 반올림, 날짜 절삭

날짜와 연, 또는 날짜와 월을 매개변수로 받아서 해당 날짜를 연산합니다. 
[ROUND(날짜, 'MONTH')]처럼 사용할 수 있으며 두 번째 매개변수를 기준으로 연산되며 일은 1일로 맞추어집니다.

 

---

 

4. 형 변환 함수

타입 변환 함수라고도 합니다. 여타 프로그래밍 언어들에 데이터 타입이 존재하듯이

데이터베이스 시스템도 여러 가지 데이터 타입을 규정하고 있습니다.

하지만 때로는 문자 타입의 데이터를 숫자처럼 다루거나 날짜 타입 데이터를 문자처럼 다루어야 할 때도 있습니다.

이럴 때 데이터의 타입 변환을 지원하는 함수가 형 변환 함수입니다. 

 

암시적 형 변환  (자동 형 변환)

데이터베이스 시스템이 융통성을 발휘하여 사용자의 엉터리 연산을 정상적으로 처리해 주는 것을 뜻합니다.
여러분들이  제 엉터리 설명을 '아... 대충 그런 거구나...'라고 이해하시는 것과 비슷합니다.

예를 들면 아래의 쿼리문은 숫자타입과 문자타입을 산술연산하려는 시도를 하고 있습니다.

SELECT
	15 * '2'

'2'는 작은따옴표로 감싸여있기 때문에 엄연히 문자입니다.
하지만 친절한 데이터베이스 시스템과 일부 스크립트 언어는 계산을 위해 유동적으로 타입을 변환해 줍니다.

물론 오류를 최소화하고 정확한 데이터 연산을 위해서는 맞춤법을 지키듯이 데이터타입을 지켜주는 편이 좋습니다.

 

명시적 형 변환 (수동 형 변환)

문자와 날짜, 문자와 숫자를 상호 변환할 수 있는 함수가 있으며 숫자와 날짜 타입의 상호 변환은 지원되지 않습니다.

TO_CHAR : 날짜, 숫자, 문자 값을 지정한 형식의 문자열 타입으로 변환해 줍니다.

[TO_CHAR('2022/12/29', 'YYYYMMDD')] 와 같이 사용할 수 있으며 반환값은 [20221229]입니다.
날짜 지정 형식은 나중에 따로 자세히 다루도록 하겠습니다😄

TO_NUMBER : 숫자 형식의 문자열을 숫자 데이터 타입으로 변환해 줍니다.

[TO_NUMBER('01012342323')] 과 같이 사용합니다. 반환값은 그대로지만 데이터 타입만 변경됩니다.

TO_DATE

[TO_DATE('20221229', 'YYMMDD')] 와 같이 사용합니다.
Y는 연, M은 월, D는 일을 뜻하며 2자리의 연도, 2자리의 월과 일을 가진 여섯 자리의 포맷으로 날짜 타입 데이터를 반환해 줍니다.

 

 

---

 

 

도움이 되어드렸다면 구독 / 좋아요 부탁드립니다.👏🏻

감사합니다.🙂