本文主要是介绍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" ?
BEGINsys.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
BEGINRETURN 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
ISc_2nd CONSTANT PLS_INTEGER:= INSTR (string_in, letter_in, 1, 2) ;c_4th CONSTANT PLS_INTEGER:= INSTR (string_in, letter_in, 1, 4) ;
BEGINRETURN 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
ISl_2nd PLS_INTEGER:= INSTR (string_in, letter_in, 1, 2);l_4th PLS_INTEGER:= INSTR (string_in, letter_in, l_2nd + 1, 2);
BEGINRETURN 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
BEGINRETURN 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
这篇关于INSTR函数浅析的文章就介绍到这儿，希望我们推荐的文章对编程师们有所帮助！
