SQL 함수의 종류에 대해서 이야기해봅시다.
SQL 함수의 개념 설명
SQL 함수는 데이터베이스(DataBase)에서 대소문자가 섞여 있는 문자를 소문자로 일괄 변경해 준다거나 소수점을 가지고 있는 긴 숫자들을 특정 자릿수에서 반올림 해준다거나 하는 등 수학에서의 함수와 같이 매핑을 제공하는 기능을 의미합니다.
수학(Mathematics)에서의 함수는 입력 값에 따라 출력 값이 매핑 되어져서 결과 값이 나오는 개념입니다. 데이터 베이스(DB)에서도 이와 비슷한 함수들이 있는 것입니다.
SQL 함수에는 문자 함수, 숫자 함수, 날짜 함수, 변환 함수, NULL과 관련된 함수가 있습니다.
SQL 함수의 종류 설명
1. 문자 함수
문자 함수의 종류에는 다양한 함수들이 존재합니다. 문자 함수에는 CHAR 함수, LOWER 함수, UPPER 함수, LTRIM 함수, RTRIM 함수, TRIM 함수, SUBSTR 함수, LENGTH 함수가 있습니다.
[문자 함수]
| 구분 | 내용 | 사용 방법 |
| 1 | CHAR | CHAR(ASCII) |
| 2 | LOWER | LOWER(문자열) |
| 3 | UPPER | UPPER(문자열) |
| 4 | LTRIM | LTRIM(문자열, [특정 문자]), 여기서 [] 는 선택 사항 |
| 5 | RTRIM | RTRIM(문자열, [특정 문자]), 여기서 [] 는 선택 사항 |
| 6 | TRIM | TRIM([위치] [특정 문자] [FROM] 문자열), 여기서 [] 는 선택 사항 |
| 7 | SUBSTR | SUBSTR(문자열, 시작점, [길이]), 여기서 [] 는 선택 사항 |
| 8 | LENGTH | LENGTH(문자열) |
| 9 | REPLACE | REPLACE(문자열, 변경 전 문자열, [변경 후 문자열]), 여기서 [] 는 선택 사항 |
| 10 | LPAD | LPAD(문자열, 길이, 문자) |
CHAR 함수는 ASCII 코드를 인수로 입력하였을 때 해당 입력 값에 매핑되는 문자가 무엇인지를 알려주는 함수입니다. ASCII는 코드이며 총 128개의 문자를 숫자로 표현할 수 있도록 정의해 놓은 개념입니다. 데이터 베이스 문제 세트나 해당 국가의 문자 세트 중에서 VARCHAR2 값으로 n과 동일한 이진수를 가지는 문자를 반환하게 됩니다. 일반적을 아스키 코드를 문자로 변환해서 반환하는 함수입니다.
LOWER 함수는 문자열을 소문자로 변환해주는 함수입니다. 입력된 영문 문자열들을 모두 소문자로 변환 시켜 줍니다.
UPPER 함수는 문자열을 대문자로 변환해주는 함수입니다.
LTRIM 함수는 문자열의 왼쪽 공백을 없애주는 함수입니다. 만약 특정 문자를 명시해주면 문자열을 왼쪽 부터 한 글자 씩을 특정 문자와 비교하여 특정 문자에 포함되어 있으면 없애주고 포함되어 있지 않으면 멈추는 함수입니다. 즉, LTRIM 함수는 공백을 없애주는 함수입니다. 문자열 인수 CHAR 좌측 끝에서 셋에서 지정한 모든 문자를 없애줍니다.
RTRIM 함수는 문자열의 오른쪽 공백을 없애주는 함수입니다. 만약 특정 문자를 명시해주었을 경우 문자열을 오른쪽에서 부터 한 글자씩 특정 문자와 비교하여 특정 문자에 포함되어 있으면 없애주고 포함되어 있지 않으면 멈추는 함수입니다. RTRIM도 LTRIM과 마찬가지로 공백을 없애주는 함수입니다.
TRIM 함수는 옵션이 없으면 문자열의 왼쪽과 오른쪽 공백을 없애주는 함수입니다. 만약 옵션을 넣어주면 문자열을 위치로 지정된 곳에서부터 한 글자씩 특정 문자와 비교하여 같은 값이 있으면 없애주고 없으면 멈추는 함수입니다.
SUBSTR 함수는 문자열의 원하는 부분만 잘라서 반환해줄 수 있는 함수입니다. 길이를 명시하지 않으면 문자열의 시작 점부터 문자열의 끝까지 반환해줍니다. SUBSTR함수는 특정 부분만 잘라서 조건을 걸어줄 때 주로 사용합니다.
LENGTH 함수는 문자열의 길이를 반환 시켜주는 함수입니다.
REPLACE 함수는 문자열에서 변경 전 문자열을 찾아서 변경 해준 다음 문자열로 변경해주는 함수입니다. 변경한 다음 문자열을 명시해주지 않을 경우 문자열에서 변경 전 문자열을 없애줍니다.
LPAD 함수는 문자열이 설정한 길이가 될 때까지 왼쪽을 특정 문자로 채워주는 함수입니다. 지정된 자릿수 n에 해당하는 길이에 1차적으로 문자열 expr1을 채워주고 왼쪽에 남은 공간에 expr1을 반복적으로 채워줍니다.
2. 숫자 함수
숫자 함수는 수치 함수라고도 부르며 숫자 함수에는 다양한 함수들이 있습니다. 숫자 함수에는 ABS 함수, SIGN 함수, ROUND 함수, TRUNC 함수, CEIL 함수, FLOOR 함수, MOD 함수가 있습니다.
[숫자 함수]
| 구분 | 내용 | 사용 방법 |
| 1 | ABS | ABS(숫자) |
| 2 | SIGN | SIGN(숫자) |
| 3 | ROUND | ROUND(숫자, [자릿수]) , 여기서 [] 는 선택 사항 |
| 4 | TRUNC | TRUNC(숫자, [자릿수]), 여기서 [] 는 선택 사항 |
| 5 | CEIL | CEIL(숫자) |
| 6 | FLOOR | FLOOR(숫자) |
| 7 | MOD | MOD(숫자1, 숫자2) |
ABS 함수는 수의 절댓값을 반환해주는 함수입니다. 인수 n의 절댓값을 반환시킵니다.
SIGN 함수는 수의 부호를 반환해주는 함수입니다. 양수이면 1을 반환하고, 음수이면 -1을 반환하며, 0이면 0을 반환합니다.
ROUND 함수는 수를 지정한 소수점 자릿수까지 반올림하여 반환해주는 함수입니다. ROUND 함수에 자릿수를 지정할 수도 있고 지정하지 않을 수도 있는데 만약 자릿수를 명시하지 않았을 경우 자릿수의 기본 값은 0이며 이에 따라 반올림 된 정수로 반환하고 자릿수가 만약 음수일 경우 지정된 정수 부분을 반올림하여 반환 시킵니다. ROUND 함수는 숫자 값을 소숫점 아래 (정수+1) 번째 위치에서 정수 방향으로 반올림 한 값을 반환합니다.
TRUNC 함수는 숫자가 지정된 소수점 자릿수까지 버림 하여 반환해주는 함수입니다. 자릿수를 명시하지 않았을 경우 자릿수의 기본 값은 0이 됩니다. 이는 버림 된 정수로 반환하게 됩니다. 만약 숫자가 음수일 경우 지정된 정수 부분에서 버림 하여 반환합니다. 수치 값 숫자1을 소숫점 자리 중에서 파라미터 숫자2의 자리 이하를 버림 처리하는 개념입니다.
CEIL 함수는 소수점 이하의 숫자를 올림 한 정수를 반환해주는 함수입니다. 인수에서 지정한 숫자를 올림하여 정수를 구하는 함수입니다. CEIL 함수를 절단 함수라고 부릅니다. CEIL 함수는 숫자형 함수 중에서 수보다 크거나 같은 최소 정수를 반환해주는 함수입니다.
MOD 함수는 숫자1과 숫자2로 나눈 나머지를 반환해주는 함수입니다. 만약 숫자2가 0이면 숫자1을 그냥 반환 시켜 줍니다.
3. 날짜 함수
날짜 함수는 대표적으로 3가지 함수가 있습니다. 날짜 함수의 대표적 3가지 함수는 SYSDATE 함수, EXTRACT 함수, ADD_MONTHS 함수가 있습니다.
[날짜 함수]
| 구분 | 내용 | 사용방법 |
| 1 | SYSDATE | SYSDATE |
| 2 | EXTRACT | EXTRACT(특정 단위 FROM 날짜 데이터) |
| 3 | ADD_MONTHS | ADD_MONTHS(날짜 데이터, 특정 개월 수) |
SYSDATE 함수는 현재의 연도, 월도, 시, 분, 초를 반환해주는 함수입니다. SYSDATE를 입력하면 현재 시간을 알려줍니다.
EXTRACT 함수는 날짜 데이터에서 특정 단위 만을 출력해서 반환해주는 함수입니다. 특정 단위는 YEAR, MONTH, DAY, HOUR, MINUTE, SECOND가 있습니다.
ADD_MONTHS 함수는 날짜 데이터에서 특정 개월 수를 더한 날짜를 반환해주는 함수입니다. 날짜의 이전 달이나 다음 달에 기준 날짜의 일자가 존재하지 않으면 해당 월의 마지막 일자가 반환 됩니다.
4. 변환 함수
변환 함수는 명시적 형 변환과 암시적 형변환이 있습니다. 데이터 베이스에서는 데이터 유형에 대한 형 변환을 할 수 있는 방법이 크게 2가지가 있는 것입니다.
[데이터 베이스의 데이터 유형 변환 방법]
| 구분 | 내용 | 설명 |
| 1 | 명시적 형변환 | 변환 함수를 사용하여 데이터 유형 변환을 명시적으로 나타냄 |
| 2 | 암시적 형변환 | 데이터베이스가 내부적으로 알아서 데이터 유형을 변환함 |
명시적 형변환은 변환 함수를 사용하여 데이터 유형 변환을 명시적으로 나타내는 것을 의미합니다.
암시적 형 변환은 변환 함수를 사용하지 않고 데이터 베이스 내부적으로 형변환을 수행하도록 하는 것을 말합니다.
암시적 형 변환을 사용할 경우 컬럼의 데이터 유형을 고려하지 않고 SQL 쿼리를 작성할 경우 성능 저하를 불러올 수 있습니다. 경우에 따라서는 에러가 발생할 수 있기 때문에 명시적 형변환을 사용하는 것이 좋습니다.
명시적 형변환에 사용하는 함수는 대표적으로 크게 3가지가 있습니다.
[명시적 형변환 함수]
| 구분 | 내용 | 사용방법 |
| 1 | TO_NUMBER | TO_NUMBER(문자열) |
| 2 | TO_CHAR | TO_CHAR(숫자 또는 날짜, [포멧]), 여기서 [] 는 선택 사항 |
| 3 | TO_DATE | TO_DATE(문자열, 포멧) |
TO_NUMBER 함수는 문자열을 숫자형으로 변환해주는 함수입니다. 인수 expr을 NUMBERE 데이터 형의 값으로 변환합니다. TO_NUMBER 함수를 사용하여 문자를 숫자로 명시적 형변환을 할 수 있게 됩니다.
TO_CHAR 함수는 숫자나 날짜형의 데이터를 정의하는 포멧 형식의 문자형으로 변환하도록 하는 함수입니다. 예를 들어 TO_CHAR 함수는 TO_CHAR(SYSDATE, ‘YYYYMMDD’)로 사용합니다.
TO_DATE 함수는 포멧 형식의 문자형 데이터를 날짜형으로 변환해주는 함수입니다. YYYY는 년을 의미하고 MM은 월을 의미하고 DD는 일을 의미합니다. HH는 12시간 기준 시를 의미하고 HH24는 24시간 기준 시를 의미하며 MI는 분, SS는 초를 의미합니다. TO_DATE는 CHAR, VARCHAR2, NCHAR, NVARCHAR2 데이터 형의 문자열 char를 날짜형 데이터 타입 값으로 변환하는 형 변환 작업을 수행하는 함수입니다. 문자형 날짜 데이터를 날짜형 데이터로 변환합니다.
5. NULL 관련 함수
NULL 관련 함수는 NULL을 처리해서 다른 문자열로 대체하는 SQL 함수입니다. NULL 값은 아직 정의되지 않은 값으로 0이나 공백과는 구분되는 개념입니다.
NULL과 관련된 함수에는 NVL, NULLIF, COALESCE, NVL2 등이 있습니다.
[NULL 관련 함수]
| 구분 | 내용 | 사용방법 |
| 1 | NVL | NVL(인수1, 인수2) |
| 2 | NULLIF | NULLIF(인수1, 인수2) |
| 3 | COALESCE | COALESCE(인수1, 인수2, 인수3, …) |
| 4 | NVL2 | NVL2(인수1, 인수2, 인수3) |
NVL 함수는 NVL(인수1, 인수2)로 표현하여 사용하였을 때 인수1의 값이 NULL인 경우 인수2를 반환하고 만약 NULL이 아니라면 인수1을 반환하도록 하는 함수입니다.
NULLIF 함수는 NULLIF(인수1, 인수2)로 표현하여 사용하였을 때 인수1과 인수2가 같으면 NULL을 반환하고 만약 같지 않다면 인수1을 반환해주는 함수를 의미합니다.
COALESCE 함수는 NULL이 아닌 최초의 인수를 반환해주는 함수입니다.
NVL2 함수는 NVL2(인수1, 인수2, 인수3)을 사용하였을 때 인수1이 NULL이 아닌 경우 인수2를 반환하고 NULL인 경우 인수3을 반환하는 함수입니다.