Thanks for those comments, I now understand better what is being returned.
It seems an easy work around for shell output with thousands separator may
not be possible. Within sqlite3.c there are ~1000 lines of printf.c code
(added in 3.8.3) so it does seem to be a development matter. printf
support for a thousands separator would be nice ... is there a place for
feature requests?
On Sat, Nov 21, 2015 at 4:04 PM, Scott Robison <scott at casaderobison.com>
wrote:
> 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
> _______________________________________________
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
