Shantanu's Blog

Selecting numbers
I have a column like this "my_col varchar(20) null".
The values in the column can be text or numbers. How can I select only
those rows where the value in this column is a valid number?
I need something like IS_DECIMAL(), but I can't find that function.
The following SQL fails to do the job

SELECT
MIXED_COLUMN,
MIXED_COLUMN + 0
FROM
TABLE
WHERE
MIXED_COLUMN =
MIXED_COLUMN + 0;

Is there a way I can run a query that selects from a column only numbers and not letters?

_____


You were on the right track. Casting a string to a number results in a 0 if MySQL can't make a proper conversion, which is slightly counter-intuitive. This might suit your needs:

mysql> select * from mixed_num;
+--------+
| my_col |
+--------+
| a |
| 0 |
| 1 |
| abc123 |
| 123abc |
| 1.2 |
| -1 |
+--------+
7 rows in set (0.12 sec)

1) Starting with a number:

mysql> select my_col, my_col + 0 from mixed_num where my_col = '0' OR my_col + 0 != 0;
+--------+------------+
| my_col | my_col + 0 |
+--------+------------+
| 0 | 0 |
| 1 | 1 |
| 123abc | 123 |
| 1.2 | 1.2 |
| -1 | -1 |
+--------+------------+
5 rows in set (0.00 sec)

Note that '123abc' is changed to '123', which may make a big difference to you.
A less magical way to get at these numbers is using REGEXP:

2) Numbers anywhere:

mysql> select my_col from mixed_num where my_col REGEXP '[[:digit:]]';
+--------+
| my_col |
+--------+
| 0 |
| 1 |
| abc123 |
| 123abc |
| 1.2 |
| -1 |
+--------+
6 rows in set (0.12 sec)

Note that rows having a digit in them are returned.


3) Unsigned decimals:

mysql> select my_col from mixed_num where my_col REGEXP '^[0-9.]+$';
+--------+
| my_col |
+--------+
| 0 |
| 1 |
| 1.2 |
+--------+
3 rows in set (0.00 sec)

4) Unsigned integers:

mysql> select my_col from mixed_num where my_col REGEXP '^[0-9]+$';
+--------+
| my_col |
+--------+
| 0 |
| 1 |
+--------+
2 rows in set (0.00 sec)

5) Signed integers:

mysql> select my_col from mixed_num where my_col REGEXP '^-?[0-9]+$';
+--------+
| my_col |
+--------+
| 0 |
| 1 |
| -1 |
+--------+
3 rows in set (2.78 sec)

...et cetera. REGEXP is explained here:

http://dev.mysql.com/doc/mysql/en/regexp.html