12 August 2010

Questions about the INSTR quiz of 11 August (1328)

A few players wrote with concerns about the INSTR quiz, which tested your knowledge of the impact of using a negative value for the POS argument to the function. Here we go: 1. "From my point of view today quiz is formulated ambiguously. None of the answers describes INSTR behavior really accurately. The _combination_ of the first and second answers does. For this reason I have selected no one of them, but I guess the other interpretation is possible as well in which case both of them shell be selected. It depends on how you interpret choices - as self-contained or as complimentary."
2. "Regarding today's question, I think two of the options can be a little incomplete, maybe misleading. This can be confusing, as it is not clear in the question if you meant "what is correct?" or "what is correct and complete?":
"Oracle searches for the NTH occurrence of the STR2 value backwards through STR1 (towards index position 1) - I didn't check this one, although I believe everything there is correct. But it fails to mention that is searches starting in position (length - POS)
"The search for STR2 begins from the end of STR1, instead of from the beginning. That is, a value for POS of -1 starts the search from the last character in STR1. A value for POS of -2 starts the search from the second-to-last character in STR1- I checked this one, and I think everything it says is correct. Still, to be *complete*, it probably had to mention that is searches backwards." My response: when I looked back over the choices for the quiz, I was surprised that I hadn't noticed when writing and reviewing the quiz how similar the two correct answers were. I generally try to avoid that, and make sure that each choice is more distinct. Having said that, I don't think there is actually any inaccuracy in either one - but I also agree that neither is a "complete" definition of INSTR. So let's address that: I do not claim in the assumptions or in this particular quiz that for a choice to be correct it has to describe completely a particular feature. Unless I state that in the particular question, all you should be concerned with is whether that choice itself is correct as a "self-contained" statement.


  1. Hello,

    you have written 'that choice itself is correct as a "self-contained"', unfortunately neither of supposedly correct choices are correct as "self-contained".

    Moreover, you have asked not for the "correct" description but for the "accurate" description and latter may be understood as "correct and complete".

  2. It's really a matter of how you interpret this phrase:

    "Which of the following statements correctly describes the impact of passing a negative integer for the POS parameter?"

    At first I also (maybe because I'm a non-native English speaker?) interpreted "correctly describes" as meaning "a correct and complete description."

    But when I read the answers I concluded that it only made sense if I interpreted it as "which statements are true (but not necessarily complete by themselves.)"

    I think the question and answers are OK, as the answers seem to make it obvious how "correctly describes" should be interpreted. But it can be confusing at first.

    Also I considered, that if it had been meant that the answers were false due to "incompleteness", that would have been a really tricky question then with no truly accurate answers. There have been devious answers before, but not that tricky ;-) Besides, if an answer had to be absolutely completely complete - it would have to be as long as the relevant section of the manual...

    Just my 2 bits...

  3. Hi steven, I remember when reading your book "Programming PL/SQL" you mentioned a special characteristic of INSTR with -ve POS.

    SELECT INSTR('dad went to see his dad','ad',-1,2) FROM DUAL;
    This gives 2 as output which searched backward.

    If we change it to
    SELECT INSTR('dad went to see his dad','ad',-2,1) FROM DUAL;
    This won't give 2 as output since it won't search backwords at all in this case but it searches fwd from that location and gives output as 22

    So if I take my second example none of the options were correct. But if i take my first example 2 options which were scored correct will be correct.

    I too got this ambiguity while answering the quiz but selected the more generic answer and was awarded 100% score. Please do let me know if my understanding is wrong.

  4. @Jay:
    When position is negative the search proceeds backwards but comparisons are still performed in the forward direction. In your second case the portion of the string beginning at offset -2 is "ad" which matches the search criteria.

  5. I do agree that if position is negative the search proceeds backwards but that was not always. Thats is what I am trying to say.
    See the optoins:
    1. "Oracle searches for the NTH occurrence of the STR2 value backwards through STR1 (towards index position 1)"
    2. "The search for STR2 begins from the end of STR1, instead of from the beginning. That is, a value for POS of -1 starts the search from the last character in STR1. A value for POS of -2 starts the search from the second-to-last character in STR1"

    Both these options are correct, provided I have taken a string such that it satisfies the above two options like my 1st example in previous post.

    What if a novice person tries to check and unfortunately chooses my 2nd kind of example? I am not saying that the optoins are wrong but the options might turn wrong based on the example we are trying to confirm that.

  6. @Kim Berg Hansen:
    Even replicating the sections from the Oracle's documentation may not serve to completely describe a function. For example, the pos argument to instr is specified to be a non-zero integer; however, Oracle does not raise an exception if a 0 is passed, instead instr returns 0. Neither does the documentation specify that NULL is returned if any of the passed arguments is NULL (although such behavior is common to most Oracle supplied functions).

    The following code includes "my_instr" which is functionally equivalent to INSTR (at least sufficiently so to demonstrate backward searching using your second case):

    FUNCTION my_instr(
    str1 VARCHAR2,
    str2 VARCHAR2,
    found_position PLS_INTEGER;
    max_position PLS_INTEGER;
    occurrence NATURAL := 0;
    search_increment PLS_INTEGER := 1;
    search_position PLS_INTEGER;
    str2_length PLS_INTEGER;
    END IF;

    str2_length := LENGTH(str2);
    max_position := LENGTH(str1) + 1 - str2_length;
    search_increment := SIGN(pos);
    search_position :=
    CASE search_increment
    WHEN -1 THEN LENGTH(str1) + 1 + LEAST(pos, 0 - str2_length)
    WHEN 1 THEN pos
    ELSE 0
    -- Value to be returned if search is unsuccessful.
    found_position := 0;

    WHILE found_position = 0 AND search_position BETWEEN 1 AND max_position LOOP
    put_line('search_position: ' || TO_CHAR(search_position, 'TM'));

    IF SUBSTR(str1, search_position, str2_length) = str2 THEN
    -- An occurrence of str2 has been found in str1.
    occurrence := occurrence + 1;

    IF occurrence = nth THEN
    -- The nth occurrence has been found.
    found_position := search_position;
    END IF;
    END IF;

    -- Even if the nth occurrence has been found, there are neither logical
    -- nor performance reasons not to increment the search_position.
    search_position := search_position + search_increment;
    END LOOP search;

    RETURN found_position;
    END my_instr;
    sys.DBMS_OUTPUT.put_line('my_instr return: '
    || TO_CHAR(my_instr(
    'dad went to see his dad',
    ), 'TM'));

    Whenever pos is negative, instr searches by moving the position for the start of the comparison backwards until either the nth match is found or all of the candidate positions have been tested. When str2 is a multi-character string, then the end of the comparison may occur after pos.

    The values in your second case are insufficient for ascertaining the behavior of instr because the output will be the same regardless of which direction the comparison start position moves during the search (in this specific case it never moves since the value in str2 occurs at pos in str1).

  7. Now I took a look on "Past quizes" and it looks as if I misread the exact wording of the quiz in question - it says "correctly" and by taking quiz I misread it as "accurately".

  8. Sometimes with potential answers like that, I just think Steven is testing us to ensure unless we have some elite speed readers/thinkers, there should be at least a few seconds delay until the selection of answers ;-)

    Both where accurate - perhaps liken them to the occasional business requirement descriptions that come from different places describing the same request.