How to Find Out if an Entire String Is Numeric

Problem: Find out if an entire string is numeric.  (Just like the is_numeric function in php.)

Problem, Part II: Most online resources show how you can find out if part of your string is numeric.

Solution:

SELECT str FROM tbl
  WHERE str REGEXP('(^[0-9]+$)');

Give it a whirl!


For those who would like to view some test results, here goes:

mysql> SELECT str FROM tbl;
+-----+
| str |
+-----+
| 10  |
| 10A |
| 10E |
| 78  |
| E78 |
+-----+
5 rows in set (0.00 sec)

mysql> SELECT str FROM tbl WHERE str REGEXP('(^[0-9]+$)');
+-----+
| str |
+-----+
| 10  |
| 78  |
+-----+
2 rows in set (0.00 sec)

Now for the other way around:

mysql> SELECT str FROM tbl WHERE str NOT REGEXP('(^[0-9]+$)');
+-----+
| str |
+-----+
| 10A |
| 10E |
| E78 |
+-----+
3 rows in set (0.00 sec)