sql where condition for int but column type is Varchar

Question

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.

What type of DB are you using? Add the correct tag for your DB. — barsju, Apr 04 '12 at 07:01
If you mark up code by indenting by 4 spaces (or hitting the `{}` button), you get proper syntax highlighting. Much better than just making it bold. — Damien_The_Unbeliever, Apr 04 '12 at 07:05
Yeah i knew.. But forgot to do it.. Thanks for your update.. — Miko, Apr 04 '12 at 07:07

Phil · Accepted Answer · 2012-04-04T08:19:48.857

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:

There would be no chance of entering a non-integer value in the column
You would then be able to apply an integer index to the column. This would greatly improve the speed of your query when the table contains large numbers of rows.

Mikael Eriksson · Answer 2 · 2012-04-04T07:13:14.953

2

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.

edited Apr 04 '12 at 07:13

answered Apr 04 '12 at 07:06

Mikael Eriksson

136,425
22
210
281

score 0 · Answer 3 · answered Apr 04 '12 at 07:07

0

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

answered Apr 04 '12 at 07:07

Prakash Patani

547
4
8

score 0 · Answer 4 · answered Apr 04 '12 at 07:12

0

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

answered Apr 04 '12 at 07:12

barsju

4,408
1
19
24

score 0 · Answer 5 · answered Sep 13 '20 at 11:21

0

In postgreSQL database you can convert like this :

select * from Buttons where 
    button_number::int >= 10 and convert(integer, button_number) <= 50

answered Sep 13 '20 at 11:21

Hoque MD Zahidul

10,560
2
37
40

sql where condition for int but column type is Varchar

5 Answers5

Linked