[Oracle/SQL] instr, substr 을 이용해서 스트링 잘라내기

instr함수는 어떤 스트링의 특정위치에서 시작해서 특정 케릭터가 위치한 곳까지의 위치를 계산해서 반환해주는 함수입니다.

Java의 split 메소드와 같은 기능을 구현할때 오라클의 substr함수와 함께 같이 사용될 수 있죠.

오라클 문서에서 설명하는 instr함수에 대한 정의 및 문법은 아래와 같습니다.

The INSTR functions (INSTR, INSTRB, INSTRC, INSTR2, and INSTR4) searches a string for a substring using characters and returns the position in the string that is the first character of a specified occurrence of the substring. The functions vary in how they determine the position of the substring to return.

• INSTR calculates lengths using characters as defined by the input character set.

• INSTRB calculates lengths using bytes.

• INSTRC calculates lengths using Unicode complete characters.

• INSTR2 calculates lengths using UCS2 code points.

• INSTR4 calculates lengths using UCS4 code points.

반환값

A nonzero INTEGER when the search is successful or 0 (zero) when it is not.

문법 

{INSTR | INSTRB | INSTRC | INSTR2 | INSTR4} (string , substring [, position [, occurrence]])

그럼 간단한 예제를 한번 보시죠.

abc.def.ghi.jkl 이라는 패키지명이 있다고 해봅시다.

이 패키지명에서 abc.def ( 두번째 depth 까지)만 추출해내고 싶은데 그러려면 substr을 생각하실 수도 있겠죠.

select substr(packageNm, 1, 7) from ClassTable;

이렇게 하면 결과는 abc.def 가 나올 것입니다.

하지만 만약 패키지명이 3글자.3글자 형태가 아니면 어떻게 될까요?

a.b.c.d.e.f 라는 패키지명을 위처럼 자르면 a.b.c.d 라고 잘려서 나오겠죠?

두번째 depth가 아니라 7번째까지 출력이 되버리겠군요.

이때 instr 함수를 사용을 합니다.

select substr( packageNm, 1, instr( packageNm, '.', 1, 2) - 1 ) ) from ClassTable;

이렇게 함수를 써줍니다.

instr( packageNm, '.', 1, 2 ) 라는 부분은 packageNm이라는 스트링을 '.' (마침표)로 구분을 짓고 1번째 인덱스에서 시작해서 2번째 마침표가 나오는 인덱스를 반환을 합니다. 그러면 마침표가 있는 인덱스가 나오기 때문에 -1을 해준 값을 substr함수의 length에 넣어주면 두번째 depth까지 substr한 결과가 나오게 됩니다.