There's
http://sqlite.1065341.n5.nabble.com/printf-with-thousands-separator-td85022.html
And my feeble attempt below. But there's got to be a better way, no?
What would be the shortest and/or most efficient way to do this in SQL?
Couldn't SQLite's built-in printf gain a thousand-separator formatting
argument,
which doesn't need to be locale aware or could be even explicit after the
arg?
Thanks, --DD
PS: In this context, I don't want to use a host-program provided UDF.
This is data meant to be viewed with any SQLite client, so kind of "report".
C:\Users\ddevienne>sqlite3
SQLite version 3.10.2 2016-01-20 15:27:19
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite>
sqlite> with s(v) as (
...> select 23
...> union all
...> select 1097
...> union all
...> select 123456789
...> union all
...> select 4123456789
...> )
...> select v,
...> case
...> when v < 1000 then cast(v as text)
...> when v < 1000000 then printf("%d,%03d", v/1000, v%1000)
...> when v < 1000000000 then printf("%d,%03d,%03d", v/1000000,
v%1000000/1000, v%1000)
...> else printf("%d,%03d,%03d,%03d", v/1000000000,
v%1000000000/1000000, v%1000000/1000, v%1000)
...> end
...> from s
...> ;
23|23
1097|1,097
123456789|123,456,789
4123456789|4,123,456,789
sqlite>
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
