database - SQLite - 如何获取字符串中所有字符的出现

Question

SQLite 中是否有可能从字符串中提取所有字母并计算它们的出现次数？字符串的示例如下所示：“这是一个示例”。

解决方案应如下所示：

字符 -> 出现

T->(1x),h->(1x),i->(2x),s->(2x),a->(2x),e->(2x),x->(1x),m ->(1x),l->(1x)

标签： databasestringsqlitecharcharacter

Answer 1

使用递归CTE：

with
  recursive cte as (
    select 1 pos, substr('This is an example', 1, 1) letter
    union all
    select pos + 1, substr('This is an example', pos + 1, 1)
    from cte
    where pos < length('This is an example')
   ),
   letters as (select distinct letter from cte where letter <> ' ')
select 
  letter, 
  length('This is an example') - length(replace('This is an example', letter, '')) counter
from letters

或者：

with
  recursive cte as (
    select 1 pos, substr('This is an example', 1, 1) letter
    union all
    select pos + 1, substr('This is an example', pos + 1, 1)
    from cte
    where pos < length('This is an example')
   )
select 
  letter, 
  count(*) counter
from cte
where letter <> ' '
group by letter

对于表中的列的情况：

create table tablename(id int, col text);

insert into tablename(id, col) values
(1, 'This is an example'),
(2, 'Another one'),
(3, 'XY');

with
  recursive cte as (
    select id, col, 1 pos, substr(col, 1, 1) letter
    from tablename
    union all
    select id, col, pos + 1, substr(col, pos + 1, 1)
    from cte
    where pos < length(col)
   )
select 
  id, col, 
  letter, 
  count(*) counter
from cte
where letter <> ' '
group by id, letter;

请参阅演示。