24 January 2012

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)

5 comments:

Rob Smith said...

The full regex for php's is_numeric is:
REGEXP('(^[+-]?[0-9]+\.?[0-9]*e?[0-9]+$)|(0x[0-9A-F]+)')

As it supports more then just ints

Rob Smith said...

Whoops, that should be:

REGEXP('^([+-]?[0-9]+\.?[0-9]*e?[0-9]+)|(0x[0-9A-F]+)$')

That way it only matches a full hex value

Unknown said...

Couldn't find any resources on how to test if part of a string is numeric - how would you test if the first two characters of a string were numeric?

MySQL DBA Guy said...

SELECT str FROM tbl WHERE LEFT(str,2) REGEXP '[0-9]+';

Unknown said...

Thanks =)