[SQL/2과목] 함수(1) - 단일행함수
이번에는, sql의 함수, 그중에서도 단일행함수에 대해서 포스팅해보겠다. 함수의 종류는 정말 많으니 잘 외워보도록 하자!
우선, 함수란 무엇일까? 함수는 입력된 값에 대해서 어떤 연산을 수행한 후 그 결과를 반환하는 일련의 명령어의 묶음이다. 벤더(기업)별로 만들어져 제공되는 내장함수, 사용자가 직접 작성하는 사용자 정의 함수가 있는데 여기 이 내장함수에 단일행함수, 다중행함수가 있다!
단일행 함수
단일행 함수는 말 그대로 단일행 값이 입력되는 함수이다. 하나의 행에 대해서 연산을 수행 후 결과를 뱉는 것이다. 행이 하나일 뿐이지, 인자는 여러개 받을 수 있다!! 당연히 결과는 단일 값을 반환한다.
단일행 함수 내에도 아래와 같이 여러가지 함수가 있다.
| 종류 | 내용 |
| 문자형 함수 | 문자를 입력하면 문자나 숫자값을 반환한다. |
| 숫자형 함수 | 숫자를 입력하면 숫자 값을 반환한다. |
| 날짜형 함수 | DATE 타입의 값을 연산한다. |
| 변환형 함수 | 문자, 숫자, 날짜형 값의 데이터 타입을 변환한다. |
| NULL 관련 함수 | NULL을 처리하기 위한 함수 |
이를 자세히 봐보자!
문자형 함수
문자를 입력하면 문자나 숫자 값을 반환하는 함수이다. Oracle이랑 MSSQL이랑 다르게 표기하는 함수도 있다. 아래 표에서 둘이 다른거는 오라클함수/MSSQL함수 이렇게 표시하겠다!
| 문자형 함수 | 함수 설명 |
| LOWER(문자열) | 문자열의 알파벳 문자를 소문자로 바꿔준다. |
| UPPER(문자열) | 문자열의 알파벳 문자를 대문자로 바꿔준다. |
| ASCII(문자) | 문자나 숫자를 아스키번호로 바꾸어준다. |
| CHR/CHAR(아스키번호) | 아스키번호를 문자나 숫자로 바꾸어준다. |
| CONCAT(문자열1, 문자열2) | 문자열 1과 문자열 2를 연결한다. 합성연산자와 동일하다.(Oracle에서는 합성연산자가 '||', MSSQL은 '+') |
| SUBSTR/SUBSTRING(문자열, m,n) | 문자열에서 m위치에서 n개의 문자 길이에 해당하는 문자를 돌려준다. n이 생략되면 끝까지! |
| LENGTH/LEN(문자열) | 문자열의 개수를 숫자값으로 돌려준다. |
| LTRIM(문자열,지정문자) or RTRIM(문자열, 지정문자) |
문자열의 첫(or 마지막) 문자부터 확인해서 지정문자가 나타나면 해당 문자를 제거한다. 지정문자가 생략되면 공백값이고, MSSQL은 지정문자를 사용할 수 없다!(즉 공백값만 제거 가능하다) |
| TRIM(leading | trailing | both 지정문자 FROM 문자열) | 문자열에서 머리말, 꼬리말, 또는 양쪽에 있는 지정문자를 제거한다. leading | trailing | both 가 생략되면 both가 디폴트값이며 지정문자가 생략되면 공백값이다. MSSQL은 지정문자를 사용할 수 없다!(즉 공백값만 제거 가능하다) |
| REPLACE(원본문자열, 찾을문자열, 바꿀문자열) | 원본문자열에서 찾을 문자열을 바꿀 문자열로 대체한다. 바꿀문자열에 공백값을 넣을 경우 삭제할 수 있다. |
| INSTR(전체문자열, 찾을 문자열)/CHARINDEX(찾을문자열, 전체문자열) | 전체 문자열에서, 찾을 문자열이 몇번째로 등장하는지를 찾는다. INSTR은 (전체문자열, 찾을문자열, 시작위치, 몇번째 발견 값) 이렇게 네개의 인자도 가능하지만, CHARINDEX는 (찾을문자열, 전체문자열, 시작위치) 이렇게 세개의 인자까지만 가능하다. 즉 MSSQL에서는 이 함수를 통해선 첫번째 발견 값만 찾을 수 있다 |
노랭이책에 없는 두개의 함수도 추가로 정리해보았다. INSTR이랑 CHARINDEX는 꽤나 다르니 유의하도록하자!
숫자형 함수
숫자를 입력하면 숫자값을 반환한다.
| 숫자형 함수 | 함수 설명 |
| ABS(숫자) | 숫자의 절대값을 반환한다. |
| SIGN(숫자) | 숫자를 양수, 음수, 0으로 구분하여 반환한다. 양수면 1, 음수면 -1, 0이면 0을 반환한다. |
| MOD(숫자 1, 숫자 2) | 숫자1을 숫자2로 나눈 나머지를 반환한다. MSSQL에는 MOD가 없고 %연산자로 대체한다. |
| CEIL/CEILING(숫자) | 숫자보다 크거나 같은 최소 정수를 반환한다. |
| FLOOR(숫자) | 숫자보다 작거나 같은 최대 정수를 반환한다. |
| ROUND(숫자, m) | 숫자를 소수점 m자리에서 반올림하여 반환한다. m이 생략된다면 디폴트는 0이다. |
| TRUNC(숫자, m) | 숫자를 소수점 m자리에서 잘라서 버린다. m 생략시 디폴트는 0이며 MSSQL에는 TRUNC가 없다. |
| SIN, COS, TAN ... | 숫자의 삼각함수값을 반환한다. |
| EXP(), POWER(), SQRT(), LOG(), LN() | 숫자의 지수, 거듭제곱, 제곱근, 로그, 자연로그 값을 반환한다. |
날짜형 함수
날짜데이터 타입의 값을 연산하는 함수이다.
| 날짜형 함수 | 함수 설명 |
| SYSDATE/GETDATE() | 현재 날짜와 시각을 반환한다. |
| EXTRACT('YEAR' | 'MONTH' | 'DAY' from d) /DATEPART('YEAR' | 'MONTH' | 'DAY', d) |
날짜 데이터에서 년/월/일 데이터를 반환한다. (시간/분/초도 가능) 만약 01월01일과 같은 날짜 데이터에서 DAY를 반환해도 01이 아닌 1이 출력된다. |
| TO_NUMBER(TO_CHAR(d,'YYYY')) /YEAR(d) |
날짜데이터에서 "년"데이터단위를 반환한다. |
| TO_NUMBER(TO_CHAR(d,'MM')) /MONTH(d) |
날짜데이터에서 "월"데이터단위를 반환한다. |
| TO_NUMBER(TO_CHAR(d,'DD')) /DAY(d) |
날짜데이터에서 "일"데이터단위를 반환한다. |
| TRUNC(날짜, 요소) /CAST(GETDATE() AS DATE), CONVERT(DATE, GETDATE()) |
지정한 요소를 기준으로 날짜를 잘라내어 해당 단위의 시작 지점으로 만든다. MSSQL은 TRUNC가 없기에 CAST와 CONVERT를 사용하여 대체한다 |
| ROUND(날짜, 요소) | 지정한 요소의 절반을 기준으로 반올림한다. MSSQL은 이 함수가 없고 대체 구문 또한 복잡하다. |
개인적으로 제일 헷갈리고 화나고 어려웠던 부분이다. 우선 Oracle이랑 MSSQL차이도 너무 크다.. 그래서 보면 날짜데이터에서 년 데이터 반환이 두개씩 있다. 오라클은 보면 EXTRACT는 직관적으로 바로 보이지만, TO_NUMBER(TO_CHAR(d,'YYYY'))은 중첩함수이다. 그래서 데이터 한 단위 뽑아낼때 EXTRACT를 채택하는것이 더 좋다. 그리고 MSSQL에서는 YEAR(d)이런 애들이 더 직관적이고 간결해보인다. 그래서 얘네를 채택하는 것이 좋다. 자주 쓰이는 애들을 볼드체 해놨다!
추가로, 오라클에서는 특정날짜 +1은 하루를 더한다는 의미이다! (MSSQL에서는 무턱대고 날짜 +1하면 오류남!!)
오라클에서 날짜에 숫자 1은 기본적으로 정확히 하루를 의미한다. 그래서 하루는 24시간이니까, 1시간은 1/24이다. 왜냐하면 하루를 24로 나누어야지만 1시간이기 때문이다! 그럼 1분은 뭘까? 1분은 1/60시간이기때문에, 1/24/60이다! 그럼 1초는 당연히 1/24/60/60이다.
1/24/60/60을 1/(24*60*60)이라고 기재해도 괜찮다! (괄호가 우선연산이니까~)
형변환함수
두개의 데이터의 데이터 타입이 일치하도록 변환하는 작업이다. 이때 형변환함수를 사용하여 데이터 타입을 일치시키는 것을 명시적 형변환이라고 한다. 개발자가 함수를 사용해서 변환하는게 아닌 DBMS에서 자동으로 타입일치시키는 것은 암시적 형변환이라고 한다.
- Oracle
| 형변환 함수 | 함수 설명 |
| TO_NUMBER(문자) | 문자 데이터 타입을 숫자 데이터 타입으로 변환(->숫자) |
| TO_CHAR(값, '출력형식') | 값을 지정된 출력 형식에 맞추어 변환(->문자) |
| TO_DATE(문자열, '입력 형식') | 문자 데이터 타입을 지정된 입력 형식에 맞춰서 변환(->날짜) |
- MSSQL
MSSQL같은 경우는 딱 정해진 함수가 없어 CAST나 CONVER, FORMAT과 같은 함수를 사용하여 변환한다.
| 형변환 함수 | 함수 설명 |
| CAST(문자열 AS 숫자타입) | 문자 데이터 타입을 숫자 데이터 타입으로 변환(->숫자) ex) CAST('1234' AS INT) |
| FORMAT(값, '출력형식') CAST(값 AS 문자타입) CONVERT(문자타입, 값, 스타일코드) |
값을 지정된 출력 형식에 맞추어 변환(->문자) |
| CAST(문자열 AS 날짜타입) CONVERT(날짜타입, '문자열', 스타일코드) |
문자 데이터 타입을 지정된 입력 형식에 맞춰서 변환(->날짜) ex) CAST('2025-08-19' AS DATE) |
NULL 관련 함수
NULL은 알 수 없는 값이기에 일반적인 연산이 불가하므로, 이 NULL 값을 다른 값으로 바꾸거나 NULL여부를 따지거나 등을 해야된다!! 매우중요!
| 일반형 함수 | 함수 설명 |
| NVL(표현식1, 표현식2) /ISNULL(표현식1, 표현식2) |
표현식 1의 결과값이 NULL이면 표현식 2의 값을 출력한다. 단, 표현식1과 표현식2 결과 데이터 타입이 같아야한다. |
| NVL2(칼럼명, 표현식1, 표현식2) | NVL과 DECODE함수를 하나로 만든 함수이다. 컬럼이 NULL이 아니면 표현식1, NULL이면 표현식 2를 반환한다. NVL은 Oracle에만 존재한다. |
| NULLIF(표현식1, 표현식2) | 표현식1이 표현식2와 같으면 NULL, 같지않으면 표현식 1을 리턴한다. |
| COALESCE(표현식1, 표현식2,,,) | 여러값 중 첫번째로 NULL이 아닌 값을 반환한다.모든 표현식이 NULL이라면 NULL을 반환한다. |
여기서 주의할 점!! 데이터베이스에서 NULL은 등호가 아닌 IS NULL이나 IS NOT NULL로 비교한다! 그리고 또 주의할점!! 오라클에서는 공백입력시 NULL로 입력된다(둘을 같이 취급) 하지만 MSSQL에서는 ''는 공집합으로 입력된다(둘을 따로 취급)
NULL은 진짜 주의하자
오늘은 단일행함수에 대해 알아보았다!! 이제 추후 다중행함수에 대해 알아보겠다!