목차
함수
수학에서 함수는 다음과 같이 표현할 수 있다.:
f(x) = 3x + 2이 식에서 x에 1을 넣으면 결과는 5가 된다.
즉, 입력(input)이 주어졌을 때 고정된 방식으로 결과(output)이 결정되는 것이 함수의 핵심이다.
프로그래밍에서도 함수는 입력값에 따라 결과값을 반환하는 구조이며, SQL에서도 이와 비슷한 개념의 내장 함수들이 존재한다.
SQL 함수란?
SQL에서의 함수는 컬럼 값이나 표현식을 입력으로 받아, 그에 따른 결과를 반환하는 도구이다.
SQL 함수는 데이터를 조회하거나 가공할 때 자주 사용되며, 특정 조건이나 형식에 따라 결과를 다르게 만들 수 있다.
대표적으로 UPPER(), LOWER(), CONCAT(), SUBSTRING()등이 있다.
문자 함수
CHR(ASCII코드)/ CHAR() ->MSSQL
ASCII 코드는 총 128개의 문자를 숫자로 표현할 수 있도록 매핑이되어있는 코드이다. CHR 함수는 ASCII 코드를 인수로 입력 받아 매핑되는 문자가 무엇인지를 알려주는 함수이다.
SELECT CHR(65) FROM DUAL;
-> ALOWER(문자열)
인수로 전달 받은 문자열을 소문자로 변환해주는 함수이다.
SELECT LOWER('UGI') FROM DUAL;
-> ugiUPPER(문자열)
인수로 전달 받은 문자열을 대문자로 변환해주는 함수이다.
SELECT LOWER('ugi') FROM DUAL;
-> UGILTRIM(문자열 [,제거할 문자])
LTRIM 함수는 문자열의 왼쪽(앞쪽)에 있는 공백 또는 지정한 문자를 제거한다.
-- 기본 문법
LTRIM(문자열 [, 제거할_문자])- 제거할 문자를 생략하면 기본적으로 공백을 제거한다.
- 대부분의 DBMS는 기본적으로 공백 제거만 지원
SELECT LTRIM(' UGI') AS trimmed_text;
-- 결과: 'UGI'
SELECT LTRIM('000123', '0') AS trimmed_zero;
-- 결과: '123' (Oracle, SQL Server 등 일부 DB에서 지원)RTRIM(문자열 [, 제거할 문자])
RTRIM 함수는 문자열의 오른쪽(끝부분)에 있는 공백 또는 지정한 문자를 제거한다.
-- 기본 문법
RTRIM(문자열 [, 제거할_문자])- 역시 기본적으로는 오른쪽 공백 제거
- 특정 문자 제거는 마찬가지로 DBMS에 따라 지원 여부 확인 필요
SELECT RTRIM('UGI ') AS trimmed_text;
-- 결과: 'UGI'
SELECT RTRIM('123000', '0') AS trimmed_zero;
-- 결과: '123' (Oracle, SQL Server 등 일부 DB에서 지원)TRIM(문자열 [, 제거 옵션])
TRIM 함수는 문자열에서 좌우 양쪽 끝에 있는 공백 또는 특정 문자를 제거하는 함수다.
DBMS에 따라 문법과 동작 방식이 조금씩 다르지만, 전반적으로 다음과 같이 쓰인다.
- 가장 많이 쓰는 형식(MySQL, PostgreSQL등)
TRIM(문자열)
-- 기본적으로 양쪽 공백 제거- 특정 문자를 제거하는 확장 형식(ANSI SQL 표준/ Oracle/ PostgreSQL등)
LEADING: 왼쪽에서만 제거
TRAILING: 오른쪽에서만 제거
BOTH: 양쪽 모두 제거(기본 값)
TRIM([LEADING | TRAILING | BOTH] '제거할문자' FROM 문자열)
예제:
SELECT TRIM(' Hello World ') AS result;
-- 결과: 'Hello World'
SELECT TRIM(BOTH '0' FROM '000123000') AS result;
-- 결과: '123'
SELECT TRIM(LEADING '0' FROM '000123000') AS result;
-- 결과: '123000'
SELECT TRIM(TRAILING '0' FROM '000123000') AS result;
-- 결과: '000123'SUBSTR(문자열, 시작점 [,길이])/ SUBSTRING(문자열) -> MSSQL
문자열에서 원하는 부분만 잘라서 반환해주는 함수로서 길이를 명시하지 않을 경우 문자열의 시작점부터 문자열의 끝까지 반환된다.
SELECT SUBSTR('HELLO!', 3, 2) FROM DUAL;
-- LL
SELECT SUBSTR('HELLO!', 3, 4) FROM DUAL;
-- LLO!LENGTH(문자열)/LEN(문자열) -> MSSQL
문자열의 길이를 반환하는 함수이다.
SELECT LENGTH('UGI') FROM DUAL;
-> 3REPLACE(문자열, 변경 전 문자열 [,변경 후 문자열])
REPLACE 함수는 주어진 문자열에서 특정 부분 문자열을 찾으며, 다른 문자열로 치환(바꾸는) 역할을 하는 문자열 처리 함수이다.
-- 기본 문법
REPLACE(원본문자열, 찾을문자열, 바꿀문자열)- 원본문자열 : 대상이 되는 전체 문자열
- 찾을문자열 : 바꾸고자 하는 대상 문자열
- 바꿀문자열 : 교체할 문자열 (생략하면 제거 효과)
-- 'UGI'라는 문자열에서 G를 Z로 바꾸기
SELECT REPLACE('UGI', 'G', 'Z') AS result;
-> 'UZI'
-- 'UGI'에서 'G'를 제거 (3번째 인자 생략된 효과)
SELECT REPLACE('UGI', 'G', '') AS result;
-> 'UI'
-- 문장 전체에서 단어 바꾸기
SELECT REPLACE('나는 우기입니다', '우기', '영희') AS result;
->'나는 영희입니다'
⚠️ 주의할 점
REPLACE()는 대소문자를 구분하므로 REPLACE('UGI', 'g', 'X')처럼 하면 'UGI' 그대로 반환이되며 NULL이 하나라도 들어가면 결과는 당연히 NULL이다.
SELECT REPLACE(NULL, 'a', 'b'); → NULLLPAD(문자열, 길이, 문자)
LPAD 함수는 지정한 문자열의 왼쪽(Left Side)을 특정 문자로 채워서, 전체 문자열의 길이를 지정한 값만큼 맞춰주는 함수다.
주로 자리 수를 맞춰 출력하거나, 앞자리를 특정 형식으로 포맷팅할 때 사용된다.
-- 기본 문법
LPAD(문자열, 전체길이, 채울문자)- 문자열 : 원래 기준이 되는 원래 문자열
- 전체길이 : 최종적으로 만들고자 하는 문자열의 총 길이
- 채울문자 : 부족한 길이만큼 왼쪽에 채울 문자(1자 이상 가능)
-- 숫자 7을 3자리로 맞추고, 앞을 0으로 채우기
SELECT LPAD('7', 3, '0') AS result;
->'007'
-- 문자열 'abc'를 길이 6으로 맞추고 왼쪽에 '*' 채우기
SELECT LPAD('abc', 6, '*') AS result;
-> '***abc'
-- 아이디 숫자 형식을 '00001'처럼 고정 포맷으로 만들기
SELECT LPAD('23', 5, '0') AS user_code;
-> '00023'
SELECT LPAD('abc', 8, 'xy') AS result;
-> 'xyxyxabc'
⚠️ 주의할 점
(원본 문자열 길이 >= 지정 길이) 일 경우 문자열이 잘려서 결과가 반환이 됨.
SELECT LPAD('123456', 4, '0');
-> '1234' (잘림)- 채울 문자가 여러 글자인 경우, 지정 길이를 초과하지 않는 선에서 왼쪽부터 잘라 붙인다.
RPAD(문자열, 길이, 문자)
RPAD 함수는 LPAD 함수의 반대라고 생각하면된다.
SELECT RPAD('UGI', 6, '-') AS result;
->'UGI---'
SELECT RPAD('5', 3, '0') AS result;
-> '500'
SELECT RPAD('abc', 8, 'xy') AS result;
-> 'abcxyxyx'숫자 함수
ABS(수)
수의 절댓값을 구하는 함수이다.
입력한 ㅅ후가 음수일 경우, 양수로 바꿔서 반환하며 양수는 그대로 반환된다.
SELECT ABS(-10); -- 결과: 10
SELECT ABS(7); -- 결과: 7SIGN(수)
숫자의 부호(Sign)를 판단해서 음수는 -1, 0은 0, 양수는 1을 반환한다.
SELECT SIGN(-100); -- 결과: -1
SELECT SIGN(0); -- 결과: 0
SELECT SIGN(50); -- 결과: 1ROUND(수 [,자릿수])
숫자를 반올림해서 반환하는 함수이다.
두 번째 인자인 자릿수를 지정하면 소수점을 기준으로 반올림 위치를 설정할 수 있다.
-- 기본 문법
ROUND(숫자, 자릿수)
-- 자릿수는 생략 가능, 기본값은 0SELECT ROUND(3.567); -- 결과: 4
SELECT ROUND(3.567, 0); -- 결과: 4
SELECT ROUND(3.567, 1); -- 결과: 3.6
SELECT ROUND(3.567, 2); -- 결과: 3.57
SELECT ROUND(3.567, 3); -- 결과: 3.567
SELECT ROUND(3567, -2); -- 결과: 3600
SELECT ROUND(3567, -1); -- 결과: 3570- 자릿수에 음수를 주면 정수 자릿수에서 반올림함(-1이면 십의 자리에서 반올림)
TRUNC(수 [, 자릿수])
지정한 자릿수 이하를 잘라내는 함수(=버림)
즉, 반올림 없이 그냥 자른다는 특징을 가지고 있어 ROUND()와 비슷하지만 올림/내림 없이 무조건 절단한다고 보면 된다.
-- 기본 문법
TRUNC(숫자, 자릿수)
-- 자릿수 생략 시 0 (소수점 제거)SELECT TRUNC(3.567); -- 결과: 3
SELECT TRUNC(3.567, 1); -- 결과: 3.5
SELECT TRUNC(3.567, 2); -- 결과: 3.56
SELECT TRUNC(4567, -2); -- 결과: 4500CEIL(수)
올림(Ceiling)함수로 주어진 수보다 크거나 같은 가장 작은 정수를 반환한다.
SELECT CEIL(3.1); -- 결과: 4
SELECT CEIL(-3.1); -- 결과: -3- 생각해보니 페이지 수 계산에 편리할 듯 하다. (예: 게시글 수 51개 → 한 페이지당 10개 → CEIL(51 / 10) = 6페이지)
FLOOR(수)
내림(Floor)함수로 주어진 수보다 작거나 같은 가장 큰 정수를 반환한다.
SELECT FLOOR(3.9); -- 결과: 3
SELECT FLOOR(-3.9); -- 결과: -4MOD(수1, 수2)
나머지를 구하는 함수(modulo 연산) 수1을 수2로 나눴을 때 나머지 값을 반환함.
-- 기본 문법
MOD(피제수, 제수)SELECT MOD(10, 3); -- 결과: 1
SELECT MOD(15, 5); -- 결과: 0
SELECT MOD(-7, 4); -- 결과: -3 (MySQL 기준, DB에 따라 다를 수 있음)날짜 함수
SYSDATE/GETDATE() --> MSSQL
현재 시스템의 날짜와 시간을 반환하는 함수이다.
MySQL에선 NOW()와 거의 동일하게 동작하지만 Oracle에서는 약간의 차이가 있다.(평가 시점 기준)
EXTRACT(단위 FROM 날짜)
날짜로부터 원하는 값(년, 월, 일 등)을 추출하는 함수이다.
연도, 월, 일. 요일, 시, 분, 초, 등 다양한 단위를 지정할 수 있다.
| 📌 주요 단위 | |
| YEAR | 연도 |
| MONTH | 월 |
| DAY | 일 |
| HOUR | 시 |
| MINUTE | 분 |
| SECOND | 초 |
| WEEK | 주차 |
| QUARTER | 분기 |
| DAYOFWEEK | 요일(1=일요일) |
SELECT EXTRACT(YEAR FROM '2025-04-08') AS year; -- 2025
SELECT EXTRACT(MONTH FROM SYSDATE()) AS this_month; -- 4
SELECT EXTRACT(DAY FROM NOW()) AS day_today; -- 8ADD_MONTHS(날짜, 개월수)
특정 날짜에 개월 수를 더하거나 빼는 함수이다.
MySQL에서는 ADD_MONTHS()가 기본 내장되어 있지 않아 DATE_ADD()를 사용해 동일한 동작을 수행.
📌 MySQL에서 대체 문법
DATE_ADD(날짜, INTERVAL 개월수 MONTH)
SELECT DATE_ADD('2025-01-15', INTERVAL 2 MONTH);
-- 결과: '2025-03-15'
SELECT DATE_ADD(CURDATE(), INTERVAL -6 MONTH);
-- 결과: 오늘 기준 6개월 전 날짜
📌 Oracle 예시
SELECT ADD_MONTHS(SYSDATE, 1) FROM DUAL;
-- 한 달 후 날짜 반환DATEDIFF(날짜1, 날짜2)
두 날짜간의 차이를 일(day) 단위로 계산하는 함수이다.
DATEDIFF(날짜1, 날짜2)- 계산식: 날짜1 - 날짜2
- 결과: 정수 (양수, 음수 가능)
SELECT DATEDIFF('2025-04-10', '2025-04-08'); -- 결과: 2
SELECT DATEDIFF(NOW(), '2025-01-01'); -- 오늘부터 며칠 지났는지NOW()
현재 날짜와 시간을 반환하는 함수이다.
- 결과 형식: 'YYYY-MM-DD HH:MM:SS'
SELECT NOW();
-- 결과: '2025-04-08 09:42:15'CURDATE()
현재 날짜만 반환하는 함수이다.(시간제외)
- 결과 형식: 'YYYY-MM-DD'
SELECT CURDATE();
-- 결과: '2025-04-08'DATE_FORMAT(날짜, 형식)
날짜/시간을 원하는 형식의 문자열로 변환해주는 함수이다.
DATE_FORMAT(날짜, '포맷문자열')| 포맷 기호 | 의미 |
| %Y | 4자리 연도 (2025) |
| %y | 2자리 연도(25) |
| %m | 월 (01~12) |
| %d | 일 (01~31) |
| %H | 시 (00~23) |
| %i | 분 |
| %s | 초 |
SELECT DATE_FORMAT(NOW(), '%Y-%m-%d'); -- '2025-04-08'
SELECT DATE_FORMAT(NOW(), '%Y년 %m월 %d일'); -- '2025년 04월 08일'
SELECT DATE_FORMAT(NOW(), '%H:%i:%s'); -- '09:45:00'변환함수(형변환/ 데이터 타입 변환)
SQL에서 형병환(Type Casting)은 한 데이터 타입을 다른 타입으로 바꿔서 처리하는 것을 말한다.
예를들어 문자열을 숫자로 바꾸거나, 숫자를 날짜로 바꾸는 것 처럼 말이다.
명시적 형변환(Explicit Casting)
사용자가 직접 지정해서 데이터 타입을 변환하는 방식을 말한다.
📌 MySQL 문법
CAST(표현식 AS 데이터형)
CONVERT(표현식, 데이터형)
📌 예제
SELECT CAST('123' AS UNSIGNED); -- 문자열 → 숫자
SELECT CAST('2025-04-08' AS DATE); -- 문자열 → 날짜
SELECT CONVERT('3.14', DECIMAL(5,2)); -- 문자열 → 소수
언제 쓰나?
- 타입이 확실하지 않은 값을 처리할 때 ('123' -> 숫자)
- 데이터 형식을 강제 맞춤이 필요할 때 (예: JOIN시 컬럼 타입이 다를 때)
- 날짜, 숫자, 문자열 포맷 정제
STR_TO_DATE(문자열, 포맷) -(MySQL)
문자열을 날짜 형식으로 변환하는 함수이다.
STR_TO_DATE('2025-04-08', '%Y-%m-%d')| 포맷 | 의미 |
| %Y | 4자리 연도 |
| %y | 2자리 연도 |
| %m | 월 |
| %d | 일 |
| %H | 시 (24h) |
| %i | 분 |
| %s | 초 |
SELECT STR_TO_DATE('2025-04-08 14:30:00', '%Y-%m-%d %H:%i:%s');
-- 결과: '2025-04-08 14:30:00' (DATETIME 타입)TO_DATE(문자열, 포맷) -(Oracle)
문자열을 날짜로 변환해주는 Oracle 전용 함수이다.
SELECT TO_DATE('20250408', 'YYYYMMDD') FROM DUAL;
-- 결과: 2025-04-08포맷 형식은 MySQL과 거의 유사하지만, % 없이 사용됨
TO_CHAR(값, 포맷) -(Oracle)
날짜나 숫자를 문자열로 변환하는 함수이다.
특정 포맷 형식을 지정해 출력 형태를 제어할 수 있다.
📌 날짜 → 문자열
TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS')
📌 숫자 → 문자열
TO_CHAR(12345.67, '999,999.00')
-- 결과: ' 12,345.67'
📌 예제
SELECT TO_CHAR(TO_DATE('20250408', 'YYYYMMDD'), 'YYYY/MM/DD') FROM DUAL;
-- 결과: '2025/04/08'TO_NUMBER(문자열 [, 포맷]) -(Oracle)
문자열을 숫자로 변환하는 함수
SELECT TO_NUMBER('1500') + 500 FROM DUAL;
-- 결과: 2000
암시적 형변환(Implicit Casting)
SQL 엔진이 자동으로 데이터 타입을 변환해주는 방식을 말한다.
사용자는 형변환을 하지 않지만, SQL이 내부적으로 판단해서 처리해준다.
하지만 암시적 형변환이 가능하다고 해서 컬럼의 데이터 유형을 고려하지 않고 SQL 작성을 하게되면 성능 저하를 불러올 수도 있고 때에 따라서는 에러를 뱉는 경우도 있기 때문에 되도록이면 명시적 형변환을 사용하자.
📌 예제
SELECT 100 + '200'; -- 결과: 300 (문자열 '200'이 숫자로 변환됨)
SELECT '2025-04-08' + 0; -- 날짜 문자열이 숫자로 암시적 변환됨
📌 주의사항
SELECT '123abc' + 1; -- 결과: 124- 암시적 변환은 의도하지 않은 결과를 유발할 수 있어 조심하자.
- 문자열에 숫자가 섞여 있을 경우, 숫자 앞까지만 잘라서 변환한다.
NULL 함수
NVL(표현식, 대체값) -(Oracle)
표현식이 NULL이면 대체값으로 반환하며 NULL이 아니면 원래 값 반환하는 함수이다.
SELECT NVL(NULL, '기본값'); -- 결과: '기본값'
SELECT NVL('우기', '기본값'); -- 결과: '우기'NVL2(표현식, NOT_NULL일때, NULL일때) -(Oracle)
표현식이 NULL이 아니면 두 번째 값 반환, NULL이면 세 번째 값을 반환하는 함수로서
IF ... THEN ... ELSE 느낌의 3항 구조이다.
SELECT NVL2('우기', '있음', '없음'); -- 결과: '있음'
SELECT NVL2(NULL, '있음', '없음'); -- 결과: '없음'NULLIF(값1, 값2) -(MySQL, Oracle, 표준 SQL)
두 값이 같으면 NULL 반환, 다르면 값1 반환하는 함수로서 주로 0으로 나누는 에러 회피, 조건적 NULL 처리에 사용한다.
SELECT NULLIF(100, 100); -- 결과: NULL
SELECT NULLIF(100, 200); -- 결과: 100
-- 실무 활용
SELECT salary / NULLIF(bonus, 0) FROM emp;
-- 분모가 0일 때 NULL로 바꿔 에러 방지COALESCE(값1, 값2, ..., 값N) -(MySQL, Oracle, 표준 SQL)
입력값들 중 NULL이 아닌 첫 번째 값을 반환하는 함수로서 여러 대안 중 하나를 선택할 수 있다는 장점이 있다.
SELECT COALESCE(NULL, NULL, '우기', '기본값'); -- 결과: '우기'
SELECT COALESCE(NULL, 0, 100); -- 결과: 0
-- 실무 활용
SELECT COALESCE(username, '비회원') FROM users; -- 사용자 이름이 없을 때 '비회원' 표시추가로 알아두면 좋은 함수들
| IS NULL, IS NOT NULL | NULL 여부를 조건을 검사 |
| CASE WHEN ... IS NULL THEN ... | NULL 조건 제어 로직 작성 가능 |
| IF(조건, 참값, 거짓값) | MySQL 전용 삼항 조건 함수로 NULL제어 가능 |
CASE
CASE는 조건에 따라 다른 값을 반환하는 SQL의 조건문으로서 프로그래밍 언어의 if, switch 문과 비슷한 역할을 한다.
-- 단순 CASE 표현식 (값 비교)
CASE 비교할_값
WHEN 값1 THEN 결과1
WHEN 값2 THEN 결과2
...
ELSE 기본값
END
-- 예제
SELECT name,
CASE grade
WHEN 'A' THEN '우수'
WHEN 'B' THEN '보통'
ELSE '재시험'
END AS 평가결과
FROM students;-- 검색 CASE 표현식 (조건 비교)
CASE
WHEN 조건1 THEN 결과1
WHEN 조건2 THEN 결과2
...
ELSE 기본값
END
-- 예제
SELECT name,
CASE
WHEN score >= 90 THEN 'A'
WHEN score >= 80 THEN 'B'
WHEN score >= 70 THEN 'C'
ELSE 'F'
END AS 등급
FROM students;
CASE 활용 예시
-- 급여 등급 나누기
SELECT name, salary,
CASE
WHEN salary >= 5000 THEN '고액'
WHEN salary >= 3000 THEN '중간'
ELSE '저소득'
END AS 급여구간
FROM employees;
-- NULL 처리 + 조건 조합
SELECT name,
CASE
WHEN email IS NULL THEN '이메일 없음'
ELSE email
END AS 이메일상태
FROM users;
-- 그룹핑 결과 포맷 변경
SELECT
CASE gender
WHEN 'M' THEN '남자'
WHEN 'F' THEN '여자'
ELSE '기타'
END AS 성별,
COUNT(*) AS 인원수
FROM users
GROUP BY gender;
주의 사항
- ELSE 생략 가능 : 생략하면 조건 불일치 시 NULL 반환
- CASE는 SELECT, WHERE, ORDER BY, GROUP BY 등 모든 절에서 사용가능
- 조건 순서 매우 중요 : 위에서 아래로 평가되며, 먼저 걸리면 끝
DECODE -(Oracle)
CASE와 같은 기능을 하는 함수로 간단한 값 비교에 매우 유용하지만 복잡한 조건 처리에는 CASE가 더 적합하다.
DECODE는 expr 값이 특정 값과 같을 경우 해당하는 결과값을 반환하는 단순 조건 분기 함수이다.
일종의 간단한 Switch-case라고 보면 된다.
-- 기본 문법
DECODE(표현식, 조건값1, 결과값1, 조건값2, 결과값2, ..., 기본값)- 표현식이 조건값들과 순서대로 비교된다.
- 조건이 일치하면 해당 결과값을 반환한다.
- 어떤 조건도 만족하지 않으면 마지막 기본값 반환이 가능하다(생략 가능)
SELECT
name,
DECODE(gender,
'M', '남자',
'F', '여자',
'기타') AS 성별
FROM users;
-- gender가 'M'이면 '남자', 'F'면 '여자' 그 외엔 '기타'
SELECT
score,
DECODE(score,
100, '만점',
0, '낙제',
'일반') AS 평가
FROM exam;
-- 100점이면 '만점', 0점이면 '낙제', 나머지는 '일반'
그냥 딱 봐서 DECODE로 구현하기 조금 복잡한거 같다 싶으면 CASE를 쓰자.
'SQL > SQLD' 카테고리의 다른 글
| #12 GROUP BY, HAVING 절 (0) | 2025.04.13 |
|---|---|
| #11 WHERE 절 (0) | 2025.04.10 |
| #9 SQL (0) | 2025.04.07 |
| #8 트랜잭션과 NULL (0) | 2025.04.07 |
| #7 반정규화(De-Normalization) (0) | 2025.04.06 |