Regular expression matching in PostgreSql

Question

I have a column in my table with the following values:

'2.190.44.6'
'2.185.36.5'
'2.180.85.8'
'2.180.53.5'
'2.170.49.5'
'2.160.55.8'
'2.145.37.5'

I want to fetch only latest values and be generic to address values which will be added in future.

Currently the following would not work:

select device from table
where device NOT LIKE '2.1[0-7]%.[0-7]%'

Results should be only the following values:

'2.190.44.6'
'2.185.36.5'
'2.180.85.8'
'2.180.53.5'

Thanks for the help in advance!!

Tim Biegeleisen · Accepted Answer · 2016-11-11 07:24:43Z

1

I think you can get away with just using substring here:

SELECT device
FROM yourTable
WHERE substring(device for 4) = '2.18' OR
      substring(device for 4) = '2.19'

See TechOnTheNet for a good resource on how to use Postgres' substring function.

Update:

If you plan on having a 2.0 release in the future, and you just want to match the first minor version pattern, then you can modify the query as follows:

SELECT device
FROM yourTable
WHERE substring(device from 3 for 2) = '18' OR
      substring(device from 3 for 2) = '19'

edited Nov 11, 2016 at 7:24

answered Nov 11, 2016 at 7:17

Tim Biegeleisen

526k32 gold badges323 silver badges399 bronze badges

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

Collectives™ on Stack Overflow

Regular expression matching in PostgreSql

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