Oracle SQL 함수 정리 : 문자

계정 : scott

테이블명 : EMP, DEPT

 

 

1. 일반 테이블 조회

 

형식 : SELECT * | columns FROM [AS 별칭] tables;

 

SELECT 문으로 컬럼을 조회합니다. 컬럼 대신 *(아스터리스크)를 사용하면 모든 컬럼 조회가 가능합니다.

AS 명령어는 컬럼명의 별칭이나 가상 컬럼을 생성할 수 있으며 생략도 가능합니다.

 

SELECT * FROM EMP;

EMP

 

2. 문자 함수

 

(1) LOWER : LOWER(column)

column의 인자값이 대소문자로 혼합되어 있거나 대문자로만 되어 있는 문자열을 소문자 문자열로 변환

 

실습 : EMP 테이블의  ENAME 컬럼값인 영문자를 소문자로 변환하여 조회한다.

SELECT EMPNO, ENAME, LOWER(ENAME) FROM EMP
WHERE ENAME ='FORD';

 

 

(2) UPPER : UPPER(column)

column의 인자값이 대소문자로 혼합되어 있거나 소문자로만 되어 있는 문자열을 대문자 문자열로 변환

 

실습 : EMP 테이블의 ENAME 컬럼값인 영문자를 대문자로 변환하여 조회한다.

SELECT EMPNO, ENAME FROM EMP;
WHERE ENAME = UPPER('FORD')

SELECT EMPNO, ENAME, UPPER(ENAME) FROM EMP
WHERE ENAME ='FORD';

 

 

(3) INITCAP : INITCAP(column)

 column 의 인자값인 영문자의 첫 번째 문자를 대문자로 변환하고 나머지 문자는 소문자로 변환

 

실습: DEPT 테이블의 DNAME 컬럼값인 영문자와 LOC 컬럼값인 영문자에서 각 단어의 첫 번째 문자를 대문자로 나머지 문자는 소문자로 변환하여 조회한다.

SELECT DEPTNO, INTICAP(DNAME), INTICAP(LOC) FROM DEPT
WHERE DEPTNO = 10;

 

 

(4) CONCAT : CONCAT(column1, column2)

1. 인자인 column1 컬럼값을 column2 컬럼값과 연결한다.

 2. ||(더블 버티컬바) 연결 연산자와 같은 기능을 가지지만 두 개의 인자만 사용할 수 있다.

 

실습 : DEPT 테이블에서 DNAME 컬럼값과 loc 컬럼값을 연결한다.

SELECT DNAME, LOC, CONCAT(DNAME, LOC) FROM DEPT
WHERE DEPTNO = 10;

 

 

(5) SUBSTR : SUBSTR(column, m[,n])

1. 인자인 column 에서 인자인 m 위치에서 시작하는 문자열의 n 개 문자열 길이만큼 지정된 문자들을 반환하고 지정된 길이의 문자열을 추출한다.

2. m 이 음수면 문자 끝에서부터 카운트를 시작한다

3. n 이 생략된 경우 문자의 끝까지 모든 문자가 반환한다.

 

실습 : EMP 테이블의 ENAME 컬럼값인 영문자의 1 번째 문자에서 시작하여 2 번째 문자까지만 조회한다.

SELECT SUBSTR(ENAME, 1, 2) FROM EMP; /*영문자 1번째에서 2번째까지*/
SELECT SUBSTR(ENAME, -1) FROM EMP;   /*영문자 끝에서 1번째까지*/
SELECT SUBSTR(ENAME, 1) FROM EMP;    /*전체자리 출력*/

 

 

(6) LENGTH : LENGTH(column)

인자인 column의 문자열 개수를 반환하고 문자열 길이를 숫자로 표현한다.

 

실습 : DEPT 테이블의 DNAME 칼럼 값인 문자의 길이를 구한다.

 

SELECT DEPTNO, DNAME, LENGTH(DNAME) FROM DEPT
WHERE DEPTNO = 10;

 

 

 

(7) INSTR : INSTR(column, string, [m], [n])

1. 인자인 column 에서 지정된 문자열을 찾아내서 0(없다) 또는 1(있다)로 값을 반환하고 위치를 숫자로 표기한다.

2. 조회 시작 위치인 인자인 m 과 문자열의 발생수인 인자인 n 을 제공할 수 있다.

3. m 과 n 의 기본값은 1 이며 이 경우 문자열의 처음부터 조회를 시작하고 첫 번째로 찾은 결과를 반환한다.

 

실습 1 : DEPT 테이블의 DNAME 컬럼값에서 명명된 문자의 위치를 구한다.

SELECT DEPTNO, DNAME, INSTR(DNAME, 'G' FROM DEPT
WHERE DEPTNO = 10;

실습 2 : DEPT 테이블에서 LOC 컬럼에서 특정 문자를 가진 행만 조회한다.

SELECT DEPTNO, DNAME, LOC FROM DEPT
WHERE INSTR(LOC, 'DALLAS')>0;

실습 3 : DEPT 테이블에서 LOC 컬럼에서 특정 문자열을 가진 행을 제외하고 조회한다.

SELECT DEPTNO, DNAME, LOC FROM DEPT
WHERE INSTR(LOC, 'DALLAS')<1;

실습 1

실습 2

실습 3

 

 

(8) LPAD : LAPD(column, n, string)

인자인 column 의 길이가 인자인 n 이 되도록 왼쪽부터 문자로 채운 표현식을 반환하며 전체 길이 중 남는 부분 좌측에 주어진 문자로 채운다.

 

실습 : DEPT 테이블의 DNAME 컬럼에 * 문자를 지정한 15 만큼 왼쪽 자리 채운다.

SELECT DEPTNO, DNAME, LPAD(DNAME, 15, '*') FROM DEPT
WHERE DEPTNO=10;

 

 

(9) RPAD : RPAD(column, n, string)

인자인 column 의 길이가 인자인 n 이 되도록 오른쪽부터 문자로 채운 표현식을 반환하며 전체 길이 중 남는 부분 우측에 주어진 문자로 채운다.

 

실습 : DEPT 테이블의 DNAME 컬럼에 * 문자를 지정한 15 만큼 오른쪽 자리 채운다.

SELECT DEPTNO, DNAME, RPAD(DNAME, 15, '*') FROM DEPT
WHERE DEPTNO=10;

 

 

(10) TRIM : TRIM(LEADING|TRAILIING|BOTH trim_character FROM column)

1. LEADING은 문자열에서 오른쪽 문자를 제거
2. TRAILING은 문자열에서 왼쪽 문자를 제거
3. BOTH 는 문자열에서 양 끝에 있는 문자를 제거
4, trim_characte가 문자열 리터럴이면 단일 따옴표로 묶어야 함

 

실습 1 : DEPT 테이블의 DNAME 컬럼값인 문자의 양 끝에 있는 공백 문자를 제거한다.

SELECT DEPTNO, DNAME, TRIM(BOTH ' ' FROM DNAME) FROM DEPT
WHERE DEPTNO=10;

실습 2 : DEPT 테이블의 DNAME 컬럼값인 문자의 오른쪽에 있는 공백 문자를 지운다.

SELECT DEPTNO, DNAME, TRIM(TRAILING ' ' FROM DNAME) FROM DEPT
WHERE DEPTNO=10;

실습 3 : DEPT 테이블의 DNAME 컬럼값인 문자의 왼쪽에 있는 공백 문자를 지운다.

SELECT DEPTNO DNAME, TRANSLATE(DNAME, 'NG', 'SO') FROM DEPT
WHERE DEPTNO=10;

 

 

(11) TRANSLATE : TRANSLATE(column, string1, string2)

1. 인자인 column 에서 인자인 string1 문자열을 string2 문자열로 대체한다

2. 지정 문자열과 대체 문자열을 각각 하나의 문자로 대체한다.

 

실습 : DEPT 테이블의 DNAME 컬럼값인 문자열에서 NG 문자를 SO 문자로 대체한다

SELECT DEPTNO DNAME, TRANSLATE(DNAME, 'NG', 'SO') FROM DEPT
WHERE DEPTNO=10;

 

 

(12) REPLACE : REPLACE(text, search_string, replacement_string)

1. 인자인 text 에서 인자인 search_string 문자열을 replacement_string 문자열로 대체한다.

2. 지정된 정확한 문자열을 찾아서 대체 문자열로 변경한다.

*3. 정확한 문자열을 변경하려고 할 때는 replace 함수를 사용하고 문자 각각을 변경하려고 하면 translate 함수를 사용한다.

 

실습 : DEPT 테이블의 DNAME 컬럼값에 공백 문자열을 빈 문자열로 대체한다.

SELECT DEPTNO, DNAME, REPLACE(DNAME,' ', '') FROM DEPT
WHERE DEPTNO = 10;