I have table called Buttons. Buttons table i have column button_number . Table contain more than 100 records. Now i have to select a buttons number between 10 to 50. When i used this following query it's not returning zero row.
select * from Buttons where button_number >= '10' and button_number <='50'
What's the problem in the query. If i use the same query with different column(data type is int) then it's working fine. Is the problem because of the data type of the column? If so what's the fix for this?
Note : button_number column data type is Varchar.
{}
button), you get proper syntax highlighting. Much better than just making it bold - Damien_The_Unbeliever 2012-04-04 07:05
The button_number is varchar and you are trying to do an integer style comparison. You will need to cast the button_number column to an integer.
select * from Buttons where
convert(integer, button_number) >= 10 and convert(integer, button_number) <= 50
Edit
The above query will require a table scan since it needs to convert every button_number to an integer before applying the where clause. This isn't a problem for 100 rows, but would be an issues for large numbers.
As mentioned in Mikael Eriksson's answer, a better alternative would be to change the type of button_number to an integer type (if possible). This would have several advantages:
If so what's the fix for this?
The other answers tells you to cast the column to int
in the query. That will work for you if you only have integers in the column. If that is the case you should change the data type for column button_number
to int
so you don't risk having a character value in there that will break your query.
modify your query like below
select * from Buttons where cast(button_number as int) >= 10 and cast(button_number as int) <=50
but make sure that all the values of column "button_number" dont have any charcters
Depending on your DB type and your version, but in MySQL you might need to use SIGNED or UNSIGNED as your datatype:
SELECT * FROM Buttons
WHERE CONVERT(SIGNED, button_number) >= 10
AND CONVERT(SIGNED, button_number) <= 50