CODEWITHSUNDEEP
sqlpostgresql
user2201789
user2201789

Reputation: 1211

Query count at the column with delimiter

I have column with data like below.

A1
A2
A3
A1/B1
A1/B2
A1/B1/C1

Usually I copy then to excel and use the text to column function then count. Can I use query command to count them directly at server? Example count How do I count total number Which does not has / is 3 which has one / is 2 which has two / is 1

updated for SQL query

select ( len(colA) - len(replace(colA, '/', '')) ) as num_slashes 
from DBO.table1

got the result

how to count 0 has 3 counts, 1 has 2 counts, 2 has 1 count?

0
0
0
1
1
2

Upvotes: 0

Views: 823

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270573

I don't think Postgres has a function like regexp_count(). One method is the difference of the lengths of two strings. You can use:

select ( length(col) - length(replace(col, '/', '')) ) as num_slashes,
       count(*)
from t
group by num_slashes
order by num_slashes;

Perhaps a more Postgres-y way of doing this converts the value to an array and then takes the length:

select array_length(string_to_array(col, '/'), 1) - 1

Upvotes: 1

Related Questions