Oracle INSTR 특정 문자를 찾을 때 사용하는 함수

(oracle) INSTR 특정 문자를 찾을 때 사용하는 함수

oracle instr

오라클에서는 대상 문자열에 대해 특정 문자열을 포함하고 있는지를 확인할 때 'INSTR' 함수를 사용합니다.

 

'LIKE' 연산자는 조건절(WHERE 절)에서 특정 문자열이 포함된 데이터를 찾을 때 사용되지만, 'INSTR' 함수는 특정 문자열이 포함된 NUMBER 타입의 위치 정보를 반환하기 때문에 특정 문자열이 포함되었는지와 더불어 대상 문자열을 자를 때도 활용될 수 있습니다.

 

해당 포스팅에서는 'INSTR 함수의 기본적인 사용법 및 사용 예시'를 정리하였습니다.

 

---

INSTR 기본 형식 및 특징

INSTR(대상 문자열, 찾을 문자열 [, 찾기를 시작할 위치 [, 찾을 결과의 순번]])

 

'INSTR' 함수는 다음과 같은 기본 형식을 가지고 있으며, 함수의 결과로 NUMBER 데이터 타입의 값을 반환합니다.

(찾을 문자열이 없는 경우 0을 반환합니다.)

 

INSTR 함수에서 각 파라미터의 특징은 아래와 같습니다.

 

파라미터	생략 가능 여부	설명
대상 문자열	불가능	검색을 수행할 대상 문자열입니다.
찾을 문자열	불가능	대상 문자열에서 찾을 문자열입니다.
찾기를 시작할 위치	가능	대상 문자열에서 찾기를 시작할 위치입니다. 해당 파라미터가 생략되었을 때 기본 값은 1로 설정되어 있으며, 해당 값을 음수로 주었을 때는 대상 문자열 마지막에서 시작하여 맨 처음으로 반대 방향으로 검색을 진행하기 때문에 끝에서 부터 찾을 때 사용될 수 있습니다.
찾을 결과의 순번	가능	찾을 결과의 순번입니다. 생략되었을 때 기본 값은 1입니다.

 

---

INSTR 함수 기본 사용 및 활용 예시

 

1. 기본 사용법

-- 1. 기본 사용법
SELECT INSTR('CORPORATE FLOOR', 'OR') 
  FROM dual;

instr 기본 사용법

INSTR 함수의 기본 사용법입니다.

'찾기를 시작할 위치'와 '찾을 결과의 순번' 파라미터가 생략되었기 때문에 '대상 문자열'의 맨 앞에서부터 검색하여 첫 번째 발견되는 '찾을 문자열'의 위치를 반환합니다.

 

 

 

 

2. 대소문자 구문 없이 검색

-- 2. 대소문자 구분 없이 검색
SELECT INSTR('CORPORATE FLOOR', 'or') AS result1,
       INSTR(LOWER('CORPORATE FLOOR'), 'or') AS result2
  FROM dual;

instr 대소문자 구분 없이 검색

INSTR 함수는 대소문자를 구별하여 문자열을 검색합니다.

때문에 대소문자의 구별 없이 검색을 해야 하는 경우 다음 예시와 같이 'UPPER' 또는 'LOWER' 함수를 함께 써서 원하는 결과를 가져올 수 있습니다.

 

 

 

 

3. 찾기를 시작할 위치 설정

-- 3. 찾기를 시작할 위치 설정
SELECT INSTR('CORPORATE FLOOR', 'OR', 4) AS result1,
       INSTR('CORPORATE FLOOR', 'OR', 9) AS result2
  FROM dual;

instr 찾기를 시작할 위치 설정

'찾기를 시작할 위치'를 설정한 경우 예시의 결과처럼 '찾기를 시작할 위치' 다음에 발견되는 '대상 문자열'의 위치를 반환하게 됩니다.

 

 

 

 

4. 뒤에서부터 검색

-- 4. 뒤에서부터 검색
SELECT INSTR('CORPORATE FLOOR', 'OR', -1) AS result1,
       INSTR('CORPORATE FLOOR', 'OR', -5) AS result2
  FROM dual;

instr 뒤에서부터 검색

앞서 설명한 것처럼 '찾기를 시작할 위치' 값을 음수로 주는 경우 대상 문자열 마지막에서부터 거꾸로 검색을 시작합니다.

(각각 마지막에서 1번째 위치에서 검색을 시작한 결과와 마지막에서 5번째 위치에서 검색을 시작한 결과)

 

 

 

 

5. 찾을 결과의 순번 설정

-- 5. 찾을 결과의 순번 설정
SELECT INSTR('CORPORATE FLOOR', 'OR', 1, 1) AS first_result,
       INSTR('CORPORATE FLOOR', 'OR', 1, 2) AS second_result,
       INSTR('CORPORATE FLOOR', 'OR', 1, 3) AS third_result
  FROM dual;

instr 찾을 결과의 순번 설정

'찾을 결과의 순번'을 설정한 경우, 해당 순번에 맞는 '대상 문자열'의 위치를 반환하게 됩니다.

만약 위 예시에서 '찾을 결과의 순번'을 4로 설정할 경우, 4번째 '대상 문자열'은 발견되지 않기 때문에 결과는 '0'이 반환됩니다.

 

 

 

 

6. 이메일에서 도메인 추출하기

-- 6. 이메일에서 도메인 추출하기
SELECT SUBSTR('example@gmail.com', INSTR('example@gmail.com', '@') + 1) AS domain 
  FROM dual;

instr 이메일에서 도메인 추출하기

INSTR 함수를 통해 반환되는 위치 값을 SUBSTR과 함께 사용하여 예시와 같이 문자열을 자르는 기능으로도 활용할 수도 있습니다.

 

 

 

 

7. WHERE 조건절에 사용하기

-- 7. WHERE 조건절에 사용하기
SELECT *
  FROM notice n
 WHERE INSTR(n.title, '긴급') > 0;

 

INSTR 함수는 다음 예시처럼 WHERE 절에서 문자열이 포함되었는지를 확인하는 데 사용될 수도 있으며, 이 경우 WHERE n.title LIKE '%긴급%'과 같이 동작하게 됩니다.

 

하지만 다음과 같이 단순히 WHERE 절에서 문자열이 포함되었는지를 확인하는 경우 'INSTR'을 사용하는 것보다 'LIKE'를 사용하는 것이 성능상 더 효율적일 수 있습니다.

(경우에 따라 다를 수는 있지만 짧은 문자열의 경우 INSTR의 속도가 빠를 수 있으며, 문자열이 길 경우 대부분 LIKE의 속도가 빠릅니다.)

 

 

 

 

< 참고 자료 >

https://gent.tistory.com/22