INSTR函数浅析

--INSTR函数

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

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 of string begins one character to the right (for forward searches) or one character to the left (for backward 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 substring 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.

如果position值为负，则从右往左开始搜寻。

occurrence is an integer indicating which occurrence of substring in string Oracle should search for. The value of occurrence must be positive. If occurrence 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 occurrence has been found.

Examples

The following example searches the string CORPORATE FLOOR, beginning with the third character, for the string "OR". It returns the position in CORPORATE FLOOR at which the second occurrence of "OR" begins:

SELECT INSTR('CORPORATE FLOOR','OR', 3, 2) "Instring"
  FROM DUAL;
 
  Instring
----------
        14

--寻找CORPORATE FLOOR中从第三个字符开始第二次出现的'OR‘位置。

题目练习：


http://www.itpub.net/thread-2072122-1-1.html


哪些选项实现了一个名为plch_between_2_4的函数，使得下列代码块执行之后会显示 "enXFeue" ?

BEGIN
   sys.DBMS_OUTPUT.put_line (plch_between_2_4 ('StevenXFeuerstein', 'e'));
END;
/


(A) 
CREATE OR REPLACE FUNCTION plch_between_2_4 (string_in   IN VARCHAR2
                                      , letter_in   IN VARCHAR2)
   RETURN VARCHAR2
IS
BEGIN
   RETURN SUBSTR (string_in
                , INSTR (string_in, letter_in, 1, 2)
                ,   INSTR (string_in, letter_in, 1, 4)
                  - INSTR (string_in, letter_in, 1, 2)
                  + 1);
END;
/


(B) 
CREATE OR REPLACE FUNCTION plch_between_2_4 (string_in   IN VARCHAR2
                                           , letter_in   IN VARCHAR2)
   RETURN VARCHAR2
IS
   c_2nd   CONSTANT PLS_INTEGER
                       := INSTR (string_in, letter_in, 1, 2) ;
   c_4th   CONSTANT PLS_INTEGER
                       := INSTR (string_in, letter_in, 1, 4) ;
BEGIN
   RETURN SUBSTR (string_in, c_2nd, c_4th - c_2nd + 1);
END;
/




(C) 
CREATE OR REPLACE FUNCTION plch_between_2_4 (string_in   IN VARCHAR2
                                           , letter_in   IN VARCHAR2)
   RETURN VARCHAR2
IS
   l_2nd   PLS_INTEGER
              := INSTR (string_in, letter_in, 1, 2);
   l_4th   PLS_INTEGER
              := INSTR (string_in, letter_in, l_2nd + 1, 2);
BEGIN
   RETURN SUBSTR (string_in, l_2nd, l_4th - l_2nd + 1);
END;
/


(D) 
CREATE OR REPLACE FUNCTION plch_between_2_4 (string_in   IN VARCHAR2
                                           , letter_in   IN VARCHAR2)
   RETURN VARCHAR2
IS
BEGIN
   RETURN SUBSTR (string_in, letter_in, 2, 4);
END;
/

A选项中相当于SUBSTR（'StevenXFeuerstein', 5 , 11-5+1） 结果是enXFeue，BC选项其实和A选择表达的意思相同，D选项substr( string, start_position, [ length ] )参数超过了要求的所以报错，答案应该是ABC