How to format a Float number in SQLite?

Question

In SQLite I need to format a number to display it with the thousand separator and decimal separator. Example: The number 123456789 should be displayed as 1,234,567.89

What I did partially works because it does not display the thousand separator as I expected:

select *, printf ("U$%.2f", CAST(unit_val AS FLOAT) / 100) AS u_val FROM items;

u_val shows: U$1234567.89 but I need U$1,234,567.89

Answer 1

The following is one way that you could accomplish the result:-

select *, printf ("U$%.2f", CAST(unit_val AS FLOAT) / 100) AS u_val FROM items;

Could become :-

SELECT 
*, 
CASE
    WHEN len < 9 THEN myfloat 
    WHEN len> 8  AND len < 12 THEN substr(myfloat,1,len - 6)||','||substr(myfloat,len - 5)
    WHEN len > 11 AND len < 15 THEN substr(myfloat,1,len -9)||','||substr(myfloat,len-8,3)||','||substr(myfloat,len-5)
    WHEN len > 14 AND len < 18 THEN substr(myfloat,1,len - 12)||','||substr(myfloat,len -11,3)||','||substr(myfloat,len-8,3)||','||substr(myfloat,len-5)
END AS u_val
FROM   
    (
        SELECT *, length(myfloat) AS len
        FROM
            (
                SELECT *,printf("U$%.2f",CAST(unit_val AS FLOAT) / 100)) AS myfloat
                FROM Items
             )
    )

The innermost SELECT extracts the original data plus a new column as per your orginal SELECT.

The intermediate SELECT adds another column for the length of the new column as derived from unit_val via the printf (this could have been done in the first/innermost SELECT, getting this value simplifies (in my opinion) the outermost SELECT, or you could use multiple length(myfloats) in the outermost SELECT).

RESULT - Example

The following is the result from a testing (source column is myfloat) :-

• The highlighted columns being the original columns.
• The circled data being the result.
• The other 2 columns are intermediate columns.

Edit

As you've clarified that the input is an integer, then :-

SELECT *,'U$'||printf('%,d',(unit_val/100))||'.'||CAST((unit_val % 100) AS INTEGER) AS u_val FROM Items

would work assuming that you are using at least version 3.18 of SQLite.

Correction

Using the SQL immediately above if the value of the last part (the cents) is less than 10 then the leading 0 is dropped. So the correct SQL is. Note for simplicity the CAST has also been dropped and rather than concatening the . it has been added to the printf formatter string so :-

SELECT 
    'U$' || 
    printf('%,d', (unit_val / 100)) ||
    printf('.%02d',unit_val % 100) 
    AS u_val
FROM Items

Or as a single line

SELECT 'U$' || printf('%,d', (unit_val / 100)) || printf('.%02d',unit_val % 100) AS u_val FROM Items

Answer 2

Here is a suggestion:

WITH cte AS (SELECT 123456789 AS unit_val)
SELECT printf('%,d.%02d', unit_val/100, unit_val%100) FROM cte;

The Common Table Expression is just there to supply a dummy value, in the absence of variables.

The %,d format adds thousands separators, but, as many have pointed out, only for integers. Because of that, you will need to use the unit_val twice, once for the integer part, and again to calculate the decimal part.

SQLite truncates integer division, so unit_val/100 gives you your dollar part. The % operator is a remainder operator (not strictly the same as “mod”), so unit_val%100 gives the cents part, as another integer. The %02d format ensures that this is always 2 digits, padding with zeroes if necessary.