SQLite Forum

printf improvement

printf improvement

(1) By anonymous on 2020-12-15 15:12:38 [link] [source]


Would it be possible to extend the printf f function to allow for both 1000s commas and decimal places? For example, I have a table of file sizes, and I would want to group them by file extensions and sum the total amounts. My SQL code is:

SELECT '*.'||FileExtension AS "File Type",
   printf('%,d',COUNT(DISTINCT SHA256)) AS "Files (Unique)",
   printf('%,d',COUNT(FileExtension)) AS "Files (Total)", 
   printf('%,10.3f',((SUM(FileSize)*1.0)/1024)/1024) AS "MBytes (Total)"
FROM Project
AND FileExtension = 'zip'
GROUP BY FileExtension
ORDER BY "MBytes (Total)" DESC;

The output is *.zip 10 10 2123.111

I would like the output to be: *.zip 10 10 2,123.111

Thank you!

(2) By ddevienne on 2020-12-15 16:01:17 in reply to 1 [link] [source]

I doubt you'll get your wish. Getting the comma modifier for integers
was already a long and tumultuous thread, and Richard granted my wish
because it was easy to code, and hard to emulate in SQL.

Commas in floating-points can be obtained by truncating to an integer,
comma-format that one, than concatenate the fractional part, so it's
not all that hard to emulate in SQL.

Myself, I'd show the bytes count as integers with commas. That's the
whole point of those commas to make them easy to read, and at least
there's no ambiguity between Mega and Mebi as in your case.

(3) By anonymous on 2020-12-15 21:05:30 in reply to 1 [link] [source]

Given the way SQLite is designed, presentation (as opposed to data storage & retrieval) is best done in the host application written in the host programming language instead of SQL. In particular, your host language should have a printf function with locale support, which should able to format your numbers according to the wishes of the user.

(4) By ddevienne on 2020-12-16 09:24:36 in reply to 3 [source]

Obviously I don't completely subscribe to this view point. Otherwise I would
not have asked for comma-formatting for integers in the first place.

SQL views are a basic kind of reporting (and thus presentation) that happens
to be both built-in and simple, and just because you prefer to write complex
code to do presentation of your SQL data, shouldn't mean everyone else should
be denied to do it using SQL views and basic SQL formatting tools.

(5) By Larry Brasfield (LarryBrasfield) on 2020-12-16 11:05:22 in reply to 4 [link] [source]

Yes, a view, or a suitably ordered query, can serve some presentation purposes.

The problem with this line of reasoning [a] is that there is no end to the enhancements it can justify. The library is about keeping and retrieving data. It is arguable that it need not have a printf() function at all. I suspect that the reason it does is that the printf engine needed to be present anyway. (It is used for type conversion to string.)

[a. (Trying to avoid erecting a straw man here.) The reasoning seems to be that because the existing, non-extended SQLite library can produce sufficiently presentable output for some purposes, it should be considered a presentation tool and evolved for that purpose. ]

A more human reader oriented number formatting function can easily be created and added as an extension. Extension functions are readily added and can be statically linked with the SQLite library. Your number pretty-printer would be a nice contribution, for some users of the library.