CODEWITHSUNDEEP
sqlpostgresql
user10508414
user10508414

Reputation:

How to count each char from varchar field?

Need to get character and character count. The character with the highest character count will be reported first. How to count each char from varchar field?

CREATE TABLE name(
    id SERIAL PRIMARY KEY,
    value TEXT
);
INSERT INTO name (value) VALUES ('abcd');
INSERT INTO name (value) VALUES ('abcd efg');
INSERT INTO name (value) VALUES ('m');

My ouput expected

a 2
b 2
c 2
d 2
e 1
f 1
g 1
m 1

I wrote a query which got only single letter

SELECT 
 sum( 
    ROUND (   
        (
            LENGTH(value)
            - LENGTH( REPLACE ( value, 'a', '') ) 
        ) / LENGTH('a')        
    )) AS a    
FROM name

Upvotes: 2

Views: 109

Answers (2)

user330315
user330315

Reputation:

You can create a list of all characters using string_to_array() and unnest, and then group that result:

select t.c, count(*)
from name, unnest(string_to_array(value,null)) as t(c)
where t.c <> ' '
group by t.c
order by count(*) desc

Online example: https://rextester.com/JXYR32728

Upvotes: 1

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521239

I would go with a calendar table approach here:

SELECT
    t1.letter,
    COUNT(t2.name) AS cnt
FROM
(
    SELECT 'a' AS letter UNION ALL
    SELECT 'b' UNION ALL
    SELECT 'c' UNION ALL
    ...
    SELECT 'z'
) t1
LEFT JOIN names t2
    ON t2.name LIKE '%' || t1.letter || '%'
GROUP BY
    t1.letter
ORDER BY
    t1.letter;

Demo

Note that this reports all letters in the alphabet, even if they have a zero count. If you only want to see positive counts, then add the following to the end of the query:

HAVING COUNT(t2.name) > 0

Upvotes: 3

Related Questions