clear markdown compare help best fiddles feedback dbanow.uk a leap of faith?
clear markdown donate comments/suggestions/bugs a leap of faith? bible365 diddy dollings
By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0. 1130830 fiddles created (16569 in the last week).

CREATE TABLE wordcount ( id serial PRIMARY KEY , description text ); INSERT INTO wordcount (description) VALUES ('What a great day') ,('This is a product. It is useful') ,($$['a', ' ', ' ', 'b']$$) ,($$['a', ' ', ' ', 'b' b ]$$) ,(' ?an? under_score : !an! under-score §$%&/ ') ,('%& .;:;') -- NO word at all , ('') -- empty string , (NULL) -- NULL , ('under_score under-score')
9 rows affected
 hidden batch(es)


SELECT id , COALESCE(cardinality(arr), 0) AS word_count , unique_word_count , description FROM ( SELECT *, string_to_array(trim(regexp_replace(description, '\W+', ' ', 'g')), ' ') AS arr FROM wordcount ) a LEFT JOIN LATERAL ( SELECT count(DISTINCT elem) AS unique_word_count FROM unnest(arr) elem ) b ON true;
id word_count unique_word_count description
1 4 4 What a great day
2 7 6 This is a product. It is useful
3 2 2 ['a', ' ', ' ', 'b']
4 3 2 ['a', ' ', ' ', 'b' b ]
5 5 4 ?an? under_score : !an! under-score §$%&/
6 0 0 %& .;:;
7 0 0
8 0 0
9 3 3 under_score under-score
 hidden batch(es)