[Oracle] 함수

문자열 함수

 

LOWER	대상 문자열을 모두 소문자로 변환
UPPER	모든 문자를 대문자로 변경
INITCAP	문자열 단어의 첫 문자는 대문자, 나머지는 소문자로 변경
CONCAT(s1, s2)	두 문자열 연결
SUBSTR(s, m, n)	문자열 중 지정한 위치에서 지정한 길이만큼의 문자열 추출
INSTR(s1, s2, m, n)	문자열 내의 특정 문자열의 위치 숫자로 표시
LENGTH(s)	문자의 길이를 숫자 값으로 표시
CHR(n)	ASCII 코드값이 n인 문자 반환
ASCII(s)	S문자의 ASCII 코드값 반환
LPAD(s1, n, s2)	문자열을 제외한 왼쪽 공간에 지정한 문자로 채움

SQL 인덱스는 1부터 시작

인덱스 시작을 0으로 하는 경우 1로 취급

음수-> 오른쪽부터

---

SUBSTR(문자열, 시작, 개수): 문자열에서 시작부터 개수만큼 문자열 추출

SELECT JOB, SUBSTR(JOB, 1, 3), SUBSTR(JOB, 2, 3), SUBSTR(JOB, 3)
FROM EMP;

 

SELECT JOB, SUBSTR(JOB, 0, 3), SUBSTR(JOB, -2, 3)  -- 뒤에서 2번째부터 3개 (2개밖에 없으니까 2개까지만)
FROM EMP;

 

 

 INSTR(문자열, 문자, 시작점, 몇 번째): 문자열에서 문자가 몇 번째 인덱스에 있는지 찾음

SELECT 'ABCDE ABCDE ABCDE ABCDE',
INSTR ('ABCDE ABCDE ABCDE ABCDE', 'C')
INSTR ('ABCDE ABCDE ABCDE ABCDE', 'C', 5)	-- 시작인덱스: 5
INSTR ('ABCDE ABCDE ABCDE ABCDE', 'C', 5, 2)  -- 5번째에서 시작, 2번째로 등장하는 C
FROM DUAL;

-- 3 9 15

이메일 주소에서 @전까지 출력 / @ 이후부터 출력

SELECT EMAIL, SUBSTR(EMAIL, 1, INSTR(EMAIL, '@') - 1), SUBSTR(EMAIL, INSTR(EMAIL, '@') + 1)
FROM TEACHER;

 

 

---

숫자 함수

 

 

ROUND

SELECT 231.45136, ROUND(231.45136, 2), ROUND(231.45136, -2), ROUND(231.45136, 0)
FROM DUAL;

 

TRUNC

SELECT 231.45136, TRUNC(231.45136, 2), TRUNC(231.45136, -2), TRUNC(231.45136, 0)
FROM DUAL;

 

CEIL, FLOOR

SELECT 231.45136, CEIL(231.45136) 올림, FLOOR(231.45136) 버림
FROM DUAL;

 

---

날짜 함수

 

SELECT EMPNO, HIREDATE, SYSDATE - HIREDATE, HIREDATE + 5, 
TO_CHAR(HIREDATE + 5/24, 'YYYY-MM-DD HH:MI:SS')
FROM EMP;

 

 

SELECT SYSDATE, EXTRACT(DAY FROM SYSDATE), EXTRACT(MONTH FROM SYSDATE), EXTRACT(YEAR FROM SYSDATE) 
FROM DUAL;

SELECT SYSDATE, TO_CHAR(SYSDATE, 'DD'), TO_CHAR(SYSDATE, 'MM'), TO_CHAR(SYSDATE, 'YYYY')
FROM DUAL;

 

YYYY년 MM월 DD일 형식 출력

SELECT HIREDATE,
TO_CHAR(HIREDATE, 'YYYY') || '년 ' || TO_CHAR(HIREDATE, 'MM') || '월 ' || TO_CHAR(HIREDATE, 'D') || '일'
FROM EMP;

SELECT HIREDATE, TO_CHAR(HIREDATE, 'YYYY" 년" MM"월" DD"일"')
FROM EMP;

 

날짜 차이 계산

SELECT CONCAT(TRUNC(SYSDATE - TO_DATE('2023-3-1')), '일') AS 경과일
FROM DUAL;

 

디데이 계산

SELECT TO_DATE('2023-3-1') + 100
FROM DUAL;

 

일반 함수

 

NVL2

-- MGR이 NULL이 아니면 두번째 인수를, NULL이면 세번째 인수를 출력
SELECT EMPNO, ENAME, MGR, NVL2(MGR, MGR || '는 관리자입니다.', '최고 관리자')
FROM EMP;

 

 

DECODE / CASE END

/*
    EMP 테이블에서 DEPNO가 10이면 관리부, 20이면 총무부 30이면 영업부
    이외의 값은 기타부로 출력하고 컬럼명은 부서명으로 한다.
    (DECODE, CASE END)
*/

-- DECODE
SELECT EMPNO, ENAME, JOB, DEPTNO,
DECODE(DEPTNO, 10, '관리부', 20, '총무부', 30, '영업부', '기타부') 부서명
FROM EMP;

-- CASE END
SELECT EMPNO, ENAME, JOB, DEPTNO,
CASE DEPTNO
    WHEN 10 THEN '관리부'
    WHEN 20 THEN '총무부'
    WHEN 30 THEN '영업부'
    ELSE '기타부'
END AS 부서명 
FROM EMP;

/*
ex)job이 manager인 경우 sal*0.1, ANALYST 인경우는   sal *0.2
     SALESMAN인 경우는 sal * 0.3을 구해서 성과급 필드를 만든다.
      (case end, decode) 
*/

SELECT EMPNO, ENAME, JOB, SAL,
DECODE(JOB, UPPER('manager'), SAL * 0.1, 'ANALYST', SAL * 0.2, 'SALESMAN', SAL * 0.3, 0) 성과급
FROM EMP;

SELECT E.*, -- 새로운 컬럼 추가하면서 모든 컬럼 조회하고 싶은 경우
DECODE(JOB, UPPER('manager'), SAL * 0.1, 'ANALYST', SAL * 0.2, 'SALESMAN', SAL * 0.3, 0) AS 성과급
FROM EMP E;


SELECT EMPNO, ENAME, JOB, SAL,
CASE JOB
    WHEN 'MANAGER' THEN SAL * 0.1
    WHEN 'ANALYST' THEN SAL * 0.2
    WHEN 'SALESMAN' THEN SAL * 0.3
    ELSE 0
END AS 성과급
FROM EMP;