Problem Statement: Find how many times each character occurs in the given sentence. For Example if the given sentence is 'good morning' should return
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
lv_string := 'replace your input string here';
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;
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
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.