CODEWITHSUNDEEP
sqlsqldatatypestype-conversion
Miko
Miko

Reputation: 2633

sql where condition for int but column type is Varchar

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.

Upvotes: 0

Views: 9148

Answers (5)

Hoque MD Zahidul
Hoque MD Zahidul

Reputation: 11949

In postgreSQL database you can convert like this :

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

Upvotes: 0

Phil
Phil

Reputation: 42991

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.

Upvotes: 4

Mikael Eriksson
Mikael Eriksson

Reputation: 138960

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.

Upvotes: 2

barsju
barsju

Reputation: 4446

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

Upvotes: 0

Prakash Patani
Prakash Patani

Reputation: 547

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

Upvotes: 0

Related Questions