Yonggeun Shin

Yonggeun Shin

시행착오가 밑거름이 되는 공간

[SQL] Oracle 내장함수 - 문자 데이터 처리

업데이트:

개요

png

학습에 활용한 프로그램 버전 및 DB는 아래와 같다.

  • Oracle 11g
  • Toad for Oracle 13.3 trial
  • db : SCOTT

Oracle과 Toad 설치 및 SCOTT계정의 연동은 인터넷에 잘 설명된 블로그가 많으니 참고


1. 대.소문자 변형 함수

함수 설명
UPPER(문자열) 대문자로 변환
LOWER(문자열) 소문자로 변환
INITCAP(문자열) 첫 글자는 대문자, 나머지는 소문자 
SELECT ENAME, UPPER(ENAME), LOWER(ENAME), INITCAP(ENAME)
FROM EMP;

png

대소문자가 혼재되어 있는 문자열 인덱싱에 활용 예

SELECT *
FROM EMP
WHERE UPPER(ENAME)=UPPER('scott');

png


2. 문자열의 길이 LENGTH

SELECT ENAME, LENGTH(ENAME)
FROM EMP;

png

WHERE절에 활용

SELECT *
FROM EMP
WHERE LENGTH(ENAME) >= 6;

png

글자수가 아닌 바이트수 반환

SELECT LENGTH('한글'), LENGTHB('한글')
FROM DUAL;

png

DUAL 테이블이란?

  • 오라클 자체에서 제공되는 테이블로 (1,1) 사이즈이다.
  • 위와 같이 함수의 쓰임이나, 데이터 추가, 삭제 등 임시로 테스트 해볼때 많이 활용한다.


3. 문자열 일부 추출 SUBSTR

SUBSTR(문자열, 시작위치, 추출길이(생략 가능))
# 추출길이 생략 시, 시작위치 부터 끝까지 추출

JOB열, 5번째 부터 끝, 뒤에서 2번째 부터 끝, 1번째~2번쨰

SELECT JOB, SUBSTR(JOB, 5), SUBSTR(JOB, -2), SUBSTR(JOB,1,2)
FROM EMP;

png


4. 특정 문자 위치 INSTR

INSTR([대상 문자열(필수)],
	  [위치를 찾으려난 부분 문자(필수)],
      [찾기 시작할 위치(선택, default 1)],
      [몇번째 문자를 찾을 건지(선택, defualt 1)])

SELECT INSTR('HELLO, ORACLE', 'L') AS var1,
       INSTR('HELLO, ORACLE', 'L', 5) AS var2,
       INSTR('HELLO, ORACLE', 'L', 2, 2) AS var3
FROM DUAL;

png

WHERE절에 활용

# ENAME에 S가 포함된 데이터 추출
SELECT *
FROM EMP
WHERE INSTR(ENAME, 'S') > 0;

png


5. 문자열 대체 REPLACE

REPLACE([문자열 또는 열], [찾을 문자], [대체할 문자(default '')])

SELECT '010-1234-5678' AS BEFORE,
        REPLACE('010-1234-5678','-',' ') AS AFTER1,
        REPLACE('010-1234-5678','-') AS AFTER2
FROM DUAL;

png


6. 패딩(padding) LPAD, RPAD

패딩(padding) 이란, 문자열의 빈공간을 특정 문자로 채우는 것을 의미한다.

# 죄측 패딩
LPAD([문자열 또는 열], [자릿수], [채울 문자])
# 우측 패딩
RPAD([문자열 또는 열], [자릿수], [채울 문자])

SELECT 'Oracle',
        LPAD('Oracle', 10, '#') AS LPAD_1,
        LPAD('Oracle', 10) AS LPAD_2,
        LPAD('Oracle', 10, '=') AS RPAD_1,
        LPAD('Oracle', 10) AS RPAD_2
FROM DUAL;

png

*패딩(padding)은 주로 개인정보 비식별화, 전송방식 등에 활용된다.


7. 문자열 합치기 CONCAT

SELECT CONCAT(ENAME, ENAME) AS TEST1,
       CONCAT(ENAME, CONCAT(' : ', ENAME)) AS TEST2
FROM EMP;

png

다음과 같이 ||연산자를 이용해 같은 결과를 얻을 수 있다.

SELECT ENAME || ENAME,
       ENAME || ' : ' || ENAME
FROM EMP


8. 특정 문자 삭제 TRIM, LTRIM, RTRIM

8-1. TRIM

TRIM([삭제 옵션(선택)] [삭제할 문자(선택)] FROM [원본 문자열(필수)])

사용 방식은 위와 같으며 콤마(,)를 사용하지 않고, 선택 인자를 지정하지 않으면 default는 공백(‘ ‘)이 된다.

  • LEADING : 왼쪽 글자 삭제
  • TRAILING : 오른쪽 글자 삭제
  • BOTH : 양쪽 글자 모두 삭제
SELECT '[' || TRIM(' _ _Oracle_ _ ') || ']' AS TRIM,
       '[' || TRIM(LEADING FROM ' _ _Oracle_ _ ') || ']' AS TRIM_LEADING,
       '[' || TRIM(TRAILING FROM ' _ _Oracle_ _ ') || ']' AS TRIM_TRALING,
       '[' || TRIM(BOTH FROM ' _ _Oracle_ _ ') || ']' AS TRIM_BOTH
FROM DUAL;

png

8-2. LTRIM과 RTRIM

LTRIM은 좌측, RTRIM은 우측 문자를 삭제하는데 TRIM과의 차이점은 삭제할 문자를 여러개 지정할 수 있다는 것이다. default는 역시 공백(‘ ‘)이다.

LTRIM([문자열 데이터], [삭제할 문자 집합(선택)])
RTRIM([문자열 데이터], [삭제할 문자 집합(선택)])

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;

png

  • 주의 : >_는 하나의 문자열로 보는게 아니라 >와 _의 개별 문자열의 집합으로 보는 것으로, 이 둘에 해당되는 문자열을 삭제한다는 의미
  • 즉, <_순으로 쓰지 않아도 무방하다.

댓글남기기

참고