MYSQL select rows between a range on a text coloumn-CodePudding

I have a table named basicinfo and one of coloum named as iepNo and it has values based on a specific fortmat e.g

m-195001
m-195002
m-195003 and so on.

I want to get record based on the given iepNo range. e.g i want to get iepNo between m-233 to m-334

As of now i am using following query

SELECT * 
FROM basicinfo 
where iepNo BETWEEN "M-100" AND "M-200"

but it is returning me 2631 rows but in actual i should get only 100 rows between m-100 and m-200. The coloumn iepNo is varchar(13). Kindly guide me how i can resolve this issue.

CodePudding user response：

You could extract the ID value and cast to integer before comparing:

SELECT * 
FROM basicinfo 
WHERE CAST(SUBSTRING_INDEX(iepNo, '-', -1) AS UNSIGNED) BETWEEN 100 AND 200;