List characters (and their count) which meet a regex-CodePudding

Home > Mobile > List characters (and their count) which meet a regex

List characters (and their count) which meet a regex

Time：01-12

This example lists the result of column col, which does not contain solely alphanumeric characters:

select col
from foo
where col ~ '[^a-zA-Z0-9]';

My aim is to list all the different characters which meet the regex with the count of their occurrence.

CodePudding user response：

Try this :

SELECT c.char[1], count(*) AS count
  FROM foo
 CROSS JOIN LATERAL regexp_matches(col, '[^a-zA-Z0-9]', 'g') AS c(char)
 GROUP BY c.char

see the test result in dbfiddle.