단일행 함수 (문자열 함수)

UPPER, LOWER, INITCAP : 각 함수는 각각의 문자열을 대문자, 소문자, 첫글자 만 대문자 로 바꿔준다.

SELECT 'OrAclE',UPPER('OrAclE'), LOWER('OrAclE'), INITCAP('OrAclE')
FROM dual;

이를 응용하는 경우는 주로 검색을 할 때이다. Oracle의 경우에는 대,소문자를 구분하기 때문에 대소문자를 구별해야 하는 상황이 아닐때 이를 이용하여 편리하게 검색할 수 있다.

예를 들어 기본계정 scott에서 'Salesman'을 검색할 때, 다음과 같은 쿼리문은 결과를 가저 오지 못한다.

SELECT *
FROM EMP
WHERE job = 'salesman';

하지만, 위의 함수들을 응용하여 검색한다면 편하게 결과를 가져올 수 있다. 예를 들어

SELECT * FROM EMP
WHERE UPPER(job) = UPPER('SaleSMaN');

과 같이 검색 할 수 있다.

REVERSE : 문자열의 순서를 역순으로 뒤집는다.

SELECT 'ORACLE',REVERSE('ORACLE')
FROM DUAL;

단, 이 기능은 영어에만 사용이 가능하고, 한글의 경우는 오류가 난다. 그 이유는 Reverse는 Byte를 역순으로 배열하는 것이기 때문에, 2Byte를 사용하는 한글의 경우는 역순의 의미가 다른 문자이기 때문이다.

SELECT '오라클',REVERSE('오라클')
FROM DUAL;

SUBSTR, SUBSTRB : 문자열의 일부를 반환한다. 이때 시작점으로부터 반환할 크기를 지정할 수도 있고, 안 할 수도 있다. 크기를 지정하지 않을 경우 전부를 반환한다. MS-SQL의 SUBSTRING과 같은 기능이다.
SUBSTR은 글자를 단위로 처리하는 반면, SUBSTRB는 Byte단위로 처리하므로, 2Byte문자에서 적용할 때에는 주의해야 한다.

SELECT 'ORACLE', SUBSTR('ORACLE',3,2),SUBSTR('ORACLE',3),SUBSTRB('ORACLE',3,2),SUBSTRB('ORACLE',3)
FROM dual;

SELECT '오라클서버', SUBSTR('오라클서버',3,2),SUBSTR('오라클서버',3),SUBSTRB('오라클서버',3,2),SUBSTRB('오라클서버',3)
FROM dual;

INSTR : 전체 문자열에서 해당 문자열이 몇번째 나오는지 알아보는 함수이다.
(전체문자열, 찾을문자열, 시작위치, 찾을 위치 Count)이 요구되며 Count가 생략될 시, 1이 자동이다. 또한 범위내에서 찾이 못했을 경우에는 0을 반환한다.

SELECT INSTR('오라클서버 찾아오라 오라이','오라',1,1) "처음부터 첫번째",
            INSTR('오라클서버 찾아오라 오라이','오라',2,1) "두번째부터 처음",
            INSTR('오라클서버 찾아오라 오라이','오라',1,2) "처음부터 두번째",
            INSTR('오라클서버 찾아오라 오라이','오라',3) "생략",
            INSTR('오라클서버 찾아오라 오라이','오라',3,3) "범위초과"
FROM DUAL;

이를 응용한 예제를 실행해 보자.
우선 다음과 같은 테이블을 만들어 데이터를 입력해보자.

CREATE TABLE TBLFILEPATH
(FILENO NUMBER(3),
FILENAME VARCHAR2(100)
)TABLESPACE USERS;

INSERT INTO TBLFILEPATH
VALUES(1,'c:\aaa\bbb\ccc\sales.xls');
INSERT INTO TBLFILEPATH
VALUES(2, 'c:\panmae.doc');
INSERT INTO TBLFILEPATH
VALUES(3, 'd:\aaa\bbb\humaninfo.hwp');
INSERT INTO TBLFILEPATH
VALUES(4, 'd:\aaa\salesinfo.xls');

COMMIT;

SELECT * FROM TBLFILEPATH;

이중에서 경로를 제외하고 파일명 만을 가져오는 테이블을 가져와보도록 하자.

SELECT FILENO,
SUBSTR(REVERSE(SUBSTR(REVERSE(filename),1,instr(REVERSE(filename),'\',1))),2) "Filename"  FROM TBLFILEPATH;