[ORACLE] 기본 문법 1

[개념 1] UPPER, LOWER 함수 : 대소문자 바꿔주기         . UPPER(문자열)    : 괄호 안 문자 데이터를 모두 대문자로 변환하여 반환         . LOWER(문자열)    : 괄호 안 문자 데이터를 모두 소문자로 변환하여 반환         . INITCAP(문자열)  : 괄호 안 문자 데이터 중 첫 글자는 대문자로, 나머지는 소문자로 변환 후 반환

사용예시

-- [개념 1-2] UPPER 함수로 문자열 비교하기
-- [실습 1-2] 사원 이름이 SCOTT인 데이터 찾기
SELECT * from EMP WHERE UPPER(ENAME) = UPPER('scott');

 

-- [개념 1-3] UPPER 함수로 문자열 비교하기
-- [실습 1-3] 사원 이름에 SCOTT 단어를 포함한 데이터 찾기
select * from EMP WHERE UPPER(ENAME) LIKE UPPER('%scott%');

 

---

[개념 2] LENGTH함수 : 문자열 길이

사용예시

-- [개념 2-1] 선택한 열의 문자열 길이 구하기
-- [실습 2-1] 사원이름과 사원이름의 길이 출력하기
Select ENAME, LENGTH(ENAME) FROM EMP;

 

-- [개념 2-2] WHERE절에서 LENGTH함수 사용하기
-- [실습 2-2] 사원 이름의 길이가 5이상인 사원정보 출력하기
SELECT ENAME, LENGTH(ENAME) FROM EMP WHERE LENGTH(ENAME) >= 5;

 

---

[개념 3] SUBSTR 함수 : 문자열 일부 추출         . SUBSTR(문자열 데이터, 시작 위치, 추출 길이)             - 문자열 데이터의 시작 위치부터 추출 길이만큼 추출한다.             - 추출 위치가 음수일 경우에는 마지막 위치부터 거슬러 올라간 위치에서 시작한다.         . SUBSTR(문자열 데이터, 시작 위치)             - 문자열 데이터의 시작 위치부터 문자열 데이터 끝까지 추출한다.             - 추출 위치가 음수일 경우에는 마지막 위치부터 거슬러 올라간 위치에서 시작한다.

사용예시

-- [개념 3-2] SUBSTR 함수와 다른 함수 함께 사용하기
-- [실습 3-2] SUBSTR 함수 안에 다른 함수(LENGTH) 함께 사용하기
SELECT JOB,
    SUBSTR(JOB, -LENGTH(JOB)), // 맨 앞부터 끝까지
    SUBSTR(JOB, -LENGTH(JOB), 2), // 앞에서 2개
    SUBSTR(JOB, -3) // 뒤에서 3개
FROM EMP;

 

---

INSTR 함수 : 문자열 데이터 안에서 특정 문자 위치 찾기         . INSTR([문자열 데이터],                 [위치를 찾으려는 부분 문자],                 [위치 찾기를 시작할 대사 문자열 데이터 위치(선택, 기본값은 1)],                 [시작 위치에서 찾으려는 문자가 몇 번째인지 지정(선택, 기본값은 1)])

사용예시

SELECT INSTR('HELLO, ORACLE!', 'L') AS INSTR_1,
                INSTR('HELLO, ORACLE!', 'L', 5) AS INSTR_2,
                INSTR('HELLO, ORACLE!', 'L', 2, 2) AS INSTR_3
FROM DUAL;

 

실행결과

/*
    # DUAL 테이블
    DUAL 테이블은 오라클의 최고 권한 관리자 계정이 SYS 소유의 테이블로
    SCOTT 계정도 사용할 수 있는 더미(dummy) 테이블이다.
    임시 연산이나 함수의 결과 값 확인 용도로 종종 사용된다.
*/

 

-- [개념 4-2] 특정 문자를 포함하고 있는 행 찾기
-- [실습 4-2] INSTR 함수로 사원 이름에 문자 S가 있는 행 구하기
SELECT * FROM EMP WHERE INSTR(ENAME, 'S') > 0;

 

비교

 

-- [실습 4-3] LIKE 연산자로 사원 이름에 문자 S가 있는 행 구하기
SELECT * FROM EMP WHERE ENAME LIKE '%S%';

 

 

---

REPLACE 함수 : 특정 문자를 다른 문자로 바꾸기         . REPLACE([문자열 데이터 또는 열 이름], [찾는 문자], [대체할 문자(선택)])

사용예시

-- [실습 5-1] REPLACE 함수로 문자열 안에 특정 문자 바꾸기
SELECT '010-1234-5678' AS REPLACE_BEFORE,
    REPLACE('010-1234-5678', '-', ' ') AS REPLACE_1,
    REPLACE('010-1234-5678', '-') AS REPLACE_2 

FROM DUAL;

 

실행화면

---

LPAD, RPAD 함수 : 데이터의 빈 공간을 특정 문자로 채우기         . LPAD : Left Padding(왼쪽 패딩)         . RPAD : Right Padding(오른쪽 패딩)         . 데이터와 자릿수를 지정한 후 데이터 길이가 지정한 자릿수보다 작을 경우에           나머지 공간을 특정 문자로 채우는 함수         . LPAD([문자열 데이터 또는 열이름], [데이터의 자릿수], [빈 공간에 채울 문자(선택])         . RPAD([문자열 데이터 또는 열이름], [데이터의 자릿수], [빈 공간에 채울 문자(선택])

사용예시

-- [실습 6-1] LPAD, RPAD 함수 사용하여 출력하기
SELECT
    LPAD('Oracle', 10, '#') AS LPAD_1,
    RPAD('Oracle', 10, '*') AS RPAD_2,
    LPAD('Oracle', 10) AS LPAD_2,
    RPAD('Oracle', 10) AS RPAD_2
FROM DUAL;

실행화면

---

두 문자열 데이터를 합치는 CONCAT 함수         . 두 개의 문자열 데이터를 하나의 데이터로 연결해 주는 함수

사용예시

-- [실습 7-1] 두 열 사이에 콜롬(:) 넣고 연결하기
SELECT 

CONCAT(EMPNO, ENAME), 

CONCAT(EMPNO, CONCAT(':', ENAME)) 

FROM EMP WHERE ENAME = 'SCOTT';

 

실행화면

---

TRIM, LTRIM, RTRIM 함수 : 특정 문자 지우기         . 삭제할 문자가 없으면 공백이 제거된다.         . TRIM([삭제 옵션(선택) [삭제할 문자(선택)] FROM [원본 문자열 데이터])         . LTRIM([원본 문자열 데이터], [삭제할 문자 집합(선택)])         . RTRIM([원본 문자열 데이터], [삭제할 문자 집합(선택)])

사용예시

-- [개념 8-1] TRIM 함수 사용하기(삭제할 문자가 없을 때)    
-- [실습 8-1] TRIM 함수로 공백 제거하여 출력하기

SELECT 

       '[' || TRIM('    __Oracle__     ') || ']' AS TRIM,
       '[' || TRIM(LEADING FROM '      __Oracle__     ') || ']' AS TRIM_LEADING,
       '[' || TRIM(TRAILING FROM '     __Oracle__     ') || ']' AS TRIM_TRAILING,
       '[' || TRIM(BOTH FROM '     __Oracle__     ') || ']' AS TRIM_BOTH
FROM DUAL;

 

실행결과

 

SELECT 

       '[' || TRIM('_' FROM '__Oracle__') || ']' AS TRIM,
       '[' || TRIM(LEADING '_' FROM '__Oracle__') || ']' AS TRIM_LEADING,
       '[' || TRIM(TRAILING '_' FROM '__Oracle__') || ']' AS TRIM_TRAILING,
       '[' || TRIM(BOTH '_' FROM '__Oracle__') || ']' AS TRIM_BOTH
    FROM DUAL;

실행결과

SELECT

                '[' || TRIM(' _Oracle_ ') || ']' AS TRIM,
                '[' || LTRIM(' _Oracle_ ') || ']' AS LTRIM,
                '[' || LTRIM('<_Oracle_>', '_<') || ']' AS LTRIM_2,
                '[' || RTRIM(' _Oracle_ ') || ']' AS RTRIM,
                '[' || RTRIM('<_Oracle_>', '_>') || ']' AS RTRIM_2
FROM DUAL;

실행결과

---

★ 통합문제 ★

-- EMPNO 열에는 EMP테이블에서 사원 이름이 다섯 글자 이상이며 여섯 글자 미만인 사원번호를 출력하시오.
-- MASKING_EMPNO 열에는 사원번호(EMPNO) 앞 두 자리 외 뒷자리를 * 기호로 출력하시오.
-- ENAME 열에는 사원 이름을 출력하고,
-- MASKING_ENAME 열에는 사원 이름의 첫 글자만 보여 주고 나머지 글자 수만큼 *기호로 출력하시오.

 

SELECT EMPNO,

                 LPAD(SUBSTR(EMPNO, 1, 2), 4, '*') AS MASKING_EMPNO,

                 // 사원번호 첫 번째부터 두개만 자르고, 총 네 자리중 부족한 부분은 * 로 오른쪽을 채운다.

                 ENAME,

                 RPAD(SUBSTR(ENAME, 1, 1), LENGTH(ENAME), '*') AS MASKING_ENMAE

                // 사원이름 첫 번째부터 한 개만 자르고, 총 이름 길이 중 부족한 부분은 *로 왼쪽을 채운다. 

FROM EMP; 

 

실행결과