SQL 기초 - 11. 함수 (스칼라 함수, 문자열 함수, 데이터 타입, 날짜와 시간)

이 글은 '김상형의 SQL 정복'의 예제 코드를 설명하는 방식으로 SQL 쿼리 예제를 소개하고 있습니다.

 

SQL 기초 - 11. 함수 (스칼라 함수, 문자열 함수, 데이터 타입, 날짜와 시간)

사실 DBMS마다 함수도 다르고 종류도 다르다. 그리고 모든 함수를 전부 외울 필요는 없다. 대략적으로 어떤 함수가 존재하는지를 파악해 둔 후, 필요한 함수를 그때 그때 찾아서 쓰는 방식이 더 효율적일 수도 있다! 이 글에서는 오라클을 기준으로 자주 나오는 함수 목록을 소개하며, 예시를 통해 설명한다.

 

수치함수

 

문자열 함수

 

데이터 타입

    1. 수치형

   

    2. 문자형

    3. 날짜형

    4. ANSI 표준 타입

-- AVG, TRIM, ROUND 등의 간단한 연산자는 직접 실습해보자.
SELECT AVG(score) FROM tStaff WHERE depart = '영업부';

SELECT ROUND(AVG(score), 2) FROM tStaff WHERE depart = '영업부';

SELECT name, REPLACE(depart, '부', '팀') FROM tStaff;

SELECT name, grade, salary FROM tStaff WHERE depart='인사과';

SELECT TRIM(name) || ' ' || grade, salary FROM tStaff WHERE depart='인사과';

MSSQL : SELECT TRIM(name) + ' ' + grade, salary FROM tStaff WHERE depart='인사과';
마리아 : SELECT CONCAT(name, ' ', grade), salary FROM tStaff WHERE depart='인사과';

SELECT ROUND(1234.5678, 0) FROM dual;	-- 1235
SELECT ROUND(1234.5678, 1) FROM dual;	-- 1234.6
SELECT ROUND(1234.5678, 2) FROM dual; 	-- 1234.57

SELECT name, area, ROUND(area, -2) FROM tCity;

-- LENGTH는 실제 문자열을, LENGTHB는 바이트를 체크한다.
-- 한글은 한 글자가 3바이트라서 17바이트가 나온다.
SELECT LENGTH('korea대한민국') FROM dual;		-- 9
SELECT LENGTHB('korea대한민국') FROM dual;		-- 17

SELECT * FROM tItem WHERE LENGTH(item) = 2;

-- 오라클에서는 CONCAT과  '||'을 둘 다 활용할 수 있다.
SELECT CONCAT(region, name) FROM tCity;

오라클 : SELECT region || name FROM tCity;
MSSQL : SELECT region + name FROM tCity;

SELECT region || '도의 ' || name FROM tCity;

SELECT CONCAT(CONCAT(region, '도의 '), name) FROM tCity;
MSSQL, 마리아 : SELECT CONCAT(region, '도의 ' , name) FROM tCity;

-- INSTR 은 해당 문자열의 시작 위치가 어디인지를 검사한다.
-- 이때, 인덱스는 1부터 시작함에 유의하자.
SELECT INSTR('우리나라 대한민국', '나라') FROM dual;      -- 3
SELECT INSTR('우리나라 대한민국', '민족') FROM dual;      -- 0

SELECT INSTR('국민에 의한 국민을 위한 국민의 국민당', '국민', 3) FROM dual;     -- 8
SELECT INSTR('국민에 의한 국민을 위한 국민의 국민당', '국민', 1, 3) FROM dual;  -- 15
SELECT INSTR('국민에 의한 국민을 위한 국민의 국민당', '국민', -1) FROM dual;    -- 19

MSSQL : SELECT CHARINDEX('국민', '국민에 의한 국민을 위한 국민의 국민당', 3);     -- 8
마리아 : SELECT POSITION('국민' IN '국민에 의한 국민을 위한 국민의 국민당');     -- 1

SELECT SUBSTR('아름다운 대한민국 금수강산', 6, 4) FROM dual;    -- 대한민국
SELECT SUBSTR('아름다운 대한민국 금수강산', -4, 2) FROM dual;   -- 금수

SELECT SUBSTR(name,1,1), COUNT(*) FROM tStaff GROUP BY SUBSTR(name,1,1) 
ORDER BY COUNT(*) DESC;

SELECT SUBSTR('...이름:홍길동,...', INSTR('...이름:홍길동,...','이름') + 3, 3) FROM dual;

SELECT LOWER('wonderful SQL') FROM dual; -- wonderful sql
SELECT UPPER('wonderful SQL') FROM dual; -- WONDERFUL SQL
SELECT INITCAP('wonderful SQL') FROM dual; -- Wonderful Sql

SELECT * FROM tCity WHERE metro = 'y';

SELECT * FROM tCity WHERE UPPER(metro) = 'Y';
SELECT * FROM tCity WHERE LOWER(metro) = 'y';

SELECT CONCAT(name, ' 사원님') FROM tStaff;

SELECT CONCAT(TRIM(name), ' 사원님') FROM tStaff;

-- LPAD, RPAD는 각각 패딩을 준다
SELECT LPAD('SQL', 10, '>') FROM dual;  -- >>>>>>>SQL
SELECT RPAD('SQL', 10, '<') FROM dual;  -- SQL<<<<<<<

SELECT LPAD(RPAD('SQL', 10, '<'), 17, '>') FROM dual;	-- >>>>>>>SQL<<<<<<<

SELECT name, LPAD(area, 4, '0') FROM tCity;

SELECT REPLACE('독도는 일본땅이다', '일본', '한국') FROM dual;

SELECT REPLACE('구글에서 구글링한다.', '구글', '네이버') FROM dual; 	
-- 네이버에서 네이버링한다.

SELECT REPLACE('Get_Total_Score', '_', '') FROM dual;   -- GetTotalScore

SELECT REPLACE('독도는 일본땅이다. 대마도는 일본땅이다.', '일본', '한국') FROM dual;

-- STUFF는 SQL SERVER에서 사용하는 REPLACE이다.
SELECT STUFF('독도는 일본땅이다. 대마도는 일본땅이다.', 5, 2, '한국');

-- '독도는 한국땅이다. 대마도는 일본땅이다' 를 만들기 위한 쿼리
SELECT SUBSTR('독도는 일본땅이다. 대마도는 일본땅이다.', 1, 
INSTR('독도는 일본땅이다. 대마도는 일본땅이다.', '일본') - 1) || '한국' ||
SUBSTR('독도는 일본땅이다. 대마도는 일본땅이다.', 
INSTR('독도는 일본땅이다. 대마도는 일본땅이다.', '일본') + 2) FROM dual;

-- str을 원하는 문자열로 미리 지정해놓으면 편하게 사용할 수 있다.
SELECT SUBSTR(str, 1 , INSTR(str, '일본') - 1) || '한국' || SUBSTR(str, INSTR(str, '일본') + 2) 
FROM tTable;

pos = INSTR(str, '일본');
SELECT SUBSTR(str, 1 , pos - 1) || '한국' || SUBSTR(str, pos + 2) FROM tTable;

-- 날짜 포맷으로 변환하는 쿼리
INSERT INTO tDate VALUES (TO_DATE('2021/12/25 12:34:56', 'yyyy/mm/dd hh24:mi:ss'));

SELECT AVG(popu) FROM tCity;

오라클 : SELECT CAST(AVG(popu) AS INT) FROM tCity;		-- 193
MSSQL : SELECT AVG(CAST(popu AS DECIMAL)) FROM tCity;	-- 193.25

SELECT '12' + 34 FROM dual;	-- 46
SELECT '12' || 34 FROM dual;	-- 1234

SELECT '12' + 34;					-- 46
SELECT '12' + CAST(34 AS VARCHAR(10));	-- 1234

SELECT '응답하라 ' + 1989;				-- 에러
SELECT '응답하라 ' + CAST(1989 AS VARCHAR(10));	-- 응답하라 1989

SELECT '응답하라 ' || 1989 FROM dual;

-- TO_CHAR로 포맷팅을 한 문자열을 리턴할 수 있다.
SELECT TO_CHAR(12345) FROM dual;		-- 12345
SELECT TO_CHAR(12345, '999,999') FROM dual;	--  12,345
SELECT TO_CHAR(12345, 'FM999,999') FROM dual; 	-- 12,345
SELECT TO_CHAR(12345, '000,999') FROM dual; 	-- 012,345

-- 포맷된 문자열을 다시 숫자로 바꾸려면 포맷 형식을 주어야 한다.
SELECT TO_NUMBER('12345') FROM dual;		-- 12345
SELECT TO_NUMBER('12,345') FROM dual;		-- 에러
SELECT TO_NUMBER('12,345', '999,999') FROM dual;	-- 12345

SELECT '응답하라 ' + CONVERT(VARCHAR(10), 1989);		-- 응답하라 1989

오라클 : SELECT name, NVL(score, 10) FROM tStaff;
MSSQL : SELECT name, ISNULL(score, 10) FROM tStaff;
마리아 : SELECT name, IFNULL(score, 10) FROM tStaff;

-- NULLIF : 특정값을 NULL로 만든다.
SELECT name, NULLIF(score, 0) FROM tStaff;

-- NVL을 써서 NULL 값을 60으로 간주하도록 만들었다.
SELECT name, NVL(NULLIF(score, 0), 60) FROM tStaff;

-- NVL은 NULL일 때의 값과 아닐때의 값을 각각 지정한다
SELECT name, NVL2(score, salary * score / 100, 50) FROM tStaff;

-- 대상 필드 다음은 두 값씩 짝을 이루어 값과 결과를 나열하며 이 중 일치하는 결과를 리턴한다.
SELECT name, DECODE(gender, '남', '멋쟁이', '여', '예쁜이', '몬난이') FROM tStaff;

SELECT SYSDATE FROM dual;  -- 20/10/17 12:21:42

INSERT INTO tStaff VALUES ('김한슬', '기획실', '여', SYSDATE, '수습', 480, 50);

-- SYSDATE는 시스템 시간이다.
SELECT SYSDATE + 12 FROM dual;

SELECT SYSDATE + 5/24 FROM dual;
SELECT SYSDATE - 30/1440 FROM dual;
SELECT SYSDATE - 80/86400 FROM dual;

-- 날짜끼리의 간격을 뺄셈으로 구할 수 있다.
SELECT name, sysdate - joindate FROM tStaff;

-- 날짜 타입 변환은 연습이 많이 필요하다.
SELECT TO_CHAR(SYSDATE, 'yyyy/mm/dd hh24:mi:ss') FROM dual;	  -- 2020/10/17 12:18:51
SELECT TO_CHAR(SYSDATE, 'yyyy/mm/dd AM hh:mi:ss') FROM dual; -- 2020/10/17 오후 12:20:35

SELECT TO_CHAR(SYSDATE, 'yyyy"년" mm"월" dd"일" hh24"시" mi"분" ss"초"') FROM dual;
-- 2020년 10월 17일 12시 24분 44초

SELECT name, TO_CHAR(joindate, 'yyyy') FROM tStaff;

SELECT TO_CHAR(joindate, 'yyyy') AS 년, TO_CHAR(joindate, 'mm') AS 월, 
TO_CHAR(joindate, 'dd') AS 일 FROM tStaff;

SELECT TO_DATE('1919/3/1', 'yyyy/mm/dd') FROM dual;

SELECT TO_DATE('1919-3-1', 'yyyy-mm-dd') FROM dual;
SELECT TO_DATE('19190301', 'yyyymmdd') FROM dual;

SELECT TO_DATE('1919/3/1') FROM dual;
SELECT TO_DATE('1919-3-1') FROM dual;
SELECT TO_DATE('19190301') FROM dual;

-- 날짜 연산을 할 때는, 문자열을 날짜 타입으로 제대로 변환해 준 후 계산을 해야 한다.
SELECT sysdate - '1919/3/1' FROM dual;	-- 에러

SELECT sysdate - TO_DATE('1919/3/1') FROM dual;

-- 2023년 3월 8일 태어난 아기의 백일잔치 날짜 구하는 쿼리
SELECT TO_CHAR(TO_DATE('2023/3/8', 'yyyy/mm/dd') + 99, 'yyyy"년" mm"월" dd"일"') FROM dual;

-- 이부분 이후는 SQL Server의 날짜 함수이므로 참고만 하자.
SELECT GETDATE();

SELECT name, YEAR(joindate) AS 년, MONTH(joindate) AS 월, DAY(joindate) AS 일 FROM tStaff;

SELECT YEAR(joindate), count(*) FROM tStaff GROUP BY YEAR(joindate) 
ORDER BY YEAR(joindate);

SELECT DATEPART(dw, GETDATE());
SELECT DATENAME(dw, GETDATE());

SELECT name, DATEDIFF(day,joindate,GETDATE()) FROM tStaff;

SELECT DATEDIFF(day, '1919/3/1', GETDATE());

SELECT CONVERT(VARCHAR(20), GETDATE(),0);		-- 06 29 2021 10:43PM
SELECT CONVERT(VARCHAR(20), GETDATE(),11);		-- 21/06/29
SELECT CONVERT(VARCHAR(20), GETDATE(),111); 		-- 2021/06/29
SELECT CONVERT(VARCHAR(20), GETDATE(),101); 		-- 06/29/2021
SELECT CONVERT(VARCHAR(20), GETDATE(),103); 		-- 29/06/2021

SELECT NOW();

SELECT DATE_FORMAT(NOW(), '%Y/%m/%d %H:%i:%s');

SELECT DATE_FORMAT(joindate, '%Y'), count(*) FROM tStaff 
GROUP BY DATE_FORMAT(joindate, '%Y') ORDER BY DATE_FORMAT(joindate, '%Y');

SELECT DATE_ADD(NOW(), INTERVAL 12 DAY);		-- 12일 후
SELECT DATE_ADD(NOW(), INTERVAL 5 HOUR);		-- 5시간 후
SELECT DATE_SUB(NOW(), INTERVAL 30 MINUTE);		-- 30분 전

SELECT name, DATEDIFF(NOW(), joindate) FROM tStaff;