How to compose query in mysql to find range between text?

Question

MySQL

Help me write a request for this field

`cca` varchar(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,

There, the content in the column is text with ranges through -

|cca |
|-------|
|360-530|
|360-600|
|360-530|
|360-530|
|360-530|

I want to search in this column for a line in which the range lies between 360 and 530
That is, I should get everything except the second record - but I don’t know how to make a search query in a text field, please help.
Yes, you can't change the column type.

Answer 1

You can use a stored function - something like this:

drop function if exists checkRange;
 
DELIMITER //
 
CREATE FUNCTION checkRange(strRange varchar, from int, to int) RETURNS bool
   DETERMINISTIC
BEGIN
    DECLARE left INT;
    DECLARE right INT;
   
    SELECT strRange REGEXP ... INTO left
 
    SET result = ...;
 
    RETURN (result); 
END//
 
DELIMITER ;

....

SELCET * FROM table WHERE checkRange(randeColumn, 360, 530)