akshay_rao akshay_rao - 1 month ago 9
SQL Question

Regular expression matching in PostgreSql

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!!

Answer

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'