Use SQL regex (LIKE) in SELECT statement

Question

I'm using SQL in pgadmin4 (Postgres). I query a list of IP's and occurences of the IP's. If it matches the regex I want to print the regex and the sum of occurences.

I'm querying the following:

select distinct dstaddress, _col1 from test where _col1 > 1 AND 
dstaddress LIKE '10.228.55.%' OR 
dstaddress LIKE '10.228.9.%' 
group by dstaddress, _col1

And this gives me as output the IP and its occurences:

"10.228.55.17"  365942
"10.228.9.104"  8
"10.228.9.105"  4
"10.228.9.106"  2
"10.228.9.107"  8
"10.228.9.108"  10
"10.228.9.109"  434
"10.228.9.110"  127

But as output I want to have the regex and the sum of occurences:

"10.228.55.%"   365942
"10.228.9.%"    593

How can I achieve this?

Gordon Linoff · Accepted Answer · 2020-11-02 13:03:14Z

1

Here is one method:

select v.pat, sum(t._col1)
from (values ('10.228.55.%'), ('10.228.9.%')) v(pat) left join
     test t
     on t._col1 > 1 and
        t.dstaddress like v.pat
group by v.pat;

Note: "occurrences" appears to be _col1. You would, of course, use whatever the appropriate column is, if that is not the case.

This puts the patterns in the derived table. They can then be used for the aggregation, without having to repeat them (a source of errors in the query).

answered Nov 2, 2020 at 13:03

Gordon Linoff

1.3m62 gold badges705 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Use SQL regex (LIKE) in SELECT statement

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related