SQL> select * from test;STR
------
a23232
5a343
b4343
5b5453
34c434
21d6 rows selected.
SQL> select abc,count(*) from
2 ( select (select max(substr(b.str,rownum,1))
3 from test a
4 where rownum <= length(b.str)) abc
5 from test b )
6* group by abc
SQL> /A COUNT(*)
- ----------
a 2
b 2
c 1
d 1
------
a23232
5a343
b4343
5b5453
34c434
21d6 rows selected.
SQL> select abc,count(*) from
2 ( select (select max(substr(b.str,rownum,1))
3 from test a
4 where rownum <= length(b.str)) abc
5 from test b )
6* group by abc
SQL> /A COUNT(*)
- ----------
a 2
b 2
c 1
d 1
select t1.c,count(*)
from
(
select subStr(t.str,inStr(translate(t.str,'abcdefgh','........'),'.'),1) c
from test t
) t1
group by c
1、替换掉所有字符为‘.’
2、查找‘.’的位置
3、取字符
4、分组 查询字符出现的次数
select t1.c,count(*)
from
(
select subStr(t.str,
inStr(translate(t.str,
'abcdefgh',
'........'
),
'.'),
1) c
from test t
) t1
group by c
1、替换掉所有字符为‘.’
2、查找‘.’的位置
3、取字符
4、分组 查询字符出现的次数
return varchar2
num number;
str varchar2(10);
as
begin
for i in 1..length(p_col) loop
str:=substr(p_col,i,1);
num:=substr(p_col,i,1)-1;
end loop;
exception
when others then
return str;
end;
/
select get_value(col_name),count(1) from a group by get_value(col_name);