오라클[sql] 문자형 함수

문자형

- UPPER : 대문자로 바꿔줌

- LOWER : 소문자로 바꿔줌

- INITCAP : 첫글자만 대문자로

- LENGTH : 글자길이  / LENGTHB : 바이트크기

- INSTR : 글자 위치 찾아주는 함수 : 없으면 0, 있으면 해당위치

- SUBSTR : 대상문자열에서 시작위치부터 몇 개를 추출하는 함수

- LPAD / RPAD

   LPAD : 오른쪽정렬을 한 후에 왼쪽에 생긴 공간에 특수 기호를 채우기

   RPAD : 왼쪽정렬을 한 후에 오른쪽에 생긴 공간에 특수 기호를 채우기

- REPLACE (문장, 바꿀문자열, 바뀔문자열)

- TRANSLATE (문장, 바꿀문자열, 바뀔문자열)

- TRIM : m 공백제거, 특정글자도 지워줌

 

 

UPPER / LOWER / INITCAP/ LENGTH

SELECT 'Hello World', UPPER('Hello World'), LOWER('Hello World') FROM dual;

SELECT 'hello world', INITCAP('hello world') FROM dual;

SELECT 'Hello World', LENGTH('Hello World'), LENGTHB('Hello World') FROM dual;

SELECT '헬로우 월드', LENGTH('헬로우 월드'), LENGTHB('헬로우 월드') FROM dual;

 

INSTR

글자 위치 찾아주는 함수 : 없으면 0, 있으면 해당위치

SELECT INSTR('Welcome To Oracle', 'e') FROM dual;

 

SUBSTR  / SUBSTRB

대상문자열에서 시작위치부터 몇 개를 추출하는 함수

SELECT SUBSTR('Welcome To Oracle', 4, 3)

             , SUBSTR('Welcome To Oracle', 1, 2)

FROM dual;

오라클에서 인덱스는 0이 아니라 1부터 시작

 

SELECT '안녕하세요오라클'
      , SUBSTR('안녕하세요오라클',4,3)
      , SUBSTRB('안녕하세요오라클',4,6)
FROM dual;

 

▶ 사원들의 입사년도와 입사 달 출력

SELECT ename

             , hiredate

             , SUBSTR(hiredate, 1, 2) AS "년도"

             , SUBSTR(hiredate, 4, 2) AS "월"

             , 19 || SUBSTR(hiredate, 1, 2) || '년 ' || SUBSTR(hiredate, 4, 2) || '월' AS "입사일"

FROM emp

WHERE hiredate < '810430';

 

LPAD / RPAD 

LPAD : 오른쪽정렬을 한 후에 왼쪽에 생긴 공간에 특수 기호를 채우기

RPAD : 왼쪽정렬을 한 후에 오른쪽에 생긴 공간에 특수 기호를 채우기

SELECT LPAD('Oracle', 20,'#') FROM dual;

SELECT RPAD('Oracle', 20,'#') FROM dual;

 

REPLACE(문장, 바꿀문자열, 바뀔문자열)

SELECT REPLACE ('내가 그린 기린 그림은 잘 그린 기린 그림이다.', '기린', '사자')

FROM dual;

 

TRANSLATE(문장, 바꿀문자열, 바뀔문자열)

SELECT TRANSLATE ('내가 그린 기린 그림은 잘 그린 기린 그림이다.', '기린', '사자')

FROM dual;

문자열 단위로 치환하는 것이 아니라 문자 한글자씩 매핑하여 치환

TRIM

공백제거, 특정글자도 지워줌

 

SELECT  '-' || trim(' 이순신 ') || '-' as col1

              , '-' || ltrim(' 이순신 ') || '-' as col2

              , '-' || rtrim(' 이순신 ') || '-' as col3

FROM dual;

SELECT TRIM( 'a' FROM 'aaaaaaaaaORACLEaaaaaa')

FROM dual;

 

예제

1. smith의 정보를 사원번호, 성명, 담당업무(소문자) 출력

SELECT empno, ename, LOWER(job) FROM emp

WHERE ename = 'SMITH';

 

2. 사원번호, 사원명(첫글자만 대문자), 담당업무(첫글자만 대문자)로 출력

SELECT empno, INITCAP(ename), INITCAP(job) FROM emp;

 

3.이름의 첫글자가 ‘K’보다 크고 ‘Y’보다 작은 사원의 정보( 사원번호, 이름, 업무, 급여, 부서번호)를 출력하되

이름순으로 정렬

SELECT empno, ename, job, sal, deptno FROM emp

WHERE SUBSTR(ename,1,1) BETWEEN 'K' AND 'Y'

ORDER BY ename;

SELECT empno, ename, comm, job, sal, deptno

from emp

WHERE substr(ename,1,1)>'K' And substr(ename,1,1)<'Y' order by ename;

 

4. 이름이 5글자 이상인 사원들을 출력

SELECT * FROM emp

WHERE LENGTH(ename) >= 5;

 

5. 이름을 15자로 맞추고 글자는 왼쪽에 오른쪽에는 ‘*’로 채운다

SELECT RPAD(ename, 15, '*') FROM emp;

 

6. 급여는 10자로 맞추고 숫자는 오른쪽에 왼쪽엔 ‘-‘로 채운다

SELECT LPAD(sal, 10, '-') FROM emp;

 

7. 급여를 숫자에서 ‘영일이삼사오육칠팔구’ 글자로 대체

SELECT TRANSLATE(sal, '0123456789', '영일이삼사오육칠팔구') FROM emp;

 

8. 급여의 숫자에서 0을 ‘$’로 바꾸어 출력

SELECT REPLACE(sal, '0', '$') AS "급여" FROM emp;