Is there a way to query mysql and only pull results that are numbers?

Question

I have this simple query and was wondering if it is even possible to only pull info where arc is only equal to numbers and not text.

Code:

$sql = mysql_query("SELECT * FROM `comics` ORDER BY `id` DESC LIMIT 20");

Answer 1

AFAIK, there is no "IsNumeric()" function in MySql.

You can, however, use a regex:

How do I check to see if a value is an integer in MySQL?

I'll assume you want to check a string value. One nice way is the REGEXP operator, matching the string to a regular expression. Simply do

select field from table where field REGEXP '^-?[0-9]+$';

this is reasonably fast. If your field is numeric, just test for

ceil(field) = field

Similarly:

http://forums.mysql.com/read.php?60,1907,38488

Answer 2

For academic purposes, I offer the case of implicit conversions. When performing a math function, MySQL will attempt to convert a string to an integer, returning 0 if it fails.

So...

SELECT 1 * '1'

Returns: 1

SELECT 1 * 'hello'

Returns 0

So if 0 is not a valid number in your field, you can simply do

SELECT * FROM comics WHERE arc * 1 > 0

Answer 3

Use a regular expression:

select * from comics where arc REGEXP '[0-9]+';