Re: Find valid numeric values in a string field?

[Eamon Daly]

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)
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:
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)
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)
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
____________________________________________________________
Eamon Daly

----- Original Message ----- 
From: "Dan Bolser" <[EMAIL PROTECTED]>
To: <mysql@lists.mysql.com>
Sent: Sunday, April 10, 2005 10:10 AM
Subject: Find valid numeric values in a string field?


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 (probably because of
optimization)...
SELECT
 MIXED_COLUMN,
 MIXED_COLUMN + 0
FROM
 TABLE
WHERE
 MIXED_COLUMN =
 MIXED_COLUMN + 0
;
Hey, lets make an IS_DECIMAL UDF! ;)
----
p.s. why aren't the addresses of these mailing lists anywhere to be found
on the MySQL Lists pages?
Dan.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: 
http://lists.mysql.com/[EMAIL PROTECTED]


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/[EMAIL PROTECTED]