11

I have a column in my table, it holds values such as 100012345. The column is varchar. Now I want to compare this to similiar values in a where:

... where myColumn > '100012345'

for example. How could I do that?

Thanks!

user1638055
  • 482
  • 2
  • 8
  • 24
  • Cast your string to the appropriate type (whatever type myColumn is). See [CAST and CONVERT (Transact-SQL)](http://msdn.microsoft.com/en-us/library/ms187928.aspx) – Jeremy Oct 05 '12 at 15:18
  • 1
    if both values are varchar, you don't have to cast them. Even if they are not, mysql should compare them properly anyway. select 123='123'; returns true for example but it might depend on your mysql config. – iouri Oct 05 '12 at 15:23

5 Answers5

16
select * from your_table
where cast(your_column as signed) = 100012345
juergen d
  • 201,996
  • 37
  • 293
  • 362
4

Have you tried to do it normally, like this:

... where myColumn > 100012345

That should work!, mysql automatically casts a string to number when it's in the context of a number operation. In the same way it casts a number to string if it's used in a string context. See the examples in the type conversion docs:

To cast a string to a numeric value in numeric context, you normally do not have to do anything other than to use the string value as though it were a number:

mysql> SELECT 1+'1';
       -> 2

If you use a string in an arithmetic operation, it is converted to a floating-point number during expression evaluation.

If you use a number in string context, the number automatically is converted to a string:

mysql> SELECT CONCAT('hello you ',2);
        -> 'hello you 2'
Nelson
  • 49,283
  • 8
  • 68
  • 81
2

You can cast the value to an integer:

CAST(myColumn AS INTEGER)

LeonardChallis
  • 7,759
  • 6
  • 45
  • 76
  • Your code is not working, you linked the documentation of CAST function but instead used syntax of CONVERT function – Kyborek Sep 05 '17 at 07:06
2

You don't have to cast strings into integers to compare, mysql does it automatically.

http://dev.mysql.com/doc/refman/5.0/en/comparison-operators.html

iouri
  • 2,919
  • 1
  • 14
  • 11
  • 1
    But if you want to e.g. get the max numeric value of a string column, you need to do e.g. SELECT MAX(the_value + 0), otherwise '99.0' is larger then '750'. – Per Lindberg Jan 22 '16 at 13:24
1

If your column field is of type varchar then you have to Cast the columns you want to compare else mysql will treat a value like 20 to be greater than 100000.You can do that by using the inbuilt cast function

select * from your_table
where cast(your_column as signed) > 100012345

You can also do

select * from your_table
    where 'your_column+0' > '100012345+0'

Mysql will add your string number to zero and then cast them as an integer

If your column field is of type INT then you can do the normal comparism

select * from your_table
where your_column > 100012345

N/B it is always appropriate to use the correct column types esp. when you know the value the column is expecting

boonu
  • 339
  • 2
  • 5