CODEWITHSUNDEEP
sqlpostgresqlsqlite
Mariusz Jucha
Mariusz Jucha

Reputation: 93

SQL, between operator but using only one column

I want, with a single SQL query, check if number (eg. 12, 20) is in range, eg. 1-20.

Live example: I have table rules:

ID  name    range
1   rule_1  1-20
2   rule_2  21-50

etc.

Each GET /rule request will generate random number (from range 1 - 100), then I need to get rules which range value covers that number.

How with SQL query get rule which range definition covers given number, for example for number = 5 it should return rule_1, for number = 40 it should return rule_2 etc etc.

The query should work in PostgreSQL and SQLite.

Upvotes: 0

Views: 174

Answers (2)

forpas
forpas

Reputation: 164099

Postgresql and SQLite have string functions needed to parse a string and convert it to a number, but they are different in syntax.
For SQLite:

select *
from rules
where ? between 
  range + 0
  and
  substr(range, instr(range, '-') + 1) + 0

See the demo.
For Postgresql:

select *
from rules
where ? between 
  substring(range, 1, position('-' in range) - 1)::int
  and
  substring(range, position('-' in range) + 1)::int

I used substring() function here just because it works also in SQLite, although there is also split_part() in Postgresql.
See the demo.

Upvotes: 3

Gordon Linoff
Gordon Linoff

Reputation: 1269953

Fix your rules so the range is two columns:

ID  name    range_lo    range_hi
1   rule_1  1           20
2   rule_2  21          50

Then you can simply do:

select r.*
from ranges r
where ? >= range_lo and ? <= range_hi;

If you want the ranges to be non-overlapping with no gaps, you could also use:

ID  name    range_start
1   rule_1  1
2   rule_2  21

And then:

select r.*
from ranges r
where ? >= range_start
order by range_start desc
limit 1;

Under no circumstances should you be storing numeric ranges as strings.

Upvotes: 1

Related Questions