CODEWITHSUNDEEP
sqlregexpostgresqlcount
Ramzendo
Ramzendo

Reputation: 606

SQL count regex matches (PostgreSQL)

I would like to count the regex instances from a table. For example:

    message                    state
    ================================
    [foo] aaaa                 active
    [bar] aaaa                 idle
    [foo] bbbb                 idle
    [foo] cccc                 active
    [bar] dddd                 active
    [tar] eeee                 idle

What I would like to have is following:

    messageType               ocurrences
    ====================================
    [foo]                             3
    [bar]                             2
    [tar]                             1

Is there any way to do that? Any help would be greatly appreciated!

Upvotes: 4

Views: 6847

Answers (2)

Ramzendo
Ramzendo

Reputation: 606

just like the response above but with Postgres version:

select regexp_matches(message, '\[.+\]') as messageType, count (*)
from table1
group by regexp_matches(message, '\[.+\]')
order by count (*) desc;

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1270371

If you just want to count the first "word" in the message, then use substring_index():

select substring_index(message, ' ', 1) as messageType, count(*)
from table t
group by substring_index(message, ' ', 1)
order by count(*) desc;

EDIT:

You can do this in Postgres by looking for the first space:

select left(message, position(' ' in message) as messageType, count(*)
from table t
group by messageType
order by count(*) desc;

Upvotes: 2

Related Questions