Oracle Instr FunctionOral Instr Function
Oracle INSTR Function
ORACLE INSTR function looks for a partial character chain within a character chain. Oracle INSTR works similarly to SUBSTR, but INSTR will return an integral that specifies the location of the substring within the character chain instead of return the substring itself. You can use Regular Expressions for more advanced character chain editing features by using the advanced INSTR function REGEXPR_INSTR.
The [, [, [, [, source_string statement is the search string. The substring statement is the search string within a source_string statement. begin_position is an optionally available key. It' an integral value that Oracle says where to begin the search in your code strings. Oracle will count down the number of digits from the end of the code_string and search backwards from that number.
When omitted, the default value is 1. occurrence is a whole number specifying what occurence of partial string Oracle should look for. This means that INSTR should give back the first matched string, the second matched string, and so on. The Oracle INSTR function returns 0 if the substring is not found in source_string.
source_string as well as substring can be any of the data types CHAR, VARCHAR2, NCHAR, NVARCHAR2, CLOB or NCLOB. The both the command line item and the event must be an integral number of the data type NUMBER or any data type that can be changed to NUMBER by implication. In the source_string, the first item is 1, not 0. INSTR searching is case-sensitive.
The INSTR Function
INSTR The INSTR function (INSTR, INSTRB, INSTRC, INSTRC, INSTR2, and INSTR4 ) looks for a partial substring with strings and retrieves the location in the strings that is the first sign of a given occurence of the partial strings. Each function differs in the way it determines the location of the substring to be returned.
The INSTR computes length using symbols specified by the entered font. With INSTRB the length is calculated from byte. The INSTRC computes length with Unicode prefix. Using UCS2 points, you can calculate length with your UCS2 system. Using UCS4 points, you can calculate length with your own UCS4 points. Non-zero value indicates that the scan was a success, non-zero value indicates that the scan was a success, zero value indicates that the scan was a failure.
Name of the text to be searched for. Name of the substring to be searched for. Non-zero IDEGER that indicates where in the substring the function starts the lookup. The INSTR computes the location using chars specified by the entered font. ENSTRB computes the location from byte. The INSTRC computes the location with Unicode full char. ENSTR2 computes the item using UCS2 points.
Calculate the item from UCS4 points using USTR4. lf the item is negativ, then INSTR will count and look backwards from the end of the character chain. This means that the function starts the query at the beginning of the character chain. A INTEGER that specifies the occurence of a character chain that the function should look for.
Appearance value must be negative. Standard occurrences are 1, i.e. the function looks for the first occurence of substrings. In the following example, the search for the text string "Corporate Floor" starts with the third sign and ends with the text word "or". There is the return item in "Corporate Floor" where the second appearance of "or" begins.
This next example will count the function backwards from the last digit to the third digit of the end, which is the first "o" in "Floor". This function then looks backwards for the second occurence of "or" and determines that this second occurence begins with the second sign in the name.
In this example, a double-byte data base charset is assumed.