Over the time I received few interesting requirements and questions. Few of them are straight forward that you do it in minutes, and few other questions are strange enough that some time have to sit and etch out the solution which is not top of the head. It would be only fair to share the information here. I would give you the requirement / question, if you can answer them, please add in comments.
You can use any language you like (except 3rd requirement). I was forced to use PL/SQL because of the project requirements.
The function, say,
foo should return only numbers for the following test cases.
So, how would you do that?
replace function ret_num (p_str in varchar2)
--- Try to return, if it is pure number
--- , it should go without exception.
--- The string contains Aplhabet.
--- Return with logic.
--- Now the classic case,
--- most of the times this is expected to fire.
return nvl( substr(p_str
, 0 --- If the string starts
--- with aplhabet (not number)
--- then return 0. NVL() does that
Given a line or sentence, the function or program should return the last word.
You can do this in plain SQL, a function is just a convenience.
replace function getWord(p_word_in in varchar2)
--- The logic is to find the last word by
--- finding the last space. Reverse the sentence
--- find the first space and retrieve the word and
--- reverse the word again
, instr(reverse(p_word_in), ' ')
Also, I think there is should be an efficient way of doing this.
The below SQL works, perfectly.
select substr( &sentence
, instr( &sentence
, ' '
, -1) + 1)
Delete All But Some Records
I want to delete set of the rows from a table. I can decide which rows need to be deleted (conditions in WHERE clause). But I want to keep few of the records from deleting. That is, if I have a 100 records, I want to delete 95 rows from table and keep 5 records in table. Remember the records should be deleted only if the count of rows is more than 5 (retention condition).
A condition: You have to do this in plain SQL.
in (select rowid
from (select rowid
, row_number() over (partition by col1
order by id
where rn > 5
You can also add your own strange question / answer you might want to share.