CODEWITHSUNDEEP
mysqlregexparsingrangeintervals
Marius
Marius

Reputation: 980

MySQL: check if a value is contained in a range interval

I was wondering if it is possible to check if a string value is contained in a column given that the column contains the start and end values.

For example: if the table has a column NR with the following rows:

400-500
45-76,23-25
12,14-19,21

I want to find the row which has the value 421 in it. So the answer should be the first row.

Is this possible in mysql?

Upvotes: 4

Views: 4349

Answers (2)

Nicola Cossu
Nicola Cossu

Reputation: 56397

I agree with the above comments. Add two columns to your table and update it using substring_index() function

update table
set lft_value = substring_index(value,'-',1),
rgt_value = substring_index(value,'-',-1)

If everything works fine remove your original values. Now it will be very simple to find values within lower and upper limits.

edit. Keeping your table structure the query would become:

select * from table
where 421 between
substring_index(value,'-',1),
and substring_index(value,'-',-1)

but I don't like this solution.

Upvotes: 0

Álvaro González
Álvaro González

Reputation: 146490

You should have two tables: one for columns, one for column ranges. With that, a simple query will retrieve what you need.

CREATE TABLE foo (
    foo_id INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
    PRIMARY KEY (foo_id)
)
ENGINE=InnoDB;

CREATE TABLE foo_range (
    foo_id INT(10) UNSIGNED NOT NULL,
    column_from INT(10) UNSIGNED NOT NULL,
    column_to INT(10) UNSIGNED NOT NULL,
    INDEX foo_range_fk1 (foo_id),
    CONSTRAINT foo_range_fk1 FOREIGN KEY (foo_id) REFERENCES foo (foo_id)
)
ENGINE=InnoDB;

INSERT INTO foo(foo_id)
VALUES (1), (2), (3);

INSERT INTO foo_range(foo_id, column_from, column_to)
VALUES (1, 400, 500), (2, 45, 74), (2, 23, 25), (3, 12, 14), (3, 19, 21);

SELECT foo_id
FROM foo_range
WHERE 421 BETWEEN column_from AND column_to;

And, actually, the main table is not even necessary unless you want to store additional data :)

If you are stuck with the other DB design, you'll probably have to retrieve all rows and use your client side language to do the matching.

Upvotes: 2

Related Questions