[ORACLE] SQL 함수 - 단일행 함수

1. 단일행 숫자 함수

단일행 함수는 쿼리 된 테이블 또는 뷰의 모든 행에 대해 단일 결과행을 반환

숫자 함수 종류

수식 연산을 하는 함수, 매개변수나 반환 값이 대부분 숫자 형태

ABS(n)  절대값

SELECT ABS(20), ABS(-20) FROM dual; -- 20 -20

 

SIGN(n)  부호

 SELECT SIGN(20), SIGN(-20), SIGN(0) FROM dual; -- 1 -1 0

 

MOD(n2, n1) 

n2를 n1으로 나눈 나머지를 반환 n1이 0이면 n2를 반환 

내부 연산 방식 : n2 - n1 * FLOOR(n2/n1)

SELECT MOD(11, 4) FROM dual;  -- 3
SELECT MOD(13, 5) FROM dual;  -- 3

 

REMAINDER(n2, n1)

n2를 n1으로 나눈 나머지를 반환

내부 연산 방식 : n2 - n1 * ROUND(n2/n1)

SELECT REMAINDER(13, 5) FROM dual; -- -2
SELECT REMAINDER(13, 6) FROM dual; -- 1

CEIL(n)

n보다 크거나 같은 가장 작은 정수

SELECT CEIL(20.5), CEIL(-20.5), CEIL(20) FROM dual;  -- 21  -20  20

 

FLOOR(n)

n 이하의 가장 큰 정수를 반환 

SELECT 13/5 FROM dual; -- 2.6

SELECT 13 - 5 * FLOOR(13/5) FROM dual; --3
SELECT FLOOR(20.5), FLOOR(-20.5), FLOOR(20) FROM dual;  -- 20 -21 20

  
ROUND(n [, integer ]) 반올림

n을 소수점 오른쪽의 정수 자리로 반올림 

SELECT ROUND(13/5) FROM dual; -- 3
SELECT 13 - 5 * ROUND(13/5) FROM dual; -- -2 
SELECT ROUND(15.683) FROM dual;  -- 16 : 소수점 첫째 자리 반올림
SELECT ROUND(15.683, 0) FROM dual;  -- 16
SELECT ROUND(15.683, 1) FROM dual;  -- 15.7  : 소수점 둘째자리에서 반올림해서 소수점 한자리 표현
SELECT ROUND(15.683, 2) FROM dual;  -- 15.68
SELECT ROUND(15.683, -1) FROM dual;  -- 20. 음수이면 소수점 왼쪽에서 반올림

 

TRUNC(n1 [, n2 ])  절삭

SELECT TRUNC(15.683) FROM dual;  -- 15
SELECT TRUNC(15.683, 1) FROM dual;  -- 15.6
SELECT TRUNC(15.683, 2) FROM dual;  -- 15.68
SELECT TRUNC(15.683, -1) FROM dual;  -- 10

 

-- emp : name, sal, sal의 5만원권 매수, sal 1만원권 매수, 나머지 금액 출력
-- 출력 컬럼명 : 이름 급여 5만원권 1만원권 기타 금액
-- 컬럼명(별명)은 숫자로 시작할 수 없고 공백도 불가능.

-- 공백이 있거나 또는 숫자로 시작해야 하는 경우에는 "칼럼명"처럼 " 안에 기술

SELECT name 이름, sal 급여, 
	TRUNC(sal/50000) "5만원권수", 
	TRUNC(MOD(sal,50000)/10000) AS "1만원권",
	MOD(sal, 10000) 나머지	
FROM emp;

 

SIN(n), COS(n), TAN(n)

삼각 함숫값을 반환 

SELECT SIN(30/180*3.14592), 
	COS(30/180*3.14592),
	TAN(30/180*3.14592)
FROM dual;

 

 

EXP(n), POWER(n2, n1), SQRT(n), LOG(n2, n1), LN(n) 등

SELECT POWER(2, 10), SQRT(2) FROM dual;

 

2. 단일행 문자 함수

문자 함수 종류
LOWER(char) 소문자

SELECT LOWER('Seoul'), LOWER('KOREA') FROM dual;
SELECT * FROM col WHERE tname = 'emp'; -- 출력되는 행이 없음
SELECT * FROM col WHERE tname = 'EMP';
-- 오라클은 딕셔너리에 테이블명(객체명), 컬럼명등은 모두 대문자로 저장된다.
SELECT * FROM col WHERE LOWER(tname) = 'emp';

 

 

UPPER(char) 대문자

SELECT UPPER('Seoul'), UPPER('korea') FROM dual;
SELECT * FROM col WHERE tname = UPPER('emp');

INITCAP(char) 단어의 첫 글자만 대문자로 하고 나머지는 소문자로

SELECT INITCAP('KOREA seoul') FROM dual;   -- Korea Seoul

       
CHR(n [ USING NCHAR_CS ])  ascii 코드에 해당하는 문자

SELECT CHR(75) || CHR(79) FROM dual; --KO

 

ASCII(char)  첫 문자의 데이터베이스 문자 세트에서 10진수 코드값

SELECT ASCII('ABC') FROM dual;  -- 65

ASCIISTR(char)  영문자와 숫자는 그대로 출력되고 나머지 non-ascii 문자는 유니코드 출력

SELECT ASCIISTR('korea 123'), ASCIISTR('대한') FROM dual;

SUBSTR(char, position [, substring_length ]) : 특정 위치의 문자열 반환
         -- 인덱스는 1부터

SELECT SUBSTR('Seoul Korea', 7, 3) FROM dual;  -- Kor
SELECT SUBSTR('Seoul Korea', -5, 3) FROM dual; -- Kor
SELECT SUBSTR('Seoul Korea', 7) FROM dual; -- Korea

 -- emp : 1900~1999년 서울 사람. name, rrn, city, sal

SELECT name, rrn, city,sal
FROM emp
WHERE SUBSTR(rrn,8,1) IN(1,2,5,6) AND city='서울';

-- emp : 2000~ 년대 사람. name, rrn, city, sal

SELECT name, rrn, city,sal
FROM emp
WHERE SUBSTR(rrn,8,1) IN(3,4,7,8);

-- emp : 78 ~ 82 년생. name, rrn, city, sal

SELECT name, rrn, city, sal
FROM emp
WHERE SUBSTR(rrn,1,2)>='78' AND SUBSTR(rrn,1,2)<='82';

-- emp : 서울사람 이면서 김 씨. name, rrn, city, sal

SELECT name, rrn, city, sal
FROM emp
WHERE city='서울' AND SUBSTR(name,1,1) = '김';

 

-- emp : 80~89 년생. name, rrn, city, sal

SELECT name, rrn, city, sal
FROM emp
WHERE SUBSTR(rrn, 1, 1) = '8';

      
-- emp : 김 씨, 이 씨, 최 씨 출력. name, rrn, city, sal

SELECT name, rrn, city, sal
FROM emp
WHERE SUBSTR(name,1,1) IN ('김','이','최');

            
-- emp : 홀수달에 태어난 사람 중 sal 내림차순. name, rrn, city, sal

SELECT name, rrn, city, sal
FROM emp
WHERE MOD(SUBSTR(rrn, 4, 1), 2) = 1
ORDER BY sal DESC;

- INSTR(string , substring [, position [, occurrence ] ])  문자열을 검색하여 위치를 반환. 없으면 0

SELECT INSTR('korea seoul', 'e') FROM dual;  -- 4
-- 처음부터 검색하여 가장 처음 만나는 위치

SELECT INSTR('korea seoul', 'abc') FROM dual;  -- 0
-- 없으면 0

SELECT INSTR('korea seoul', 'e', 7) FROM dual; -- 8
-- 7번째 위치(seoul)부터 검색

SELECT INSTR('korea seoul', 'e', 1, 2) FROM dual; -- 8
-- 처음부터 검색하여 두번째 나오는 위치

-- emp : 이름에 '이'라는 단어가 포함된 사람. name, tel, sal

SELECT name, tel, sal 
FROM emp 
WHERE INSTR(name,'이') > 0;

-- emp : name, tel, tel 서버스번호, tel 국번, tel 번호
-- 이런 문제는 자바에 분리하는 것이 더 효율적

SELECT name, tel, SUBSTR(tel, 1, INSTR(tel, '-') - 1) 서비스,
	SUBSTR(tel, INSTR(tel, '-') + 1, 
		INSTR(tel, '-', 1, 2) - INSTR(tel, '-') - 1) 국번,
	SUBSTR(tel, INSTR(tel, '-', 1, 2) + 1) 번호
FROM emp;

- LENGTH(char) 문자열의 길이. 한글도 1자씩 출력

SELECT LENGTH('대한민국') FROM dual; -- 4
SELECT LENGTHB('대한민국') FROM dual;  -- 12. byte 수 출력. 한글은 UTF-8 로 저장됨

 

- REPLACE(char, search_string [, replacement_string])

SELECT REPLACE('seoul korea', 'seoul', 'busan') FROM dual; -- busan korea
SELECT REPLACE('123455245853', '5') FROM dual; -- 모든 5 제거(12342483)

-- emp 테이블에서 tel의 - 제거

SELECT name, tel, REPLACE(tel, '-') FROM emp;

-- emp 테이블에서 tel의 -을 #으로 변경

SELECT name, tel, REPLACE(tel, '-', '#') FROM emp;

- CONCAT(char1, char2)  문자열 결합

SELECT '서울' || '부산' FROM dual; --서울부산
SELECT CONCAT('서울', '부산') FROM dual; --서울부산

- LPAD(expr1, n [, expr2]) : 남는 왼쪽 공간에 expr2로 채움
- RPAD(expr1, n [, expr2])

SELECT LPAD('korea', 12, '*') FROM dual;  -- *******korea
SELECT RPAD('korea', 12, '*') FROM dual;  -- korea*******

SELECT LPAD('korea', 3, '*') FROM dual;  -- kor
SELECT LPAD('korea', 0, '*') FROM dual;  -- null
SELECT LPAD('대한', 6, '*') FROM dual;  -- **대한. LPAD는 한글은 2칸으로 처리

 

 

-- emp : name, rrn, rrn(성별 다음부터는 * 로)

SELECT name, rrn, SUBSTR(rrn,1,8) || '******' 주민번호
FROM emp;

SELECT name, rrn, RPAD(SUBSTR(rrn,1,8), 14, '*') 주민번호
FROM emp;

 

-- emp : name, tel. 단 전화번호 뒤 3자리는 *로 출력. 전번 길이는 12, 13 일수 있다.

SELECT name, RPAD(SUBSTR(tel,1,LENGTH(tel)-3), LENGTH(tel), '*') tel
FROM emp;

-- last_name을 9자리로 출력하며 남는 자리는 해당 자릿수에 해당하는 숫자로 출력

WITH tb AS (
	SELECT 'seoul' last_name FROM dual
    	UNION ALL
	SELECT 'haha' last_name FROM dual
		UNION ALL
	SELECT 'no' last_name FROM dual
    ) 
    SELECT RPAD(last_name, 9, SUBSTR('123456789', LENGTH(last_name)+1)) last_name
    FROM tb;

 

SELECT name, sal, LPAD('*', TRUNC(sal/100000), '*')  -- 그래프
FROM emp;

      -- 이름 마킹
         김호 -> 김*호, 나가다 -> 나*다, 호가나다 -> 호**다