On Fri, Feb 10, 2017 at 10:46 AM, Dominique Devienne <ddevie...@gmail.com> wrote:
> What would be the [...] most efficient way to do this (NDR: thousand > separator) in SQL? > Here's a little demo that evaluates the 3 approaches proposed in this thread. Formatting 10M numbers takes ~2s using DRH's built-in, ~10s using Edzard Pasma's string-based solution (thank you BTW!), ~5s using my numeric-based solution. Regarding ease-of-use, no context, the order is then DRH, Edzard, and myself dead-last. DRH wins on all counts! ;) --DD C:\Users\ddevienne>sqlite3.18.0rc SQLite version 3.18.0 2017-03-06 20:44:13 Enter ".help" for usage hints. Connected to a transient in-memory database. Use ".open FILENAME" to reopen on a persistent database. sqlite> .timer on sqlite> select 'baseline'; sqlite> with num(n) as (select 1 union all select n+1 from num where n < 1e7) ...> select count(*) from num; 10000000 Run Time: real 3.986 user 3.978026 sys 0.000000 sqlite> with num(n) as (select 1 union all select n+1 from num where n < 1e7) ...> select sum(length( ...> case ...> when n < 1000 then cast(n as text) ...> when n < 1000000 then printf("%d,%03d", n/1000, n%1000) ...> when n < 1000000000 then printf("%d,%03d,%03d", n/1000000, n%1000000/1000, n%1000) ...> else printf("%d,%03d,%03d,%03d", n/1000000000, n%1000000000/1000000, n%1000000/1000, n%1000) ...> end ...> )) from num; 87887899 Run Time: real 8.765 user 8.767256 sys 0.000000 sqlite> with num(n) as (select 1 union all select n+1 from num where n < 1e7) ...> select sum(length( ...> ltrim(substr(n,-9,3)||','||substr(n,-6,3)||','||substr(n,-3,3),'0,') ...> )) from num; 87887899 Run Time: real 14.631 user 14.632894 sys 0.000000 sqlite> with num(n) as (select 1 union all select n+1 from num where n < 1e7) ...> select sum(length( ...> printf('%,d', n) ...> )) from num; 87887899 Run Time: real 6.258 user 6.255640 sys 0.000000 _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users