Sqlite Format Number with 2 Decimal Places Always
I Have Some Tables in a Sqlite Database That Contains Float Column Type, Now I Want to Retrieve the Value with a Query and Format the Float Field Always with 2...
I have some tables in a SQLite database that contains FLOAT column type, now i want to retrieve the value with a Query and format the float field always with 2 decimal places, so far i have written i query like this one :
SELECT ROUND(floatField,2) AS field FROM table
this return a resultset like the the following :
3.56
----
2.4
----
4.78
----
3
I want to format the result always with two decimal places, so :
3.56
----
2.40
----
4.78
----
3.00
Is it possible to format always with 2 decimal places directly from the SQL Query ? How can it be done in SQLite ?
Thanks.
4 Answers
You can use printf as:
SELECT printf("%.2f", floatField) AS field FROM table;
for example:
sqlite> SELECT printf("%.2f", floatField) AS field FROM mytable;
3.56
2.40
4.78
3.00
sqlite>
On older versions of SQLite that don't have PRINTF available you can use ROUND.
For example...
SELECT
ROUND(time_in_secs/1000.0/60.0, 2) || " mins" AS time,
ROUND(thing_count/100.0, 2) || " %" AS percent
FROM
blah
Due to the way sqlite stores numbers internally,
You probably want something like this
select case when substr(num,length(ROUND(floatField,2))-1,1) = "." then num || "0" else num end from table;
u can solve it like this:
SELECT round(-4.535,2);
-4.54
Sarah Jenkins is a veteran tech journalist with over 12 years of experience covering artificial intelligence, mobile innovations, and digital ethics. Her insights have appeared in leading technology publications worldwide.
