Thanks to suggestions on this list I explored the sed post processing
avenue and found a solution. There are quite a few sed docs at
http://sed.sourceforge.net For my needs I adapted an example from
http://www-rohan.sdsu.edu/doc/sed.html (These docs are like ancient
scrolls.)
# add commas to interger strings, changing "1234567" to "1,234,567"
gsed ':a;s/\B[0-9]\{3\}\>/,&/;ta'
# add commas to numbers with decimal points and minus signs
gsed ':a;s/\(^\|[^0-9.]\)\([0-9]\+\)\([0-9]\{3\}\)/\1\2,\3/g;ta'
I adjusted the 2nd as follows to remove a space for each comma added which
preserves column layout:
sed ':a;s/\( \)\(^\|[^0-9.]\)\([0-9]\+\)\([0-9]\{3\}\)/\2\3,\4/g;ta'
It works like this:
sqlite> create table t(f1 real);
sqlite> insert into t values(100.123);
sqlite> insert into t values(12200.13);
$ sqlite3 -header -column test.db "select printf(\"%10.2f\",f1)from t;" |
sed ":a;s/\( \)\(^\|[^0-9.]\)\([0-9]\+\)\([0-9]\{3\}\)/\2\3,\4/g;ta";
printf("%10.2f",f1)
-------------------
100.12
12,200.13
This is sufficient for my needs since most of my output is numeric but I
did notice the following limitations with respect to dates and 'number'
strings:
1- The script looks for numbers [0-9] thus:
dates formatted like '2015-12-31' become '2,015-12-31'
number strings like '12345678' become '12,345,678'
2- Any numbers that begin in column 1 of each line fail
the match criteria (SQLite default is left justified).
3- Work arounds:
(1) Right justify via printf() where commas are wanted.
(2) Concatenate non matching characters where commas not wanted:
printf(\"%-12s\",\"* \" || Date) AS Date >> * 2015-12-31
