I am trying to select all the rows in which COLUMN has a value between 1 and 9. The problem is the datatype for COLUMN is text and I have used the following code to get those rows:
SELECT * FROM TABLE WHERE COLUMN BETWEEN '1' AND '9';
Instead of getting rows with COLUMN value between 1 and 9, I also get rows with COLUMN values 10, 11 and so forth. How can I make it give me only rows with COLUMN values between 1 and 9?
If all of the values in the column are guaranteed to be integers™ you could do this:
SELECT * FROM TABLE WHERE CInt(COLUMN) BETWEEN 1 AND 9
For example like this:
SELECT * FROM TABLE WHERE COLUMN BETWEEN '1' AND '9' and LEN(COLUMN) = 1;
WHERE COLUMN in ('1','2','3','4','5','6','7','8','9') - which is another solution without BETWEEN. I agree that TABLE and COLUMN would probably cause conflicts if used thus. They are mere placeholders for the actual names here.You can convert column type in where clause.
SELECT * FROM TABLE WHERE convert(bigint,COLUMN) BETWEEN '1' AND '9';
but make sure all values can be converted to bigint type or u can use float/int etc.
