Abstract Confusions

Complexity is not a cause of confusion. It is a result of it.

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?


create or
replace function ret_num (p_str in varchar2)
return number
  --- 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.
   when value_error
   --- Now the classic case,
   --- most of the times this is expected to fire.
   return nvl( substr(p_str
                     , 1
                     , regexp_instr(p_str
             , 0  --- If the string starts
                  --- with aplhabet (not number)
                  --- then return 0. NVL() does that

Last Word

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.

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

     --- 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;


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)
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.


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

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


One response to “Some Interesting Requirements / Questions

  1. Pingback: Tweets that mention Some Interesting Requirements / Questions « Abstract Confusions -- Topsy.com

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s

%d bloggers like this: