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. 1130703 fiddles created (16584 in the last week).

CREATE TABLE wordcount (id SERIAL NOT NULL PRIMARY KEY, description TEXT NOT NULL); INSERT INTO wordcount (description) VALUES ($$What a great day$$); INSERT INTO wordcount (description) VALUES ($$This is a product. It is useful$$); INSERT INTO wordcount (description) VALUES ($$['a', ' ', ' ', 'b']$$);
1 rows affected
1 rows affected
1 rows affected
 hidden batch(es)


SELECT id, REGEXP_SPLIT_TO_TABLE(description, ',') FROM wordcount; -- Keeping the id field helps clarity, even if superfluous.
id regexp_split_to_table
1 What a great day
2 This is a product. It is useful
3 ['a'
3 ' '
3 ' '
3 'b']
 hidden batch(es)


SELECT id, REGEXP_REPLACE(REGEXP_SPLIT_TO_TABLE(description, ','), '[^a-zA-Z\s]', '', 'g') FROM wordcount; -- Remove all non-alpha, non-spaces. Otherwise the words "product" and "product." would -- be counted as different! Again, keeping the id field makes things clearer, -- even if not strictly necessary for purists
id regexp_replace
1 What a great day
2 This is a product It is useful
3 a
3
3
3 b
 hidden batch(es)


SELECT id, STRING_TO_ARRAY(REGEXP_REPLACE( REGEXP_SPLIT_TO_TABLE(description, ','), '[^\w\s]', '', 'g'), ' ') FROM wordcount; -- id again - not strictly necessary at this step.
id string_to_array
1 {What,a,great,day}
2 {This,is,a,product,It,is,useful}
3 {a}
3 {"","",""}
3 {"","",""}
3 {"",b}
 hidden batch(es)


WITH cte1 AS ( SELECT id, UNNEST(STRING_TO_ARRAY(REGEXP_REPLACE( REGEXP_SPLIT_TO_TABLE(description, ','), '[^\w\s]', '', 'g'), ' ')) as "word", description FROM wordcount ) SELECT id, COUNT(word) AS "Word_count", COUNT(DISTINCT(word)) AS "Distinct_count", description FROM cte1 WHERE LENGTH(word) > 0 GROUP BY id, description ORDER BY id;
id Word_count Distinct_count description
1 4 4 What a great day
2 7 6 This is a product. It is useful
3 2 2 ['a', ' ', ' ', 'b']
 hidden batch(es)