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.