Thursday, March 4, 2010

Oracle Puzzle: How may O's ur sentense has

Problem Statement: Find how many times each character occurs in the given sentence. For Example if the given sentence is 'good morning' should return
g:2
o:3
d:1
'':1
m:1
r:1
n:2

It is quite simple if you opt for a pl/sql procedure. The inbuilt functions length and replance are just enough. Here is the procedure I had written to solve this problem. May be you can come up with better solution.

set serveroutput on
declare
lv_string varchar2(500);
lv_length number(2);
begin
lv_string := 'replace your input string here';
loop
lv_length := length(lv_string) - nvl(length (replace (lv_string, substr(lv_string,1,1))),0);
dbms_output.put_line ( substr(lv_string,1,1) || ': ' || to_char(lv_length));
lv_string := replace (lv_string, substr(lv_string,1,1));
exit when lv_string is null;
end loop;
end;

Can this be solved by a SQL statement? Yes. There is a way :-}
I have been wondering the power of ROWNUM many times. Indeed this is also a fine example. Here is my SQL solution to this problem,

select lv_char, count(lv_char)
from (select substr(lv_string,rownum,1) lv_char
from (select 'replace your input string here' lv_string
from all_objects)
where rownum<=length(lv_string))
group by lv_char;

Can you generate Monthly Calender for current year using a SQL query? Sounds interesting is not it? I took up this challenge couple of years back and solve it just by using DECODE and ROWNUM. Perhaps I can share that in another post.