Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

MySql: Compare 2 strings which are numbers?

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!

like image 668
user1638055 Avatar asked Oct 05 '12 15:10

user1638055


People also ask

How do you compare strings and numbers?

If you want to compare their string values, then you should convert the integer to string before comparing (i.e. using String. valueOf() method). If you compare as integer values, then 5 is less than "123". If you compare as string values, then 5 is greater than "123".

How do you compare values in two strings?

1) By Using equals() Method It compares values of string for equality. String class provides the following two methods: public boolean equals(Object another) compares this string to the specified object. public boolean equalsIgnoreCase(String another) compares this string to another string, ignoring case.

Can you use == to compare two strings?

You should not use == (equality operator) to compare these strings because they compare the reference of the string, i.e. whether they are the same object or not. On the other hand, equals() method compares whether the value of the strings is equal, and not the object itself.

How do you know if two strings have the same value?

Using equals() method: equals() method in the strings used to check the string value equality whether they contain the same character sequence. Using == operator: == operator used to check the reference equality of the two strings, whether they are pointing towards the same string object.


2 Answers

select * from your_table
where cast(your_column as signed) = 100012345
like image 188
juergen d Avatar answered Oct 04 '22 13:10

juergen d


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'
like image 38
Nelson Avatar answered Oct 04 '22 13:10

Nelson