Hi, BETWEEN for VARCHAR type field.

[Pistocel]

Hi,

My employer asked me to debug a part of the app. and it is written by a co-worker.
In the buggy query I found this in the table :

notice_period   Type:	varchar(255)

and in the query:

`notice_period` BETWEEN 0 AND 30

This conditions :
"BETWEEN 0 AND 30" return the results thar are >0 and <30, and other results that have in the field chars (like "No payment"). I can't change the table structure because the app. is very big. I need a work around.

Do you know haw I can make this query to work ?

If I search "BETWEEN 0 AND 30" it will give me only the results that have the 'notice_period' BETWEEN 0 AND 30 and not the rows that have other text in it ?

Thank you for the time,
Pistocel.

[r937]

you neglected to mention which database you're using

this is the Databases forum and there is a separate subform for MySQL, but i'm going to guess you're using MySQL anyway, because of the wacky backticks

try this --

WHERE notice_period REGEXP '[^[:digit:]]' = 0
  AND notice_period BETWEEN 0 AND 30