MySQL select rows between a range on a text column-CodePudding

I have a table named basicinfo and one of column named as iepNo and it has values based on a specific format 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 this query:

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

but it is returning 2631 rows but actually, I should get only 100 rows between m-100 and m-200. The column iepNo is of type 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;