List:General Discussion« Previous MessageNext Message »
From:Eamon Daly Date:April 11 2005 3:02pm
Subject:Re: Find valid numeric values in a string field?
View as plain text  
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" <dmb@stripped>
To: <mysql@stripped>
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/mysql?unsub=1
> 

Thread
Find valid numeric values in a string field?Dan Bolser10 Apr
  • Re: Find valid numeric values in a string field?Eamon Daly11 Apr
    • Re: Find valid numeric values in a string field?Dan Bolser11 Apr