# 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?

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

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