Oracle INSTR()

오라클 INSTR()

 

INSTR()

   - 문자열과 비교하여 문자열의 위치를 return 해준다.

   - 비교할 문자열의 위치와, 발견횟수등을 정하여서도 사용이 가능하다. (position, occurence)

   - 검색관련해서도 사용가능

 

INSTR(string, substring)

INSTR(string, substring, position, occurence)

의 형태

 

The INSTR functions search string for substring. The search operation is defined as comparing the substring argument with substrings of string of the same length for equality until a match is found or there are no more substrings left. Each consecutive compared substring or string begins one character to the right(for forward searches) or one character to the left(for backword searches) from the first character of the previous compared substring. If a substring that is equal to substring is found, then the function returns an integer indicating the position of the first character of this substring. If no such substrings is found, then the function returns zero.

 

• position is an nonzero integer indicating the character of string where Oracle Database begins the search-that is, the position of the first character of the first substring to compare with substring. If position is negative, then Oracle counts backward from the end of string and then searches backward from the resulting position.
• occurence is an integer indicating which occurence of substring in string Oracle should search for. The value of occurence must be positive. If occurence is greater than 1, then the database does not return on the first match but continues comparing consecutive substrings of string, as described above, until match number occurence has been found.

INSTR accepts and returns positions in characters as defined by the input character set, with the first character of string having position 1. INSTRB usee bytes instead of characters. INSTRC uses Unicode complete characters. INSTR2 uses UCS2 code points. INSTR4 uses UCS4 code points.

 

string can be any of the data types CHAR, VARCHAR2, NCHAR, NVARCHAR2, CLOB, or NCLOB. The exceptions are INSTRC, INSTR2, and INSTR4, which do now allow string to be a CLOB or NCLOB.

 

substring can be any of the data types CHAR, VARCHAR2, NCHAR, NVARCHAR2, CLOB, or NLOB.

 

The value returned is of NUMBER data type. 

 

Both position and occurence must be of data type NUMBER, or any data type that can be implicitly converted to NUMBER, and must resolve to an integer. The default values of both position and occurence are 1, meaning Oracle begins searching at the first character of string for the first occurence of substring. The return value is relative to the beginning of string. regardless of the value position.

 

SQL> SELECT INSTR('CORPORATE FLOOR', 'OR') FROM DUAL ;

   -> 2

 

3번째 글자부터 시작해서 'OR'가 2번째 검색되는 위치

SQL> SELECT INSTR('CORPORATE FLOOR', 'OR', 3, 2) FROM DUAL ;

   --> 14

 

첫번째 글짜부터 시작해서 'OR'가 2번째 검색되는 위치

SQL> SELECT INSTR('CORPORATE FLOOR', 'OR', 1, 2) FROM DUAL ;

   -> 6