PSQL Select number range in text column

Databases
1

Hi!

I’m trying to find a way to do this, so, to draw a picture for you, let me say:

I’ve got a column - text type- containing alpha-numeric data (basically a piece of info and sizes)
“COL.050,00-025,00-08,0”
“COL.050,00-040,00-10,0 PUR”
“COL.055,00-040,00-12,0 PUR”
“COL.050,80-038,10-09,5”
“COL.070,00-060,00-10,00 XXX”
“RASC.035,00-045,00-07,0/10,0”

from wich I like to select records containing a number from a int range-number
kind of…
“select * from db where alphanumeric ~* numrange(40.0, 50.0)”
returning
“COL.050,00-025,00-08,0”
“COL.050,00-040,00-10,0 PUR”
“COL.055,00-040,00-12,0 PUR”
“RASC.035,00-045,00-07,0/10,0”

“select * from db where alphanumeric ~* numrange(30.0, 39.0)”
returning
“COL.050,80-038,10-09,5”
“RASC.035,00-045,00-07,0/10,0”

“select * from db where alphanumeric ~* numrange(60.0, 65.0)”
returning
“COL.070,00-060,00-10,00 XXX”

My idea is to do this on PostgreSQL as far as my target device is a mobile (so I think it will do calculations on my server faster than on an external device and table contains about 45000 records)

After a while search on internet I guess the calculations should involve numrange or generate_series, but I don’t arrive to use returned values on this string column.

By the way, I’m newer on PgSQL, so my knowledge is very basic.

Thank you

:face_with_monocle:

2

You can do that in any DB, including SQLite, just thinking differently.

Create 2 tables, one storing the lines (id, line), and another storing ranges: id_for_line, from_number, to_number

Get a line, store it, get the id.
Parse the line and extract all ranges there as a from-to pair, a single point can have the same value in from_number and to_number.
Insert several range rows for that line in the ranges table.

Later when desiring to know what lines contains a number in one of those ranges, query the ranges table, and consolidate multiple repeating id_for_line into just one and return those lines.

1 Like
3

…so easy!

From time to time I lost myself on trying to do calculations more difficult than should be.

I didn’t use a table itself but a subquery on wich I extract the needed values, then, I can compare.

select cod_art, descrip from(
   select cod_art, descrip, 
     case coalesce(substring(descrip,position('-' in descrip)-6,6) > '0' and substring(descrip,position('-' in descrip)-6,6) < 'a','f')
        WHEN true THEN to_number(replace(substring(descrip,position('-' in descrip)-6,6),',','.'),'999.99')
     END AS "a",
     case coalesce(substring(descrip,position('-' in descrip)-6,6) > '0' and substring(descrip,position('-' in descrip)-6,6) < 'a','f')
        WHEN true THEN to_number(replace(substring(descrip,position('-' in descrip)+1,6),',','.'),'999.99')
     END AS "b"
from art ) subtabla
where subtabla.a >=80 and subtabla.b <=90

Thank you for the idea

4

What @Rick_Araujo is saying indirectly, is that the data should not be saved in a field in a way that you need to parse it to get something.

You should create the tables in such a way that you only need to query, adding a clause to filter the returned rows when needed. It is very important, at the stage of creating the tables, that you know whow you will query the data.

This way you will determine how to properly set the tables. Sometimes you need to break the input data as he showed you.

5

OK

Nevertheless, given data is this way, so I can add a new column and, when importing data (in this case I’m controlling/updating data each 15 minutes), and extract this info and put it into defined columns.

By the way it’s a todo as far as I will need to control this data deeply…but for this matter it works fast enough