Problem:
Given a string e.g 'gaurav goel' , return the individual character of the string along with its count in the whole string, with order kept intact.
The result should be:
g 2
a 2
u 1
r 1
v 1
NULL 1
0 1
e 1
l 1
Solution:
select ind,cn
from
(
select ind,cn,row_number() over (partition by ind order by ind) rownumber from
(
select
substr(given_input,rownum,1) ind,
rownum rn,
count(substr(given_input,rownum,1)) over (partition by substr(given_input,rownum,1)order by substr(given_input,rownum,1)) cn
from
(
select 'gaurav goel' given_input from all_objects
)
where
rownum<=length(given_input)
)
order by rn
)
where
rownumber=1
Another way to handle this
ReplyDeleteSELECT ssplit.string_split, ssplit.COUNT
FROM (SELECT sinner.string_split, COUNT (sinner.string_split) COUNT
FROM (SELECT ROWNUM rn, SUBSTR ('kartik sharma', ROWNUM, 1) string_split
FROM all_objects) sinner
WHERE ROWNUM < LENGTH ('kartik sharma') + 1
GROUP BY sinner.string_split) ssplit,
(SELECT string_split, MIN (rownum_generate.rn) string_order
FROM (SELECT ROWNUM rn, SUBSTR ('kartik sharma', ROWNUM, 1) string_split
FROM all_objects) rownum_generate
WHERE ROWNUM < LENGTH ('kartik sharma') + 1
GROUP BY string_split) ssplit1
WHERE ssplit.string_split = ssplit1.string_split
ORDER BY ssplit1.string_order
Cheers
Kartik
Mate,
ReplyDeleteThis query is awesome and at the same time esoteric.
I am a novice, who finds this beyond my kin but glamorous.
This is analogouslooking at a super model from the closest distance yet too far to speak to her ;-)
Cheers
Kaushik