Page 1 of 1

Selecting integer values rows

Posted: Mon Mar 20, 2006 6:50 am
by anjanesh
Is there a MySQL equivalent of PHP's is_numeric ?
Im looking for a function to select integer-value rows only. The field type is varchar though and contains alpha-numeric values too.

Thanks

Posted: Mon Mar 20, 2006 10:20 am
by feyd
use casting or regexp.

Posted: Tue Mar 21, 2006 3:26 am
by anjanesh

Code: Select all

SELECT `id` , `col1`FROM `tbl1`WHERE CONVERT(`col1` , UNSIGNED INTEGER) = `col1`
is returning all the values - including ones with alhpa-numeric.

Posted: Tue Mar 21, 2006 8:42 am
by feyd

Code: Select all

mysql> SELECT CAST('five' AS SIGNED), CAST(CAST('five' AS SIGNED) AS CHAR) = 'five';
+------------------------+-----------------------------------------------+
| CAST('five' AS SIGNED) | CAST(CAST('five' AS SIGNED) AS CHAR) = 'five' |
+------------------------+-----------------------------------------------+
|                      0 |                                             0 |
+------------------------+-----------------------------------------------+
1 row in set, 2 warnings (0.02 sec)

mysql> SELECT CAST('3' AS SIGNED), CAST(CAST('3' AS SIGNED) AS CHAR) = '3';
+---------------------+-----------------------------------------+
| CAST('3' AS SIGNED) | CAST(CAST('3' AS SIGNED) AS CHAR) = '3' |
+---------------------+-----------------------------------------+
|                   3 |                                       1 |
+---------------------+-----------------------------------------+
1 row in set (0.00 sec)
STRCMP() could also be used.