Darksbane
asked on
mySQL compare string as numeric
I'm querying a database I don't have control over. There is a string field which holds numbers, NULL and ''. What do I need to do to compare the string field as a number
Ex. I have the following values in the table
NULL
''
'0'
'1'
'2'
'11'
I want to select * from table where field < 2
It should only return 1 and 0 (I would also be OK with it treating NULL and '' as 0 and returning those), not the 11 or 2.
Ex. I have the following values in the table
NULL
''
'0'
'1'
'2'
'11'
I want to select * from table where field < 2
It should only return 1 and 0 (I would also be OK with it treating NULL and '' as 0 and returning those), not the 11 or 2.
Try select * from table where CAST(field, INT) < 2
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.