VenuGopal Papasani wrote:
Dear all,
I have a table with the following structure.
ield Type Collation Null Key Default
Extra Privileges Comment
---------------- ------------ --------------- ------ ------ -------
-------------- ------------------------------- -------
id int(11) (NULL) NO PRI (NULL)
auto_increment select,insert,update,references
indicatorName varchar(255) utf8_general_ci YES
(NULL) select,insert,update,references
periodName varchar(255) utf8_general_ci YES
(NULL) select,insert,update,references
source varchar(255) utf8_general_ci YES
(NULL) select,insert,update,references
level int(11) (NULL) YES
(NULL) select,insert,update,references
value varchar(255) utf8_general_ci YES
(NULL) select,insert,update,references
numeratorValue varchar(255) utf8_general_ci YES
(NULL) select,insert,update,references
denominatorValue varchar(255) utf8_general_ci YES
(NULL) select,insert,update,references
The values in value,NumeratorValue and DenominatorValue
Value NumeratorValue
denominatorValue
NaN Null
Null
infinity
null Null
2143.9888 NUll
NUll
0.0 0.0
0.0
Now i need a query which converts the varchar into some numeric
values.For ex for non numeric values like NAN,Infinity, Null get as zero
and
2143.98888 is converted into a numerical 2143.9888 and 0.0 is also
converted
to numeric.The resultset should in Numeric value all the above fields,
Can i
do it using a query.If so can any one give me the query
You could use case:
select case when value is null then 0 else cast(value as unsigned) end
AS new_value;
http://dev.mysql.com/doc/refman/5.1/en/case-statement.html
http://dev.mysql.com/doc/refman/5.1/en/cast-functions.html
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
