A few days ago, I ranted briefly about a pattern in Oracle SQL documentation. Just now, I bumped into a perfect example what I was ranting about. The rant, and the topic of this thread, is intended to document an objective, rational evaluation of a pattern I have suspect/perceive in oracle sql documentation. Perhaps my detection is invalid. Perhaps my detection is legitimate, but my assessment is invalid or grounded in the wrong level of orientation for using this world-leading product (Oracle SQL).
WHAT I DETECT
Simple information on basic functions often is not present in Oracle documentation. This frustrates me, because using more complicated information is usually easier and more relevant to me after I have grasped the basics.
EXAMPLE I JUST ENCOUNTERED
I need to extract a substring from a number that might contain a decimal point, in a position that varies randomly among values in the field from record to record. What I need is the character staring at beginning of the string and and ending wherever the decimal point is. Example: if the value in the haystack is 12345.678, I need to get 12345.
I am sure there are many approaches to this requirement. I am absolutely certain the approach occurring to me is sound from a professional standpoint. I am equally certain better approaches mostly likely exist, but my approach is plenty good for the situation at hand. So, what I want to do, in pseudo code form, is
substring_starting with first character and ending at a position in the string the substring function needs provided(function returning the position of a character in string(field value)))
Since I am new to Oracle SQL, I need to find the name and exact behavior of the Oracle function for substring and position_of_substing_in_string.
MY BEEF IN THIS CASE IS THE DOCUMENTATION FOR INSTR
Thanks to political bias not wrecking technical searches, it took only a few seconds to find my way to documetation for INSTR, a function which can provide the position of the decimal point in a string. I am expecting the documentation to say something like:
instr($haystack, $needle)
Find the numeric position of the first occurrence of needle in the haystack string.
What did I get?
First, a load of stuff I knew before starting a search for this function. What I am looking for is the sequence of parameters used, and an example I can apply to a situation as simple as mine.
I read 1% of this bogus opening, figuring an example would finally show up:
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.
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.
INSTR accepts and returns positions in characters as defined by the input character set, with the first character of string having position 1. INSTRB uses 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 not allow string to be a CLOB or NCLOB.
substring can be any of the data types CHAR, VARCHAR2, NCHAR, NVARCHAR2, CLOB, or NCLOB.
The value returned is of NUMBER data type.
Both position and occurrence 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 occurrence are 1, meaning Oracle begins searching at the first character of string for the first occurrence of substring. The return value is relative to the beginning of string, regardless of the value of position.
See Also:
Oracle Database Globalization Support Guide for more on character length.
Oracle Database SecureFiles and Large Objects Developer's Guide for more on character length.
Table 2-8 for more information on implicit conversion
Appendix C in Oracle Database Globalization Support Guide for the collation determination rules, which define the collation the INSTR functions use to compare the substring argument with substrings of string
When I found my way to the example: good grief:
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:
Copy
SELECT INSTR('CORPORATE FLOOR','OR', 3, 2) "Instring"
FROM DUAL;
Instring
----------
14
A few weeks ago, I figured out that “dual” is some sort of sample data that is a good reference when communicating with oraclers, but I don't know enough about it yet to be fluent.
So this first example shows us how to find the nth occurrance of substring. For first example, what is the problem showing how to find the first occurrence, that probably does not require as many parameters, since first occurrence most likely is a default;
Is this sketch showing the required and optional parameters?
MY IMAGINATIVE INTERPRETATION
This is just a guess, based on my unique life experience. I am guessing people at Oracle Inc and some of their first customers back in the in the 1970s were proud of their intelligence, considering it a distinction that supported their competitive position and had thirst for demonstrating it and acknowledging others who could match or at least not be exposed as stupid when interacting with them. I am thinking maybe for some reason Oracle Inc. had a culture of never insulting anyone's intelligence by never telling any anything that was simple, based a false belief that telling people things they might already know, or might have been able to anticipate is disrespectful. I am guessing some sort of corporate norm along those lines became a standard in their documentation which requires it never be simple.
Ok, everyone else's turn, if you are so inclined. What say you?
The one occurring to me is using a function that returns the position of the decimal point, combined with a substring function tat