* Karam Chand [...] > > > Is there any way wherein I can get data like > > > .000001, > > > .000002 etc.
* Roger Baklund > > Maybe the FORMAT() function will do what you want: * Karam Chand > It formats the output to ##,###,##.## . I dont want > the commas and also I dont know what maximum decimal > value is there in the table. It may be .001 or it > maybe .0000001 . I just want them in simple expanded > form. Try combining FORMAT() with TRIM(): USE test; CREATE TABLE floattest (f1 DOUBLE); INSERT INTO floattest VALUES (0.0001),(0.002),(0.00003); SELECT TRIM(BOTH '0' FROM FORMAT(f1,18)) f1 FROM floattest; +--------+ | f1 | +--------+ | .0001 | | .002 | | .00003 | +--------+ 3 rows in set (0.00 sec) <URL: http://www.mysql.com/doc/en/String_functions.html#IDX1211 > Note that DOUBLE (and FLOAT and REAL) are approximate numeric types, you need to decide on a precission when displaying the number. In the above example I used FORMAT(f1,18), which works fine on my computer, but it might not work on a different computer. If I increase the precission to 21, it breaks on my computer: SELECT TRIM(BOTH '0' FROM FORMAT(f1,21)) f1 FROM floattest; +------------------------+ | f1 | +------------------------+ | .0001 | | .002 | | .000030000000000000001 | +------------------------+ 3 rows in set (0.00 sec) <URL: http://www.mysql.com/doc/en/Problems_with_float.html > If you also need to remove commas, you can add the REPLACE() function to the party: SELECT REPLACE(TRIM(BOTH '0' FROM FORMAT(f1,18)),',','') f1 FROM floattest; <URL: http://www.mysql.com/doc/en/String_functions.html#IDX1214 > -- Roger -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
