Power By 专否社区

sql where condition for int but column type is Varchar

Go To StackoverFlow.com

0

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.

2012-04-04 06:59
by Arung
What type of DB are you using? Add the correct tag for your DB - barsju 2012-04-04 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 2012-04-04 07:05
Yeah i knew.. But forgot to do it.. Thanks for your update. - Arung 2012-04-04 07:07

4

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.
2012-04-04 07:02
by Phil

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.

2012-04-04 07:06
by Mikael Eriksson

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

2012-04-04 07:07
by Prakash Patani

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
2012-04-04 07:12
by barsju
Ads