On Mon, 11 Apr 2005, Eamon Daly wrote: >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:
I see. Cheers. Shame their is no 'IS_VALID_INT()' or 'IS_VALID_FLOAT()'. :) Dan. > >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]
