Players wrote with the following concerns and my responses:
1. "I think there was a slight mistake in this quiz...it is hard to see the diff between '' and ' ', but this makes big difference in result. see SQL below. and results. That's why I didn't choose 3rd answer correctly. select replace ('abc#def#', '#', '') from dual; Result: abcdef select replace ('abc#def#', '#', ' ') from dual"
SF: Well, I don't think this was a mistake, per se, but I can see how it might have been difficult to tell. I am going to add to my list of "cautions" when writing and reviewing quizzes that we should avoid relying on the appearance of white space in our quizzes.
2. "It was asked to replace all occurrences of # with NULL. I wasn't aware that an empty string ('') was the same as NULL, While I understand what the spec says, (NULL or empty string will replace all occurrences, is it really equivalent to say it is setting it to NULL?"
SF: ah, yes, the old "NULL vs. zero-length string" issue. There are many viewpoints on this (see this thread for such a discussion, including a fine contribution by Justin Cave, winner of 3rd place in most recent playoff). The bottom line is that Oracle treats these as the same today and very likely always will.
[Side note: the link to Justin's name is the first time I have taken advantage of our public profile URL in a blog. :-) I hope you are doing the same, on your own blogs or websites. Give everyone an opportunity to learn about your PL/SQL career and your activity on the Challenge!]
3. "I would like to share with you that I just got (maybe too) suspicious because of the evil word 'NULL' and your formulation of the question as 'Which of the following uses of REPLACE will replace all the # symbols with NULL?' How can you tell if a certain character is being replaced by a null? All we can we see is the output. So why not ask: which of these usages would lead to 'abcabc' being printed. Or: which of these usages would lead to removing all of the '#' occurrences from the input string. Well never mind. Other developers probably do understand the question." and similarly "On today's quiz, I assume "replace all # characters with NULL" means "remove all # characters from the string, leaving the other characters." But this wasn't completely clear to me."
SF: Yes, I think your formulation is better - and I also need to be more careful about referring to NULL as opposed to "the NULL value" - but I don't believe that any changes in scoring is required.
4. Finally, a reviewer and player both noted that I could have (and felt that I should have) included another choice:
BEGIN DBMS_OUTPUT.put_line (replace ('abc#def#', '#'));That is, test your knowledge of the impact of relying on the default value for the third argument.
SF: Ah well, that would have been good to do, you are right. I will create another quiz on this particular nuance of REPLACE!
Your thoughts, dear players?