Hi Bruce,
I had a go at post-processing the sqlite3 shell's output to apply thousand
separators. I don't recommend looking too hard at the sed for the sake of
your sanity, but the gist is it repeatedly prepends a comma to trailing
groups of three digits, and then repeatedly removes commas which appear
after a decimal point[1].
[1] which now that I think of it will cause problems if you have lists of
floating point values separated by commas
The simplest way to use it is to pipe sqlite3's output into the sed
command. However this has a heavy impact on the prompt - we can do slightly
better with the more complicated invocation, assuming some flavour of
linux/bsd:
sqlite3 temp.sqlite 3> >(sed ': a;
s/\([0-9]\+\)\([0-9][0-9][0-9]\)\($\|[^0-9]\)/\1,\2\3/g; t a; : b;
s/\.\([0-9]\+\),\([0-9]\)/.\1\2/; t b')
SQLite version 3.8.1 2013-10-17 12:57:35
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> .output /dev/fd/3
This still interacts poorly with the prompt:
sqlite> select * from a;
sqlite> 1,029,587
1,287,750
Note the first row (1029587) appears after the prompt. Its more usable if
you get rid of the prompt entirely:
sqlite> .prompt ''
select * from a;
1,029,587
1,287,750
This still preserves the continuation prompt in the case that you
mistype/forget a semicolon:
select * from a
...> ;
1,029,587
1,287,750
The downside is you can't copy/paste results into a new sql query. Also
note this doesn't differentiate between numerical/text values, anything
that looks like a number will have separators inserted.
-sqweek
On 22 November 2015 at 23:34, Bruce Hohl <brucehohl at gmail.com> wrote:
> 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
> >
> _______________________________________________
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
