On Sat, Nov 21, 2015 at 1:47 PM, Bruce Hohl <brucehohl at gmail.com> wrote:
> (1) SQLite will not return output with a thousands separator as follows:
>
> sqlite> select printf("%15.2f",123456789.12789);
> 123456789.13
>
> sqlite> select printf("%'15.2f",123456789.12789);
> <no output>
>
>
> --------------------------------------------------------------------------------
> (2) C language printf("%'15.2f",x) honors ' in printf() for thousands
> separator:
>
Some libraries support this as a non-standard extension, but it is not part
of standard C. Thus it will not work uniformly everywhere. And since SQLite
doesn't use printf directly, it won't work at all.
>
> vi comma1.c
> #include <stdio.h>
> #include <locale.h>
> int main(void)
> {
> printf("%'15.2f\n", 123456789.1234);
> setlocale(LC_ALL, "");
> printf("%'15.2f\n", 123456789.1234);
> return 0;
> }
>
> $ gcc comma1.c -o comma1
>
> $ ./comma1
> 123456789.12
> 123,456,789.12
>
>
> --------------------------------------------------------------------------------
> (3) So I thought maybe a C extension to SQLite might honor the thousands
> separator:
>
> Using the half.c extension example from
> https://www.sqlite.org/cvstrac/wiki?p=LoadableExtensions with
> printf("%'12.2f",x) added:
>
> #include <stdio.h>
> #include <locale.h>
> #include <sqlite3ext.h>
> SQLITE_EXTENSION_INIT1
>
> // The half() SQL function returns half of its input value.
> static void halfFunc(
> sqlite3_context *context,
> int argc,
> sqlite3_value **argv
> ){
> sqlite3_result_double(context,
> printf("%'12.2f",0.5*sqlite3_value_double(argv[0])));
> }
>
> // SQLite invokes this routine once when it loads the extension.
> int sqlite3_extension_init(
> sqlite3 *db,
> char **pzErrMsg,
> const sqlite3_api_routines *pApi
> ){
> SQLITE_EXTENSION_INIT2(pApi)
> sqlite3_create_function(db, "half", 1, SQLITE_ANY, 0, halfFunc, 0, 0);
> return 0;
> }
>
>
> $ gcc -shared -fPIC -I. -o half.so half.c
> $ sqlite3
> sqlite> select load_extension('./half.so');
>
> sqlite> select half(750000);
> 375000.0012.0
> sqlite> select half(7500000);
> 3750000.0012.0
> sqlite> select half(75000000);
> 37500000.0012.0
> sqlite> select half(750000000);
> 375000000.0012.0
> sqlite> select half(7500000000);
> 3750000000.0013.0
> sqlite> select half(75000000000);
> 37500000000.0014.0
>
> Result:
> (1) Output includes unwanted '12.0', '13.0', '14.0'.
> The 12, 13, 14 appear to be the number of characters printed.
> Not sure of the reason for the '.0'
>
> (2) No thousands separator as wanted by including ' in printf():
> printf("%'12.2f",0.5*sqlite3_value_double(argv[0]))
>
> I mostly use sqlite from the command line so it would really be nice to
> have a thousands separator for more readable output. Comments or
> suggestions please.
>
The problem with the extension is that it is using printf, which displays
the actual characters of the floating point value directly to stdout,
completely bypassing SQLite. Then the return value of printf (the number of
characters printed) is used as the value of sqlite3_result_double.
Obviously the ' extension isn't being honored here either, maybe because
the "C" locale is in use. Either way, it isn't doing what you expect.
One way to go would be to extend the format capabilities of the SQLite
version of the printf function (which has nothing in common with the
standard C library printf function) to honor a thousands separator. I don't
know if there would be any interest on the part of the SQLite team to
implement something like that.
--
Scott Robison
