[SQL] 함수 명령어 정리- 문자, 숫자, 날짜, 형변환, 그룹

함수 명령어 정리

문자 처리 함수

명령어	리턴 타입	의미	예시
LENGTH	NUMBER	컬럼 값/문자열의 길이반환	SELECT LENGTH('벌써3월') FROM DUAL; SELECT * FROM EMPLOYEE WHERE LENGTH(EMAIL)>=16;
LENGTHB	NUMBER	컬럼 값/문자열의 길이(BYTE) 반환	SELECT LENGTHB(EMP_NAME) FROM EMPLOYEE;
INSTR	NUMBER	지정 위치의 지정한 번째로 나타나는 문자의 시작 위치 반환	SELECT EMAIL, INSTR(EMAIL, ‘@’, -1, 1) FROM EMPLOYEE;
LPAD / RPAD	CHARACTER	임의의 문자열을 왼쪽/오른쪽에 덧붙임	SELECT LPAD(EMAIL, 20, ‘#’) FROM EMPLOYEE;
LTRIM / RTRIM	CHARACTER	왼쪽/오른쪽의 지정한 모든 문자 제거	SELECT LTRIM(PHONE, '010') FROM EMPLOYEE;
TRIM	CHARACTER	문자열의 앞/뒤/양쪽 지정 문자 제거 LEADING : 왼쪽 문자 제거 TRAILING : 오른쪽 문자 제거 BOTH : 양쪽 문자 제거	SELECT TRIM('K' FROM 'KKK CHAN KKK') FROM DUAL; SELECT TRIM(LEADING 'K' FROM 'KKK CHAN KKK') FROM DUAL; SELECT TRIM(TRAILING 'K' FROM 'KKK CHAN KKK') FROM DUAL;
SUBSTR	CHARACTER	지정 위치, 지정 개수의 문자열을 잘라 냄	SELECT SUBSTR('HELLO FUNCTION',7,2) FROM DUAL;
LOWER UPPER INITCAP	CHARACTER	소문자/대문자/첫 글자만 대문자로 변환	SELECT LOWER('Welcome to world') FROM DUAL; SELECT UPPER('Welcome to world') FROM DUAL; SELECT INITCAP('Welcome to world') FROM DUAL;
CONCAT	CHARACTER	문자열을 하나로 합침	SELECT CONCAT(EMP_NAME, SALARY) FROM EMPLOYEE;
REPLACE	CHARACTER	특정 문자(열)을 지정한 문자(열)로 바꿈	SELECT REPLACE('I LOVE YOU','LOVE','HATE')  FROM DUAL;
REVERSE	CHARACTER	데이터 출력의 순서를 변경하여 출력	SELECT REVERSE('I LOVE YOU') FROM DUAL;
TRANSLATE	CHARACTER	특정문자를 지정한 문자로 자동으로 변경	SELECT TRANSLATE('010-1234', '01234', '영일이삼사') FROM EMPLOYEE;

 

숫자 처리 함수

명령어	리턴 타입	의미	예시
ABS	NUMBER	숫자의 절대값 반환	SELECT ABS(10),ABS(-10) FROM DUAL;
MOD	NUMBER	숫자를 나누어 나머지 반환 (%)	SELECT MOD(10,3) FROM DUAL;
ROUND	NUMBER	지정한 위치부터 반올림	SELECT ROUND(126.456,2) FROM DUAL;
FLOOR	NUMBER	소수점 자리 수 버림	SELECT FLOOR(126.456) FROM DUAL;
TRUNC	NUMBER	지정한 위치부터 소수점 자리의 수를 버림	SELECT TRUNC(126.456,2) FROM DUAL;
CEIL	NUMBER	숫자 혹은 컬럼을 올림	SELECT CEIL(126.456) FROM DUAL;

 

날짜 처리 함수

명령어	리턴 타입	의미	예시
SYSDATE	DATE	현재날짜 출력	SELECT SYSDATE FROM DUAL;
SYSTIMESTAMP	DATE	현재날짜 시 분 초 밀리세컨초 출력	SELECT SYSTIMESTAMP FROM DUAL;
MONTHS _BETWEEN	NUMBER	두 개 월의 개월 수 차이	SELECT EMP_NAME, HIRE_DATE, MONTHS_BETWEEN(SYSDATE, HIRE_DATE) FROM EMPLOYEE;
ADD_MONTHS	DATE	숫자만큼 개월 수를 더함	SELECT SYSDATE, ADD_MONTHS(SYSDATE, 2) FROM DUAL;
NEXT_DAY	DATE	인자의 요일이 가장 가까운 날짜	SELECT NEXT_DAY(SYSDATE,'월') FROM DUAL;
LAST_DAY	DATE	인자의 날짜가 속한 달의 마지막 날짜	SELECT EMP_NAME, HIRE_DATE, LAST_DAY(HIRE_DATE) FROM EMPLOYEE;
EXTRACT	DATE	년, 월, 일 정보 추출하여 반환	SELECT EXTRACT(YEAR FROM HIRE_DATE) YEAR, EXTRACT(MONTH FROM HIRE_DATE) MONTH, EXTRACT(DAY FROM HIRE_DATE) DAY FROM EMPLOYEE;

 

형 변환 함수

명령어	리턴 타입	의미	예시
TO_CHAR	CHARCTER	문자형 데이터로 변환하여 반환	SELECT EMP_NAME, TO_CHAR(HIRE_DATE, 'YYYY-MM-DD'), TO_CHAR(SALARY, 'L999,999,999'), FROM EMPLOYEE;
TO_DATE	DATE	날짜형 데이터로 변환하여 반환	SELECT TO_DATE('19921030','YYYYMMDD') FROM DUAL;   SELECT EMP_NO, EMP_NAME, HIRE_DATE FROM EMPLOYEE WHERE HIRE_DATE > TO_DATE(20000101, ‘YYYYMMDD’);
TO_NUMBER	NUMBER	숫자형 데이터로 변환하여 반환	SELECT TO_NUMBER('550,000', '999,999') FROM DUAL;
NVL	NUMBER CHARACTER	NULL값을 지정 값으로 변경	SELECT BONUS, NVL(BONUS,0) FROM EMPLOYEE;
NVL2	NUMBER CHARACTER	NULL값이 아닌 경우와, NULL값인 경우에 대체 값 지정	SELECT BONUS, NVL2(BONUS,'있다','없다') FROM EMPLOYEE;
DECODE	결과	비교하고자 하는 값 또는 컬럼이 조건식과 같으면 반환	SELECT DECODE(SUBSTR(EMP_NO, 8, 1), ‘1’, ‘남’, ‘2’, ‘여‘) AS 성별 FROM EMPLOYEE;
CASE	결과	비교하고자 하는 값 또는 컬럼이 조건식과 같으면 결과 값 반환(조건은 범위 값 가능)	SELECT EMP_NAME, SALARY, CASE WHEN SALARY > 5000000 THEN '1등급‘ WHEN SALARY > 3500000 THEN '2등급‘ WHEN SALARY > 2000000 THEN '3등급' ELSE '4등급‘ END 등급 FROM EMPLOYEE;

 

그룹 함수

명령어	리턴 타입	의미	예시
SUM	NUMBER	컬럼 값들의 총합 반환	SELECT SUM(SALARY), FROM EMPLOYEE WHERE SUBSTR(EMP_NO, 8, 1) = ‘1’;
AVG	NUMBER	컬럼 값들의 평균 반환	SELECT ROUND(AVG(NVL(BONUS, 0)), 2) FROM EMPLOYEE;
MAX / MIN	NUMBER	컬럼의 최대값과 최소값 반환	SELECT MAX(SALARY), MIN(SALARY) FROM EMPLOYEE;
COUNT	NUMBER	조건을 만족하는 행의 개수 반환	SELECT COUNT(DEPT_CODE) FROM EMPLOYEE WHERE DEPT_CODE = ‘D5’;
ORDER BY		컬럼 출력 시 정렬해서 출력	SELECT DEPT_CODE, EMP_NAME FROM EMPLOYEE ORDER BY DEPT_CODE DESC;
NULLS		정렬 후 NULL값의 위치를 정함	SELECT DEPT_CODE, EMP_NAME FROM EMPLOYEE ORDER BY DEPT_CODE DESC NULLS LAST;
GROUP BY		특정 값으로 그룹을 맺음	SELECT DEPT_CODE, SUM(SALARY) FROM EMPLOYEE GROUP BY DEPT_CODE;
HAVING		그룹함수로 Row를 필터링 할 때 사용	SELECT DEPT_CODE, COUNT(*) FROM EMPLOYEE GROUP BY DEPT_CODE HAVING COUNT(*)>=3 ORDER BY 2 DESC;
ROLLUP		그룹의 총 합계 또는 평균까지 출력	SELECT NVL(DEPT_CODE,'총계'), SUM(SALARY) FROM EMPLOYEE WHERE DEPT_CODE IS NOT NULL GROUP BY ROLLUP(DEPT_CODE);
CUBE		그룹의 모든 조합을 집계한 결과 반환	SELECT NVL(DEPT_CODE,'총계'), SUM(SALARY) FROM EMPLOYEE WHERE DEPT_CODE IS NOT NULL GROUP BY CUBE(DEPT_CODE);
GROUPING		ROLLUP, CUBE 사용 여부 확인	SELECT DEPT_CODE, JOB_CODE, SUM(SALARY),  CASE WHEN GROUPING(DEPT_CODE) = 0 AND GROUPING(JOB_CODE) = 1 THEN '부서별 합계‘ WHEN GROUPING(DEPT_CODE) = 1 AND GROUPING(JOB_CODE) = 0 THEN '직급별 합계' WHEN GROUPING(DEPT_CODE) = 1 AND GROUPING(JOB_CODE) = 1 THEN '총 합계' ELSE '그룹별 합계' END AS 구분 FROM EMPLOYEE WHERE DEPT_CODE IS NOT NULL GROUP BY CUBE(DEPT_CODE, JOB_CODE) ORDER BY 1;