Some Interesting Requirements / Questions

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.

Return Number

The function, say, foo should return only numbers for the following test cases.

Input	Output
foo(‘123’)	123
foo(‘123a’)	123
foo(‘a123’)	0

So, how would you do that?

Solution

create or
replace function ret_num (p_str in varchar2)
return number
is
begin
  --- Try to return, if it is pure number
  --- , it should go without exception.
  return to_number(p_str);

  --- The string contains Aplhabet.
  --- Return with logic.
exception
   when value_error
   then
   --- Now the classic case,
   --- most of the times this is expected to fire.
   return nvl( substr(p_str
                     , 1
                     , regexp_instr(p_str
                                   ,'[a-zA-Z]')-1
                     )
             , 0  --- If the string starts
                  --- with aplhabet (not number)
                  --- then return 0. NVL() does that
             );
end;

Last Word

Given a line or sentence, the function or program should return the last word.

Solution

You can do this in plain SQL, a function is just a convenience.

create or
replace function getWord(p_word_in in varchar2)
 return varchar2
is
    lv_out        varchar2(20);
begin

     --- 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
    select reverse(substr(reverse(p_word_in)
                         , 0
                         , instr(reverse(p_word_in), ' ')
                         )
                  )
      into lv_out
      from dual;

    return lv_out;

end;

Also, I think there is should be an efficient way of doing this.

Update:

The below SQL works, perfectly. 🙂

select substr( &sentence
                 , instr( &sentence
                         , ' '
                         , -1) + 1)
from dual

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.

Solution:

delete
  from mytable
 where rowid
    in (select rowid
          from (select rowid
                     , row_number() over (partition by col1
                                                     , col2
                                                 order by id
                                         )rn
                  from mytable
               )
         where rn > 5
       );

You can also add your own strange question / answer you might want to share.