【오라클(Oracle) SQL 함수】 REGEXP_INSTR 함수

o REGEXP_INSTR

 

---

문 법

 

regexp_instr::=

---

목 적

문자열에서 지정한 패턴 조건(정규 표현식)을 만족하는 문자열의 최초의 위치(무슨 문자인지)를 반환한다. 또한, 검색을 시작하는 시작 위치를 지정하는 것도 가능합니다.

REGEXP_INSTR 함수는 INSTR 함수의 기능을 확장하여 문자열에서 정규 표현식 패턴에 해당하는 문자을 검색하는데 사용된다. 이 함수는 입력 문자 집합에 정의된 문자를 사용하여 문자열을 평가한다. 이 함수는 return_option 인수의 값에 따라 일치하는 부분 문자열의 시작 위치 또는 종료 위치를 나타내는 정수를 반환한다. 일치하는 값을 찾지 못하면 함수는 0의 값을 반환한다.

이 함수는 POSIX 정규 표현식 표준과 유니코드 정규 표현식 가이드라인을 따른다.

 

source_string 인수

source_string 인수는 검색 값으로 사용되는 문자 표현식이다. 일반적으로 문자열이고, CHAR, VARCHAR2, NCHAR, NVARCHAR2, CLOB, NCLOB 데이터 타입 중에서 사용 할 수 있다.

 

---

pattern 인수

pattern 인수는 정규 표현식이다. 일반적으로 텍스트 리터럴이고, CHAR, VARCHAR2, NCHAR, NVARCHAR2 데이터 타입 중에서 사용 할 수 있다. 최대 512 바이트까지 지정할 수 있다. 만약 pattern 인수의 데이터 타입이 source_char 인수의 데이터 타입과 다르다면, 오라클 데이터 베이스는 pattern 인수를 source_char 인수의 데이터 타입으로 변환한다. pattern 인수에서 지정할 수 있는 연산자 리스트는  "Oracle Regular Expression Support" 참조.

 

---

position 인수

position 인수는 오라클이 검색을 시작해야 하는 source_char 인수의 문자 위치를 나타내는 양의 정수이다. 기본값은 1이며, 이는 오라클이 source_string 인수의 첫 번째 문자에서 검색을 시작하는 것을 의미한다.

 

---

occurrence 인수

occurrence 인수는 source_char 문자열 인수에서 패턴 검색을 위하여 pattern 인수에서 지정한 패턴과 일치하는 문자열이 복수로 존재하는 경우에 여러 패턴 중에서 몇 번째 패턴을 검색해야 하는지를 나타내는 양의 정수이다. 기본값은 1이며, 오라클이 pattern 인수에서 지정한 패턴을 검색하여서 가장 먼저 검색된 첫 번째 패턴을 검색한다는 것을 의미한다.

 occurrence 인수가 1보다 큰 경우, 데이터베이스는 pattern 인수에서 지정하는 패턴과 일치하는 첫 번째 패턴의 문자열을 검색하고, 바로 직후에 위치하는 첫 번째 문자에서 두 번째 패턴의 검색을 시작한다. 이 작업 방식은 첫 번째로 검색된 패턴 문자열의 두 번째 문자에서 두 번째 패턴 문자열의 검색을 시작하는 INSTR 함수와는 다르다.

 

---

return_option 인수

return_option 인수를 지정하면 검색 패턴을 발견(존재)하는 경우에 Oracle이 반환해야 할 항목을 지정 할 수 있다.

• 0의 값을 지정하면 Oracle은 검색하는 패턴이 발견된(존재하는) 문자열의 첫 번째 문자 위치를 반환한다. 이것이 기본값이다.
• 1의 값을 지정하면 오라클은 검색하는된 패턴이 발견된 문자열의 다음 문자의 위치를 반환한다.

 

다음 예제는 "Hello, world!" 문자열에서 "ll"의 위치를 찾는 프로그램이다. 첫 번째 쿼리에서 return_option 인수를 0으로 설정하여 발생하는 문자열의 첫 번째 문자 위치를 반환한다. 따라서 결과는 2가 된다. 두 번째 쿼리에서는 return_option 인수를 1로 설정하여 발생하는 문자열 다음의 위치를 반환한다. 따라서 결과는 3이 된다. 즉, 이 프로그램은 "Hello, world!" 문자열에서 "ll"의 위치가 3와 5 사이라는 것을 보여준다.

Oracle Program

SELECT REGEXP_INSTR('Hello, world!', 'll',1,1, 0) return_0, 
       REGEXP_INSTR('Hello, world!', 'll',1,1, 1) return_1
from dual

 

Results

RETURN_0	RETURN_1
----------------------------
   3	       	    5

 

---

match_param 인수

match_param 인수는 VARCHAR2 또는 CHAR 데이터 타입의 문자 표현식으로 함수의 기본적인 검색(매칭) 작업 방식을 변경할 수 있다. 이 매개변수의 작업방식은 REGEXP_COUNT 함수와 동일하다.

      match_param 인수에는 다음의 값을 하나 이상을 지정할 수 있다.

리터럴	내용
'i'	대소문자를 구분하지 않고 검색작업을 수행한다.
'c'	대소문자를 구분하여 검색작업을 수행한다.
'n'	임의의 모든 문자와 대응(일치)하는 기호인 마침표(.) 를 사용하여 줄바꿈 문자도 함께 대응하여 처리하도록 할 수 있도록 한다. 이 매개변수를 지정하지 않으면 마침표는 줄바꿈 문자와 대응(일치)하지 않는다.
'm'	소스 문자열을  다중 라인으로 처리한다. 오라클은 캐럿(^) 및 달러 기호($)를 각각 전체 입력 문자열의 시작과 끝으로 해석하는 것이 아니라, 다중 라인(줄바꿈 문자를 고려)을 고려하여서 입력 문자열 내 개별 행의 시작과 끝으로 해석한다. 이 매개변수를 지정하지 않으면 오라클은 줄바꿈 문자가 포함되어서 여러 라인으로 입력된 입력 문자열을 한 줄로 처리한다.
'x'	공백 문자를 무시한다. 기본적으로 공백 문자를 공백 문자로 처리한다.

match_param  인수에 복수의 모순된 값을 지정하면, 오라클은 마지막 문자 값을 이용한다. 예를 들어, 만약 'ic'를 지정하면, 오라클은 대소문자를 구분하여 'c'에 해당하는 검색 작업을 수행한다. 만약 위에서 설명한 문자와 다른 문자를 지정하면, 오라클은 에러를 발생한다.

만약 match_param 인수를 생략하면,

• • 기본 대/소문자 및 악센트 감도는 REGEXP_COUNT 함수의 결정된 데이터 정렬(대조, collation)에 의해 결정된다.
  • period(.)은 새 줄바꿈 문자와 일치하지 않는다.
  • 소스 문자열은 단일행으로써 취급된다.

 

---

subexpr 인수

부분(하위) 정규 표현식(subexpression)이 포함된 pattern 인수와 함께 사용되는 subexpr 인수는 부분 정규 표현식의 위치를 가리키는 0에서 9 사이의 정수로, 정규 표현식 함수에서 몇 번째 부분의 부분 정규 표현식을 추출할지 결정한다. subexpr 인수는 괄호로 묶인 패턴의 조각이다. 부분 정규 표현식은 중첩하여 사용 할 수 있다. 하위(부분) 정규 표현식은 패턴 내에서 왼쪽 괄호가 나타나는 순서대로 번호가 매겨진다. 즉, 패턴 내에 괄호가 여러 개 있으면, 왼쪽에서부터 오른쪽으로 괄호가 나타나는 순서대로 부분 표현식에 대한 번호가 매겨진다. 이렇게 부분(하위) 정규 표현식(subexpression) 에 번호를 매기는 이유는, 패턴을 분석하거나 처리하기 쉽도록 하기 위함이다.

예를 들어 다음과 같은 정규 표현식을 생각해 보자.

0123(((abc)(de)f)ghi)45(678)

이 표현식에는 다음과 같은 순서로 5개의 부분 정규 표현식이 존재한다. "abcdefghi", "abcdef", "abc", "de" , "678".

• subexpr 인수의 값이 0이면 pattern 인수와 일치하는 전체 부분 문자열의 위치가 반환된다.
• subexpr 인수가 0보다 크면, 일치하는 부분 문자열에서 부분 정규 표현식(subexpression)의 번호가 subexpr 인수에서 지정한 번호에 해당하는 부분 문자열 조각의 위치를 반환한다.

pattern 인수에서 subexpr 인수에서 지정한 부분 표현식이 없는 경우 함수는 0을 반환한다. subexpr 인수의 값이 null 값인 경우 NULL을 반환한다. subexpr 인수의 기본값은 0이다.

 

---

패키지 함수 비교 (Oracle, Pandas, R Prog, Dplyr, Sqldf, Pandasql, Data.Table)

• REGEXP_INSTR Oracle Function 프로그래밍 비교

 

---

관련 함수

함수명	함수내용
INSTR	문자 열중에서 지정한 문자를 지정하여서 처음 나타나는 위치를 숫자로 반환한다. (문자검색)
REPLACE	문자열 char에서 "search-string"을 검색하여서 일치하는 모든 부분을 "replacement_string"으로 변경한다.
REGEXP_REPLACE	문자열에서 지정한 패턴 조건(정규 표현식, 정규식)을 만족하는 부분을 검색하여서 다른 문자열로 치환합니다. 복잡한 치환/검색 조작을 가능하게 합니다.
REGEXP_SUBSTR	문자열에서 지정한 패턴 조건(정규표현식, 정규식)을 만족하는 부분의 문자열을 반환한다.
REGEXP_INSTR	문자열에서 지정한 패턴 조건(정규 표현식)을 만족하는 문자열의 최초의 위치(무슨 문자인지)를 반환한다. 또한, 검색을 시작하는 시작 위치를 지정하는 것도 가능합니다.
SUBSTR	문자열 Char에서 position 문자 위치로부터 substring_length 문자 길이만큼 문자열을 추출하여 반환한다.

 

---

예 제

다음 예제는 문자열을 조사하여, 공백 이외의 문자가 존재하는지 여부를 검색한다. 오라클은 문자열의 첫 번째 문자에서 검색을 시작하여, 하나 이상의 공백이 아닌 문자가 6번째로 나타나는 시작 위치(기본값)를 반환한다.

Oracle Program

SELECT REGEXP_INSTR('500 Oracle Parkway, Redwood Shores, CA',
                    '[^ ]+', 1, 6) "REGEXP_INSTR"
FROM   DUAL;

 

Results

REGEXP_INSTR
------------
          37

 

---

다음 예제에서는 대소문자에 관계없이 s, r 또는 p로 시작하고 그 뒤에 임의의 6개의 알파벳 문자가 존재하는 문자열을 검사한다. 오라클은 문자열에서 세 번째 문자에서 검색을 시작해서, 대소문자에 구분 없이 s, r 또는 p로 시작되는 7 글자 단어가 두 번째 출현한 다음에 나오는 문자열 내 문자의 위치를 반환한다.

Oracle Program

SELECT REGEXP_INSTR('500 Oracle Parkway, Redwood Shores, CA',
                    '[s|r|p][[:alpha:]]{6}', 3, 2, 1, 'i') "REGEXP_INSTR"
FROM   DUAL;

 

Results

REGEXP_INSTR
------------
          28

 

---

위 예제에 대한 좀 더 상세한 내용을 확인하기 위한 프로그램이다.

Oracle Program

SELECT REGEXP_INSTR('500 Oracle Parkway, Redwood Shores, CA',
                    '[s|r|p][[:alpha:]]{6}', 3, 2, 1, 'i') "REGEXP_INSTR",
       REGEXP_INSTR('500 Oracle Parkway, Redwood Shores, CA',
                    '[s|r|p][[:alpha:]]{6}', 3, 2, 0, 'i') "REGEXP_INSTR_REF1",
       REGEXP_SUBSTR('500 Oracle Parkway, Redwood Shores, CA',
                     '[s|r|p][[:alpha:]]{6}', 3, 2, 'i') "REGEXP_SUBSTR_REF2"
  FROM DUAL;

 

Results

REGEXP_INSTR	REGEXP_INSTR_REF1	REGEXP_SUBSTR_REF2
28	        21	                Redwood

 

---

이 함수는 패턴의 시작 위치를 반환하며, 따라서 INSTR 함수와 유사한 형태로 동작한다. REGEXP_INSTR 함수의 사용 방법은 표 6에서 확인할 수 있다. 두 함수의 가장 중요한 차이는 REGEXP_INSTR 함수를 이용하는 경우 특정 문자열이 아닌 패턴을 지정할 수 있으며, 따라서 훨씬 유연한 검색이 가능하다는 사실이다. 다음 예에서는 REGEXP_INSTR 함수를 사용하여 Joe Smith, 10045 Berry Lane, San Joseph, CA 91234 문자열에서 5 개의 숫자로 구성된 우편 번호 패턴의 시작 부분을 반환하고 있다. 정규 표현식 [[:digit:]]{5}를 사용하는 경우 우편 번호가 아닌 집 주소 번호의 시작 위치를 얻게 된다 (처음으로 검색되는 5 개 연속 숫자 패턴이 10045이기 때문입니다). 따라서 $ 메타 문자를 사용하여 표현식의 앵커를 라인 끝부분으로 지정해야 한다. 이렇게 하면 집 주소 번호에 관계없이 우편 번호의 시작 위치를 얻을 수 있다.

Oracle Program

SELECT REGEXP_INSTR('Joe Smith, 10045 Berry Lane, San Joseph, CA 91234',
                    '[[:digit:]]{5}$') AS rx_instr
FROM dual

 

Results

  RX_INSTR
----------
        45

 

---

B(b)또는 C(c)가 2번 이상 문자열에 들어 있는 데이터를 검색한다.

Oracle Program

SELECT * FROM TEST_REGEX 
WHERE  REGEXP_INSTR(VALUE, '[b-c]', 1, 2, 0, I)>0;

 

---

오라클 SQL 함수(Oracle SQL Function) 목록 리스트 링크

 

[SQL, Pandas, R Prog, Dplyr, SQLDF, PANDASQL, DATA.TABLE] SQL EMP 예제로 만나는 테이블 데이터 처리 방법 리스트 링크

2023.12 수정