An example, how can represent the number of times 'L','E','T','R','B','K' appears in LETTERBOOK

CodePudding user response：

If you are using SQL server.

with letters as (
select substring(a.b, v.number 1, 1) letter
from (select 'LETTERBOOK' b) a
join master..spt_values v on v.number < len(a.b)
where v.type = 'P'
)
select
  letter,
  count(letter) "number"
from letters
group by letter
order by letter
GO

letter | number
:----- | -----:
B      |      1
E      |      2
K      |      1
L      |      1
O      |      2
R      |      1
T      |      2

db<>fiddle here

CodePudding user response：

In PosetgreSQL you can use regexp_split_to_table

WITH letters AS (
    SELECT regexp_split_to_table('LETTERBOOK', '') s
) 
SELECT s, COUNT(*) 
FROM letters 
GROUP BY s 
ORDER BY 2 DESC;

PostgreSQL fiddle

CodePudding user response：

In Oracle:

with 
tab1 as (select 'letterbook' a from dual)
select
    regexp_substr(a,'[A-z]{1}',1,level) letter,
    count(*) "NUMBER"
from tab1
connect by level <= length(a)
group by regexp_substr(a,'[A-z]{1}',1,level)
order by regexp_substr(a,'[A-z]{1}',1,level);  

Result:

LETTER|NUMBER
    b   1
    e   2
    k   1
    l   1
    o   2
    r   1
    t   2

CodePudding user response：

In MySQL 8.0

select

  substring(s,rn,1) letter,
  count(*) "number"
from
(select 'letterbook' as s)s
join (
select row_number() over (order by col) rn
from 
(select 1 col union all select 1 union all select 1 union all select 1) a,
(select 1 union all select 1 union all select 1 union all select 1) b,
(select 1 union all select 1 union all select 1 union all select 1) c
)e
where rn <= length(s)
group by substring(s,rn,1)
order by substring(s,rn,1);

letter | number
:----- | -----:
b      |      1
e      |      2
k      |      1
l      |      1
o      |      2
r      |      1
t      |      2

db<>fiddle here