Solved

mySQL compare string as numeric

Posted on 2010-11-15
2
419 Views
Last Modified: 2012-05-10
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.
0
Comment
Question by:Darksbane
  • 2
2 Comments
 
LVL 6

Expert Comment

by:DalHorinek
ID: 34139122
Try select * from table where CAST(field, INT) < 2
0
 
LVL 6

Accepted Solution

by:
DalHorinek earned 500 total points
ID: 34139137
0

Featured Post

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In database programming, custom sort order seems to be necessary quite often, at least in my experience and time here at EE. Within the realm of custom sorting is the sorting of numbers and text independently (i.e., treating the numbers as number…
Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

809 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question