[Oracle] 문자 처리 함수(Function)의 모든것

오라클 문자 처리 함수

문자처리 함수는 문자와 관련된 특별한 조작을 위한 함수이고 단일 함수이기 때문에 테이블의 행 단위로 처리된다. 

 

대표적인 문자 처리 함수

 

문자 처리 함수 예

-- INITCAP
SELECT INITCAP('ORACLE SQL')
FROM dual;
--> Oracle Sql

-- UPPER
SELECT UPPER('Oracle SQL')
FROM dual;
--> ORACLE SQL
-- TIP: 문자열에서 대소문자인지 구분이 안될때 어퍼로 치환하고 찾기 좋음

-- LOWER
SELECT LOWER('Oracle SQL')
FROM dual;
--> oracle sql

-- CONCAT #하나 이상의 문자는 연결 불가능 -> || 연산자 사용
SELECT CONCAT('Oracle','SQL')
FROM dual;
--> OracleSQL

-- LENGHT: 문자열 길이 반환
SELECT LENGTH('Oracle')
FROM dual;
--> 6

-- INSTR: 문자열 특정 문자 나타내는 위치 반환
SELECT INSTR('MILLER', 'L', 1, 2), INSTR('MILLER', 'X', 1, 2)
FROM dual;
-- 4, 0

-- SUBSTR: 문자열에서 일부분의 문자열을 추출할 때 사용하는 함수
SELECT SUBSTR('900303-1234567', 8, 1)
FROM dual;
--> 1
SELECT SUBSTR('900303-1234567', 8)
FROM dual;
--> 1,2,3,4,5,6,7

-- REPLACE: 특정 문자열을 치환할때 많이 사용
SELECT 'JACK and JUE', REPLACE('JACK and JUE', 'J', 'BL')
FROM dual;
--> JACK and JUE, BLACK and BLUE

-- LPAD: 문자열을 오른쪽 정렬후 특정 문자를 왼쪽부터 지정한 문자로 채운다
SELECT LPAD('MILLER', 10, '*')
FROM dual;
--> ****MILLER

-- RPAD: 문자열을 왼쪽 정렬 후에 지정한 문자로 치환
SELECT RPAD('MILLER', 10, '*')
FROM dual;
--> MILLER****

-- LTRIM: 특정 문자 삭제하기 위해 사용(실무-공백지울때  사용)
SELECT LTRIM('MILLER', 'M')
FROM dual;
--> ILLER

-- RTRIM: 특정 문자 삭제하기 위해 사용
SELECT RTRIM('MILLER', 'R')
FROM dual;
--> MILLE

-- TRIM: 특정 문자 삭제하기 위해 사용(공백 지우기 목표)
SELECT TRIM(LEADING '0' FROM '0001234567000')
FROM dual;
--> 1234567000

SELECT TRIM(TRAILING '0' FROM '0001234567000')
FROM dual;
--> 0001234567

SELECT TRIM(BOTH '0' FROM '0001234567000')
FROM dual;
--> 1234567

SELECT TRIM('0' FROM '0001234567000')
FROM dual;
--> 1234567

 

INSTR 함수

INSTR 함수는 문자열에서 특정 문자가 나타나는 위치를 반환한다. 찾고자 하는 문자가 여러개 있는 경우에는 몇 번째로 나오는 문자를 검색할지를 옵션으로 설정 할 수 있다. 만약 찾고자 하는 자가 없는 경우에는 0 값을 번환한다. 

 

기본 Syntax,

INSTR( 컬럼명 | 표현식, 검색값, [m, n] )

예제,

INSTR( 'MILLER', 'L', 1, 2 )
FROM dual;

위 예제에서는 'MILLER'라는 단어에서 'L'의 위치를 반환하는 쿼리이다. 이때, 뒤에 [ 1, 2 ] 옵션을 주었는데, 여기서 1은 'MILLER' 라는 전체 문자의 몇번째 자리부터 'L'을 찾기 시작할것인지 정하는 옵션이고, '2'는 만약 단어에 'L'이 여러개라면 몇번째 'L'을 찾을지 정하는 옵션이다.

예제는, 첫번째 자리(인덱스)부터 'L'을 찾기 시작하여 두번째 'L'의 자리를 찾는 쿼리이다. 출력값으로는 '4'가 나온다.

 

SUBSTR 함수

SUBSTR 함수는 문자열에서 일부분의 문자열을 추출할때 사용한다. 실제로 실무에서 많이 사용하는 함수 중 하나이다. 

 

기본 Syntax,

SUBSTR( 컬럼명 | 표현식, m [,n] )

예제, 

SELECT SUBSTR('900303-1234567', 8, 2)
FROM dual;

위 예제에서는 '900303-1234567'의 8번째 자리부터 두자리 수를 반환하는 쿼리이다. 여기서 8(m)은 몇번째 자리부터 시작하는지를 정하고 2(n)은 시작 자리부터 몇번째 자리까지 출력하는지를 정한다. 만약, n을 생략하면 시작(m)부터 끝까지를 추출력한. 또한, m이 음수인 경우 뒤에서부터 시작하여 m번째 문자부터 반대방향으로 n개의 문자를 반환한다. 위 쿼리의 출력값은 '12'다. 

 

문자열 함수를 사용한 예제: 주민번호를 보안상 이유로 '900303-1******' 포맷으로 출력해라

-- 방법 1
SELECT SUBSTR('900303-1234567', 1, 8) || '******'
FROM dual;

-- 방법 2 
SELECT RPAD(SUBSTR('900303-1234567', 1, 8), 14, '*')
FROM dual;

-- 방법 3
SELECT REPLACE('900303-1234567', SUBSTR('900303-1234567', 9), '******')
FROM dual;