데이터베이스 개발자나 DB 관리자라면 MS-SQL 함수들을 제대로 알고 있어야 효율적인 쿼리 작성이 가능합니다. 특히 데이터 분석이나 리포트 작성 시 함수의 중요성은 더욱 커집니다. 이번 포스트에서는 MS-SQL에서 가장 많이 사용되는 필수 함수들을 카테고리별로 정리하고, 실무에서 바로 사용할 수 있는 예제와 함께 알아보겠습니다.
MS-SQL Server 2022를 기준으로 설명드리지만, 대부분의 함수는 이전 버전에서도 동일하게 사용 가능합니다. 🙂
1. 문자열 함수 (String Functions)
문자열 데이터를 다루는 일은 SQL 작업에서 가장 빈번하게 발생합니다. MS-SQL에서는 문자열 조작을 위한 다양한 함수를 제공합니다.
1.1 SUBSTRING – 문자열 일부 추출
-- 구문: SUBSTRING(문자열, 시작위치, 길이)
SELECT SUBSTRING('안녕하세요', 1, 2) AS 결과; -- '안녕' 반환
SUBSTRING 함수는 문자열에서 지정한 위치부터 지정한 길이만큼의 문자를 추출합니다. 첫 번째 위치는 1부터 시작합니다.
1.2 LEFT와 RIGHT – 좌우에서 문자 추출
-- 좌측에서 N개 문자 추출
SELECT LEFT('SQL Server', 3) AS 결과; -- 'SQL' 반환
-- 우측에서 N개 문자 추출
SELECT RIGHT('SQL Server', 6) AS 결과; -- 'Server' 반환
LEFT는 문자열 왼쪽부터, RIGHT는 오른쪽부터 지정한 개수만큼 문자를 추출합니다.
1.3 LEN – 문자열 길이 확인
SELECT LEN('안녕하세요') AS 결과; -- 5 반환
LEN 함수는 문자열의 문자 수를 반환합니다. 공백을 포함한 길이를 반환하지만, 끝의 공백은 무시합니다.
1.4 LTRIM과 RTRIM – 공백 제거
-- 좌측 공백 제거
SELECT LTRIM(' SQL ') AS 결과; -- 'SQL ' 반환
-- 우측 공백 제거
SELECT RTRIM(' SQL ') AS 결과; -- ' SQL' 반환
-- 양쪽 공백 제거 (SQL Server 2017 이상)
SELECT TRIM(' SQL ') AS 결과; -- 'SQL' 반환
LTRIM은 왼쪽, RTRIM은 오른쪽 공백을 제거합니다. SQL Server 2017부터는 TRIM 함수를 사용하여 양쪽 공백을 한 번에 제거할 수 있습니다.
1.5 REPLACE – 문자열 대체
-- 구문: REPLACE(문자열, 찾을문자열, 대체문자열)
SELECT REPLACE('SQL Server 2019', '2019', '2022') AS 결과; -- 'SQL Server 2022' 반환
REPLACE 함수는 문자열 내의 특정 부분을 다른 문자열로 대체합니다.
1.6 CONCAT – 문자열 연결
-- 구문: CONCAT(문자열1, 문자열2, ...)
SELECT CONCAT('SQL ', 'Server ', '2022') AS 결과; -- 'SQL Server 2022' 반환
-- + 연산자를 사용한 연결 (전통적인 방법)
SELECT 'SQL ' + 'Server ' + '2022' AS 결과; -- 'SQL Server 2022' 반환
CONCAT 함수는 SQL Server 2012부터 도입되었으며, 여러 문자열을 하나로 연결합니다. NULL 값은 빈 문자열로 처리되는 장점이 있습니다.
1.7 UPPER와 LOWER – 대소문자 변환
SELECT UPPER('sql server') AS 대문자; -- 'SQL SERVER' 반환
SELECT LOWER('SQL SERVER') AS 소문자; -- 'sql server' 반환
UPPER는 모든 문자를 대문자로, LOWER는 모든 문자를 소문자로 변환합니다.
1.8 STUFF – 문자열 삽입 및 대체
-- 구문: STUFF(문자열, 시작위치, 삭제길이, 삽입문자열)
SELECT STUFF('SQL Server 2019', 12, 4, '2022') AS 결과; -- 'SQL Server 2022' 반환
STUFF 함수는 문자열의 지정한 위치부터 특정 길이를 삭제하고 새로운 문자열을 삽입합니다.
2. 날짜 및 시간 함수 (Date and Time Functions)
날짜와 시간 데이터는 업무 시스템에서 매우 중요한 부분입니다. MS-SQL은 다양한 날짜/시간 관련 함수를 제공합니다.
2.1 GETDATE – 현재 날짜와 시간
SELECT GETDATE() AS 현재시간; -- 예: '2025-05-20 14:30:25.123' 반환
GETDATE 함수는 시스템의 현재 날짜와 시간을 반환합니다.
2.2 DATEADD – 날짜 더하기/빼기
-- 구문: DATEADD(단위, 더할값, 날짜)
-- 1개월 후
SELECT DATEADD(MONTH, 1, GETDATE()) AS 한달후;
-- 7일 전
SELECT DATEADD(DAY, -7, GETDATE()) AS 일주일전;
-- 1년 후
SELECT DATEADD(YEAR, 1, GETDATE()) AS 일년후;
DATEADD 함수는 지정한 날짜에 특정 기간을 더하거나 뺍니다. 단위로는 YEAR, QUARTER, MONTH, DAY, HOUR, MINUTE, SECOND 등을 사용할 수 있습니다.
2.3 DATEDIFF – 날짜 간 차이 계산
-- 구문: DATEDIFF(단위, 시작날짜, 종료날짜)
-- 두 날짜 사이의 일수 차이
SELECT DATEDIFF(DAY, '2023-01-01', '2023-12-31') AS 일수차이; -- 364 반환
-- 두 날짜 사이의 월수 차이
SELECT DATEDIFF(MONTH, '2023-01-15', '2023-05-10') AS 월수차이; -- 4 반환
DATEDIFF 함수는 두 날짜 간의 차이를 지정한 단위로 계산합니다.
2.4 DATEPART – 날짜의 특정 부분 추출
-- 구문: DATEPART(단위, 날짜)
-- 연도 추출
SELECT DATEPART(YEAR, GETDATE()) AS 연도;
-- 월 추출
SELECT DATEPART(MONTH, GETDATE()) AS 월;
-- 요일 추출 (1=일요일, 2=월요일, ..., 7=토요일)
SELECT DATEPART(WEEKDAY, GETDATE()) AS 요일;
DATEPART 함수는 날짜에서 특정 부분(연, 월, 일, 시간 등)을 추출합니다.
2.5 DATENAME – 날짜 부분의 이름 반환
-- 구문: DATENAME(단위, 날짜)
-- 월 이름 반환 (영문)
SELECT DATENAME(MONTH, GETDATE()) AS 월이름; -- 예: 'May' 반환
-- 요일 이름 반환 (영문)
SELECT DATENAME(WEEKDAY, GETDATE()) AS 요일이름; -- 예: 'Tuesday' 반환
DATENAME 함수는 날짜의 특정 부분에 대한 이름을 반환합니다.
2.6 FORMAT – 날짜 형식 지정
-- 구문: FORMAT(날짜, 형식)
SELECT FORMAT(GETDATE(), 'yyyy-MM-dd') AS 날짜; -- 예: '2025-05-20' 반환
SELECT FORMAT(GETDATE(), 'yyyy년 MM월 dd일') AS 한글날짜; -- 예: '2025년 05월 20일' 반환
FORMAT 함수는 SQL Server 2012부터 도입되었으며, 날짜를 원하는 형식으로 변환할 수 있습니다.
3. 수학(산술) 함수 (Mathematical Functions)
SQL에서 수치 계산을 위한 다양한 수학 함수도 제공합니다.
3.1 ABS – 절대값
SELECT ABS(-123) AS 절대값; -- 123 반환
ABS 함수는 숫자의 절대값을 반환합니다.
3.2 ROUND – 반올림
-- 구문: ROUND(숫자, 소수자리수)
SELECT ROUND(123.456, 2) AS 반올림; -- 123.46 반환
SELECT ROUND(123.456, 0) AS 정수반올림; -- 123 반환
SELECT ROUND(123.456, -1) AS 십단위반올림; -- 120 반환
ROUND 함수는 지정한 소수 자릿수까지 숫자를 반올림합니다. 세 번째 매개변수로 0을 지정하면 반올림, 1을 지정하면 버림을 수행합니다.
3.3 CEILING과 FLOOR – 올림과 내림
SELECT CEILING(123.45) AS 올림; -- 124 반환
SELECT FLOOR(123.45) AS 내림; -- 123 반환
CEILING은 숫자보다 크거나 같은 가장 작은 정수를, FLOOR는 숫자보다 작거나 같은 가장 큰 정수를 반환합니다.
3.4 POWER – 거듭제곱
-- 구문: POWER(숫자, 지수)
SELECT POWER(2, 3) AS 거듭제곱; -- 8 반환 (2의 3승)
POWER 함수는 숫자의 거듭제곱을 계산합니다.
3.5 SQRT – 제곱근
SELECT SQRT(16) AS 제곱근; -- 4 반환
SQRT 함수는 숫자의 제곱근을 반환합니다.
3.6 RAND – 난수 생성
-- 0~1 사이의 난수 생성
SELECT RAND() AS 난수;
-- 1~100 사이의 정수 난수 생성
SELECT FLOOR(RAND() * 100) + 1 AS 정수난수;
RAND 함수는 0과 1 사이의 난수를 생성합니다. 범위를 조정하여 원하는 범위의 난수를 생성할 수 있습니다.
4. 집계 함수 (Aggregate Functions)
집계 함수는 데이터 분석과 요약에 필수적인 함수들입니다.
4.1 COUNT – 행 개수 세기
-- 모든 행 개수
SELECT COUNT(*) FROM 테이블명;
-- NULL이 아닌 특정 열의 값 개수
SELECT COUNT(열이름) FROM 테이블명;
-- 중복을 제외한 고유 값 개수
SELECT COUNT(DISTINCT 열이름) FROM 테이블명;
COUNT 함수는 지정한 조건에 맞는 행의 개수를 반환합니다. COUNT(*)는 모든 행을, COUNT(열이름)은 NULL이 아닌 값을 가진 행의 개수를 반환합니다.
4.2 SUM – 합계
SELECT SUM(금액) AS 총합계 FROM 주문;
SUM 함수는 지정한 열의 값들의 합계를 반환합니다. NULL 값은 무시됩니다.
4.3 AVG – 평균
SELECT AVG(금액) AS 평균금액 FROM 주문;
AVG 함수는 지정한 열의 값들의 평균을 반환합니다. NULL 값은 계산에서 제외됩니다.
4.4 MIN과 MAX – 최소값과 최대값
SELECT MIN(금액) AS 최소금액, MAX(금액) AS 최대금액 FROM 주문;
MIN은 최소값을, MAX는 최대값을 반환합니다.
4.5 GROUP BY와 HAVING 활용
집계 함수는 주로 GROUP BY와 함께 사용하여 데이터를 그룹화하고 각 그룹별로 집계합니다.
-- 그룹별 집계
SELECT 카테고리, COUNT(*) AS 개수, SUM(금액) AS 총액
FROM 주문
GROUP BY 카테고리;
-- 조건 필터링
SELECT 카테고리, COUNT(*) AS 개수, SUM(금액) AS 총액
FROM 주문
GROUP BY 카테고리
HAVING SUM(금액) > 10000;
GROUP BY는 지정한 열의 값이 같은 행을 그룹화하고, HAVING은 집계 결과에 대한 조건을 지정합니다.
5. 변환 함수 (Conversion Functions)
데이터 타입 간 변환이 필요할 때 사용하는 함수들입니다.
5.1 CAST – 데이터 타입 변환
-- 구문: CAST(표현식 AS 데이터타입)
SELECT CAST(123.45 AS INT) AS 정수변환; -- 123 반환
SELECT CAST('2023-05-01' AS DATE) AS 날짜변환; -- 2023-05-01 반환
CAST 함수는 표현식을 지정한 데이터 타입으로 변환합니다. ANSI SQL 표준을 준수하는 방식입니다.
5.2 CONVERT – 데이터 타입 변환 (스타일 지정)
-- 구문: CONVERT(데이터타입, 표현식 [, 스타일])
-- 날짜를 문자열로 변환 (스타일 103: 영국/프랑스 형식 dd/mm/yyyy)
SELECT CONVERT(VARCHAR, GETDATE(), 103) AS 날짜변환; -- '20/05/2025' 반환
-- 문자열을 날짜로 변환
SELECT CONVERT(DATE, '2023-05-01') AS 날짜변환; -- 2023-05-01 반환
CONVERT 함수는 SQL Server에서만 제공하는 함수로, 스타일 매개변수를 통해 변환 형식을 지정할 수 있습니다.
5.3 TRY_CAST와 TRY_CONVERT – 안전한 변환
-- 실패시 NULL 반환
SELECT TRY_CAST('abc' AS INT) AS 안전변환; -- NULL 반환
SELECT TRY_CONVERT(INT, 'abc') AS 안전변환; -- NULL 반환
TRY_CAST와 TRY_CONVERT 함수는 변환이 실패할 경우 오류 대신 NULL을 반환하여 쿼리가 중단되지 않도록 합니다.
6. 논리 함수 (Logical Functions)
논리적인 조건 처리를 위한 함수들입니다.
6.1 ISNULL – NULL 값 대체
-- 구문: ISNULL(검사값, 대체값)
SELECT ISNULL(NULL, '기본값') AS 결과; -- '기본값' 반환
SELECT ISNULL('값이 있음', '기본값') AS 결과; -- '값이 있음' 반환
ISNULL 함수는 첫 번째 인수가 NULL이면 두 번째 인수를 반환하고, 그렇지 않으면 첫 번째 인수를 그대로 반환합니다.
6.2 COALESCE – 첫 번째 NULL이 아닌 값 반환
-- 구문: COALESCE(표현식1, 표현식2, ...)
SELECT COALESCE(NULL, NULL, '세 번째 값', '네 번째 값') AS 결과; -- '세 번째 값' 반환
COALESCE 함수는 여러 표현식을 평가하여 첫 번째로 NULL이 아닌 값을 반환합니다.
6.3 CASE – 조건부 로직
-- 단순 CASE
SELECT
고객이름,
CASE 등급
WHEN 'A' THEN '최우수'
WHEN 'B' THEN '우수'
WHEN 'C' THEN '일반'
ELSE '신규'
END AS 등급명
FROM 고객;
-- 검색 CASE
SELECT
제품명,
CASE
WHEN 가격 >= 10000 THEN '고가'
WHEN 가격 >= 5000 THEN '중가'
ELSE '저가'
END AS 가격대
FROM 제품;
CASE 표현식은 다양한 조건에 따라 다른 값을 반환하는 데 사용됩니다. 단순 CASE는 특정 표현식의 값을 비교하고, 검색 CASE는 각 WHEN 절에서 개별 조건을 평가합니다.
6.4 IIF – 간단한 조건 평가
-- 구문: IIF(조건, 참일때값, 거짓일때값)
SELECT IIF(가격 > 5000, '고가', '저가') AS 가격대 FROM 제품;
IIF 함수는 SQL Server 2012부터 도입되었으며, 간단한 조건 평가에 사용할 수 있습니다.
7. 윈도우 함수 (Window Functions)
윈도우 함수는 행 간의 관계를 쉽게 정의할 수 있는 강력한 기능을 제공합니다.
7.1 ROW_NUMBER – 행 번호 부여
-- 구문: ROW_NUMBER() OVER([PARTITION BY 열] ORDER BY 열)
-- 전체 데이터에 행 번호 부여
SELECT
ROW_NUMBER() OVER(ORDER BY 판매액 DESC) AS 순위,
제품명,
판매액
FROM 판매;
-- 카테고리별로 행 번호 부여
SELECT
카테고리,
ROW_NUMBER() OVER(PARTITION BY 카테고리 ORDER BY 판매액 DESC) AS 카테고리내순위,
제품명,
판매액
FROM 판매;
ROW_NUMBER 함수는 정렬된 결과 집합의 각 행에 연속적인 번호를 할당합니다.
7.2 RANK – 순위 부여 (동점 처리)
SELECT
RANK() OVER(ORDER BY 판매액 DESC) AS 순위,
제품명,
판매액
FROM 판매;
RANK 함수는 정렬된 결과 집합의 각 행에 순위를 할당합니다. 동일한 값은 같은 순위를 받고, 다음 순위는 건너뜁니다(예: 1,2,2,4).
7.3 DENSE_RANK – 빈틈 없는 순위 부여
SELECT
DENSE_RANK() OVER(ORDER BY 판매액 DESC) AS 순위,
제품명,
판매액
FROM 판매;
DENSE_RANK 함수는 RANK와 유사하지만, 순위에 빈틈이 생기지 않습니다(예: 1,2,2,3).
7.4 NTILE – 그룹으로 분할
-- 구문: NTILE(그룹수) OVER(ORDER BY 열)
-- 데이터를 4개 그룹으로 분할
SELECT
NTILE(4) OVER(ORDER BY 판매액 DESC) AS 분위수,
제품명,
판매액
FROM 판매;
NTILE 함수는 정렬된 데이터를 지정한 수의 그룹으로 균등하게 분할합니다.
7.5 LAG와 LEAD – 이전/이후 행 참조
-- 이전 행의 값 참조
SELECT
날짜,
판매액,
LAG(판매액) OVER(ORDER BY 날짜) AS 전일판매액,
판매액 - LAG(판매액) OVER(ORDER BY 날짜) AS 증감액
FROM 일별판매;
-- 이후 행의 값 참조
SELECT
날짜,
판매액,
LEAD(판매액) OVER(ORDER BY 날짜) AS 다음날판매액
FROM 일별판매;
LAG 함수는 현재 행 이전의 행 값을, LEAD 함수는 현재 행 이후의 행 값을 참조합니다.
8. 시스템 함수 (System Functions)
데이터베이스 시스템 정보와 관련된 다양한 함수들입니다.
8.1 @@VERSION – SQL Server 버전 확인
SELECT @@VERSION AS SQL서버버전;
@@VERSION은 현재 SQL Server의 버전, 빌드 번호 등 상세 정보를 반환합니다.
8.2 DB_NAME – 현재 데이터베이스 이름
SELECT DB_NAME() AS 현재DB;
DB_NAME 함수는 현재 사용 중인 데이터베이스의 이름을 반환합니다.
8.3 USER_NAME – 현재 사용자 이름
SELECT USER_NAME() AS 현재사용자;
USER_NAME 함수는 현재 연결된 사용자의 이름을 반환합니다.
8.4 HOST_NAME – 클라이언트 컴퓨터 이름
SELECT HOST_NAME() AS 클라이언트호스트;
HOST_NAME 함수는 클라이언트 컴퓨터의 이름을 반환합니다.
9. 실제 활용 예제
이제 위에서 배운 함수들을 조합하여 실무에서 자주 사용되는 예제를 살펴보겠습니다.
9.1 거래 내역 요약 리포트
SELECT
DATEPART(YEAR, 거래일자) AS 연도,
DATEPART(MONTH, 거래일자) AS 월,
COUNT(*) AS 거래건수,
SUM(금액) AS 총금액,
AVG(금액) AS 평균금액,
MIN(금액) AS 최소금액,
MAX(금액) AS 최대금액
FROM 거래
GROUP BY DATEPART(YEAR, 거래일자), DATEPART(MONTH, 거래일자)
ORDER BY 연도, 월;
이 쿼리는 거래 데이터를 연도와 월별로 요약하여 거래 건수, 총금액, 평균금액 등을 계산합니다.
9.2 순위 기반 상위 고객 목록
WITH 고객구매금액 AS (
SELECT
고객ID,
고객명,
SUM(금액) AS 총구매액,
COUNT(*) AS 구매횟수,
AVG(금액) AS 평균구매액
FROM 거래
JOIN 고객 ON 거래.고객ID = 고객.ID
WHERE 거래일자 >= DATEADD(MONTH, -6, GETDATE())
GROUP BY 고객ID, 고객명
)
SELECT
ROW_NUMBER() OVER(ORDER BY 총구매액 DESC) AS 순위,
고객ID,
고객명,
FORMAT(총구매액, '#,###') AS 총구매액,
구매횟수,
FORMAT(평균구매액, '#,###') AS 평균구매액
FROM 고객구매금액
WHERE 구매횟수 >= 3
ORDER BY 총구매액 DESC;
이 쿼리는 최근 6개월간 3회 이상 구매한 고객의 구매 금액 순위를 계산합니다.
9.3 월별 성장률 분석
WITH 월별매출 AS (
SELECT
DATEPART(YEAR, 거래일자) AS 연도,
DATEPART(MONTH, 거래일자) AS 월,
SUM(금액) AS 월매출
FROM 거래
GROUP BY DATEPART(YEAR, 거래일자), DATEPART(MONTH, 거래일자)
)
SELECT
연도,
월,
월매출,
LAG(월매출) OVER(ORDER BY 연도, 월) AS 이전월매출,
CASE
WHEN LAG(월매출) OVER(ORDER BY 연도, 월) = 0 THEN NULL
ELSE FORMAT((월매출 - LAG(월매출) OVER(ORDER BY 연도, 월)) / LAG(월매출) OVER(ORDER BY 연도, 월) * 100, '0.00')
END AS 성장률
FROM 월별매출
ORDER BY 연도, 월;
이 쿼리는 월별 매출과 이전 월 대비 성장률을 계산합니다.
10. 마무리하며… (사용하며 약간 신경써야 할 부분)
MS-SQL의 다양한 함수들을 사용하면 복잡한 쿼리도 효율적으로 작성할 수 있습니다. 그러나 몇 가지 주의해야 할 점이 있습니다.
함수 사용 시 주의사항
- 인덱스 활용: 열에 함수를 적용하면 인덱스를 활용하지 못할 수 있습니다. 가능하면 WHERE 절에서는 열에 직접 함수를 적용하지 않도록 주의하세요.
- 암시적 변환 주의: 데이터 타입 간 암시적 변환은 성능 저하나 예기치 않은 결과를 초래할 수 있습니다. 명시적으로 CAST나 CONVERT를 사용하는 것이 좋습니다.
- NULL 처리 확인: 대부분의 함수는 NULL 입력에 대해 NULL을 반환합니다. NULL 값 처리 방식을 잘 이해하고 필요시 ISNULL 또는 COALESCE를 사용하세요.
- 버전 호환성 확인: 일부 함수는 특정 SQL Server 버전에서만 사용 가능합니다. 코드의 호환성을 위해 사용 중인 버전에서 지원하는 함수인지 확인하세요
이상으로 많이 사용되고 유용한 MS-SQL 함수를 정리해 보았습니다. 이번 포스트를 참고하여 MSSQL 함수를 편리하게 사용해 보시기를 추천드립니다. 🙂