thousand separator for printing large numbers

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
34 messages Options
12
Reply | Threaded
Open this post in threaded view
|

thousand separator for printing large numbers

Dominique Devienne
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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: thousand separator for printing large numbers

Clemens Ladisch
Dominique Devienne wrote:
> But there's got to be a better way, no?

A database stores data.  Formatting the data for the user is not the
job of the database but of the actual application.

> Couldn't SQLite's built-in printf gain a thousand-separator formatting
> argument, which doesn't need to be locale aware

Thousand separators _are_ locale specific.


Regards,
Clemens
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: thousand separator for printing large numbers

Dominique Devienne
On Fri, Feb 10, 2017 at 11:26 AM, Clemens Ladisch <[hidden email]>
wrote:

> Dominique Devienne wrote:
> > But there's got to be a better way, no?
>
> A database stores data.  Formatting the data for the user is not the
> job of the database but of the actual application.


Honestly Clemens? There wouldn't be a built-in printf() and substr() etc...
if that was the case. At least you're consistent with your answer to the
post I was referring to. Hopefully someone less rigid in what a DB should
be will chime in too, with some actual SQL.


> > Couldn't SQLite's built-in printf gain a thousand-separator formatting
> > argument, which doesn't need to be locale aware
>
> Thousand separators _are_ locale specific.
>

They are if you want them to be. I can arbitrarily say I want a given one.
And so is the decimal point BTW, which SQLite happily hard codes to the C
locale.
e.g. The US 1,000.5 is 1.000,5 in FR, basically the "reverse".

So I'd be perfectly happy with a hard-coded thousand separator. Especially
since
the hard-coded separator can easily be replace()d by a different one to to
be locale OK via
some easy and short text substitution using out-of-the-box SQLite.

Dealing with locales would go against the grain of SQLite, I can accept
that, but the thousand separator
is just too common (be it , or . or _ or  space or else) to be summarily
dismissed like so IMHO. --DD
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: thousand separator for printing large numbers

Simon Slavin-3
In reply to this post by Clemens Ladisch

On 10 Feb 2017, at 10:26am, Clemens Ladisch <[hidden email]> wrote:

>> Couldn't SQLite's built-in printf gain a thousand-separator formatting
>> argument, which doesn't need to be locale aware
>
> Thousand separators _are_ locale specific.

Just to add to what Clemens wrote, you would at least need ways of doing comma-separation for thousands, dot-separation for thousands, and space-separator for thousands.  Between them, those cover the USA, Canada, and all countries in the EU.  If you post

2,257,322,372

to someone in, for example, Denmark, they’ll thing it’s four different numbers.

Simon.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: thousand separator for printing large numbers

Arjen Markus-3
> -----Original Message-----
> From: sqlite-users [mailto:[hidden email]] On Behalf
> Of Simon Slavin
>
> Just to add to what Clemens wrote, you would at least need ways of doing comma-
> separation for thousands, dot-separation for thousands, and space-separator for
> thousands.  Between them, those cover the USA, Canada, and all countries in the
> EU.

Not entirely, in German-spoken countries the apostrophe (') is often used as a thousands-separator.

Regards,

Arjen





DISCLAIMER: This message is intended exclusively for the addressee(s) and may contain confidential and privileged information. If you are not the intended recipient please notify the sender immediately and destroy this message. Unauthorized use, disclosure or copying of this message is strictly prohibited. The foundation 'Stichting Deltares', which has its seat at Delft, The Netherlands, Commercial Registration Number 41146461, is not liable in any way whatsoever for consequences and/or damages resulting from the improper, incomplete and untimely dispatch, receipt and/or content of this e-mail.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: thousand separator for printing large numbers

Simon Slavin-3

On 10 Feb 2017, at 1:25pm, Arjen Markus <[hidden email]> wrote:

> Not entirely, in German-spoken countries the apostrophe (') is often used as a thousands-separator.

That’s Switzerland, right ?  Not a member of the EU.  I didn’t know it covered other countries too.  Thanks.

To make it worse still, the ISO standard says we should all be using a non-breaking space, regardless of country.

Simon.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: thousand separator for printing large numbers

Arjen Markus-3
> -----Original Message-----
> From: sqlite-users [mailto:[hidden email]] On Behalf
> Of Simon Slavin
> Sent: Friday, February 10, 2017 2:36 PM
> To: SQLite mailing list
> Subject: Re: [sqlite] thousand separator for printing large numbers
>
>
> On 10 Feb 2017, at 1:25pm, Arjen Markus <[hidden email]> wrote:
>
> > Not entirely, in German-spoken countries the apostrophe (') is often used as a
> thousands-separator.
>
> That's Switzerland, right ?  Not a member of the EU.  I didn't know it covered other
> countries too.  Thanks.
>
> To make it worse still, the ISO standard says we should all be using a non-breaking
> space, regardless of country.
>
I have seen it in presentations by German speakers (the nationality, not the language), mostly as an unnoticed mistake or perhaps an automatic correction by "helpful" presentation software. (Another fun exercise: collect the various date formats in use in various countries)

Regards,

Arjen

DISCLAIMER: This message is intended exclusively for the addressee(s) and may contain confidential and privileged information. If you are not the intended recipient please notify the sender immediately and destroy this message. Unauthorized use, disclosure or copying of this message is strictly prohibited. The foundation 'Stichting Deltares', which has its seat at Delft, The Netherlands, Commercial Registration Number 41146461, is not liable in any way whatsoever for consequences and/or damages resulting from the improper, incomplete and untimely dispatch, receipt and/or content of this e-mail.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: thousand separator for printing large numbers

Dominique Devienne
On Fri, Feb 10, 2017 at 2:41 PM, Arjen Markus <[hidden email]>
wrote:

> > -----Original Message-----
> > From: sqlite-users [mailto:[hidden email]]
> On Behalf
> > Of Simon Slavin
> > Sent: Friday, February 10, 2017 2:36 PM
> > To: SQLite mailing list
> > Subject: Re: [sqlite] thousand separator for printing large numbers
> >
> >
> > On 10 Feb 2017, at 1:25pm, Arjen Markus <[hidden email]>
> wrote:
> >
> > > Not entirely, in German-spoken countries the apostrophe (') is often
> used as a
> > thousands-separator.
> >
> > That's Switzerland, right ?  Not a member of the EU.  I didn't know it
> covered other
> > countries too.  Thanks.
> >
> > To make it worse still, the ISO standard says we should all be using a
> non-breaking
> > space, regardless of country.
> >
> I have seen it in presentations by German speakers (the nationality, not
> the language), mostly as an unnoticed mistake or perhaps an automatic
> correction by "helpful" presentation software. (Another fun exercise:
> collect the various date formats in use in various countries)
>

You guys are all beside the point! ;)

Once you can do select printf("%,d", an_int) which gets you a "hard-coded"
1,234,567,
you can always replace(select printf("%,d", an_int), ',', '.') or whatever
separator you
want (char(160) for non-breakeable space Simon).

But right now, you cannot, and have to resort to ugly SQL, which is also
less efficient.

Thus this post to:
1) ask SQL experts on the best way to emulate thousand-sep in SQL
2) ask DRH to consider enhancing SQLite's built-in printf() to support it
out-of-the-box.

Thanks,  --DD
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: thousand separator for printing large numbers

Hick Gunter
The beauty of SQLite lies in its user extensability. If you want a presentation layer function in SQLite you can always write your own custom function, without forcing your specific needs on the community as a whole.

e.g. print_money(<amount> [, <format>])

with a check that <amount> is actually an integer (and not a float, because you should not be storing money in floats, see threads wrt rounding)
and format being a string <scale><decimal><thousands> and defaulting to "2.," (for values is in cents of major currency unit or whatever your application is using)

-----Ursprüngliche Nachricht-----
Von: sqlite-users [mailto:[hidden email]] Im Auftrag von Dominique Devienne
Gesendet: Freitag, 10. Februar 2017 15:00
An: SQLite mailing list <[hidden email]>
Betreff: Re: [sqlite] thousand separator for printing large numbers

On Fri, Feb 10, 2017 at 2:41 PM, Arjen Markus <[hidden email]>
wrote:

> > -----Original Message-----
> > From: sqlite-users
> > [mailto:[hidden email]]
> On Behalf
> > Of Simon Slavin
> > Sent: Friday, February 10, 2017 2:36 PM
> > To: SQLite mailing list
> > Subject: Re: [sqlite] thousand separator for printing large numbers
> >
> >
> > On 10 Feb 2017, at 1:25pm, Arjen Markus <[hidden email]>
> wrote:
> >
> > > Not entirely, in German-spoken countries the apostrophe (') is
> > > often
> used as a
> > thousands-separator.
> >
> > That's Switzerland, right ?  Not a member of the EU.  I didn't know
> > it
> covered other
> > countries too.  Thanks.
> >
> > To make it worse still, the ISO standard says we should all be using
> > a
> non-breaking
> > space, regardless of country.
> >
> I have seen it in presentations by German speakers (the nationality,
> not the language), mostly as an unnoticed mistake or perhaps an
> automatic correction by "helpful" presentation software. (Another fun exercise:
> collect the various date formats in use in various countries)
>

You guys are all beside the point! ;)

Once you can do select printf("%,d", an_int) which gets you a "hard-coded"
1,234,567,
you can always replace(select printf("%,d", an_int), ',', '.') or whatever separator you want (char(160) for non-breakeable space Simon).

But right now, you cannot, and have to resort to ugly SQL, which is also less efficient.

Thus this post to:
1) ask SQL experts on the best way to emulate thousand-sep in SQL
2) ask DRH to consider enhancing SQLite's built-in printf() to support it out-of-the-box.

Thanks,  --DD
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


___________________________________________
 Gunter Hick
Software Engineer
Scientific Games International GmbH
FN 157284 a, HG Wien
Klitschgasse 2-4, A-1130 Vienna, Austria
Tel: +43 1 80100 0
E-Mail: [hidden email]

This communication (including any attachments) is intended for the use of the intended recipient(s) only and may contain information that is confidential, privileged or legally protected. Any unauthorized use or dissemination of this communication is strictly prohibited. If you have received this communication in error, please immediately notify the sender by return e-mail message and delete all copies of the original communication. Thank you for your cooperation.


_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: thousand separator for printing large numbers

Dominique Devienne
On Fri, Feb 10, 2017 at 4:44 PM, Hick Gunter <[hidden email]> wrote:

> The beauty of SQLite lies in its user extensability. If you want a
> presentation layer function in SQLite you can always write your own custom
> function, without forcing your specific needs on the community as a whole.
>
> e.g. print_money(<amount> [, <format>])
>
> with a check that <amount> is actually an integer (and not a float,
> because you should not be storing money in floats, see threads wrt rounding)
> and format being a string <scale><decimal><thousands> and defaulting to
> "2.," (for values is in cents of major currency unit or whatever your
> application is using)
>

First, SQLite is useful outside of purely embedded cases.
When it's embedded, sure, write your own UDF, and be done with it.

But when you use SQLite to create databases to be viewed in any SQLite
client,
as is the case here, you're limited to what SQLite provides out-of-the-box.
The DB
in question are a mix of a few raw-data tables in human-unfriendly form,
and many
views which are human friendly and slice-and-dice the raw data for easy
analysis,
and such views use functions like datetime(), printf(), etc...

My integers have nothing to do with money, and the mere fact most modern
printf
or formatting libraries have thousand-separate clearly shows it's a general
purpose
and common enough need. It's no different from the currently formatting
capabilities
already provided by printf().

Sometime I wonder what's wrong with this community about the so called
"lightness"
of SQLite as justification to refuse all request for enhancements, however
small or
generally useful they are. Probably the same people who'd have cried fool
over
FKs, CTEs, JSON, row-values, table-valued functions / epo-vtable, etc...
when
SQLite was even "lighter" then, had these been proposed on list.

They all just appeared magically out of DRH's hat one day, mostly
unannounced
until the last minute when ready for the world. Substantial and complex
additions
that make SQLite even greater, albeit less light... (OMG!) and overall
we're all
grateful of course. Oh well, maybe this one little useful addition will
also come
eventually (or not, given this rant...). --DD
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: thousand separator for printing large numbers

Jens Alfke-2
In reply to this post by Dominique Devienne

> On Feb 10, 2017, at 5:59 AM, Dominique Devienne <[hidden email]> wrote:
>
> 2) ask DRH to consider enhancing SQLite's built-in printf() to support it
> out-of-the-box.

I disagree; this is feature bloat. SQLite is an embedded database, and the host app can do whatever it wants with the data, such as formatting it. There is no serious need to make SQLite into a Swiss army knife that can do all sorts of things that the host app could easily do itself.

> But when you use SQLite to create databases to be viewed in any SQLite
> client, as is the case here, you're limited to what SQLite provides out-of-the-box.


I don’t see that use case as being as important as the primary ones. I suppose you can look at SQLite as a sort of interchange format for database client apps, but putting SQLite itself in charge of user interface features like localized number formatting is wrong-headed.

I think you’re much better off going to the developers of the client apps that you use and asking them to add improved number formatting in their UI.

—Jens
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: thousand separator for printing large numbers

John McKown
On Fri, Feb 10, 2017 at 11:21 AM, Jens Alfke <[hidden email]> wrote:

>
> > On Feb 10, 2017, at 5:59 AM, Dominique Devienne <[hidden email]>
> wrote:
> >
> > 2) ask DRH to consider enhancing SQLite's built-in printf() to support it
> > out-of-the-box.
>
> I disagree; this is feature bloat. SQLite is an embedded database, and the
> host app can do whatever it wants with the data, such as formatting it.
> There is no serious need to make SQLite into a Swiss army knife that can do
> all sorts of things that the host app could easily do itself.
>
> > But when you use SQLite to create databases to be viewed in any SQLite
> > client, as is the case here, you're limited to what SQLite provides
> out-of-the-box.
>
>
> I don’t see that use case as being as important as the primary ones. I
> suppose you can look at SQLite as a sort of interchange format for database
> client apps, but putting SQLite itself in charge of user interface features
> like localized number formatting is wrong-headed.
>
> I think you’re much better off going to the developers of the client apps
> that you use and asking them to add improved number formatting in their UI.
>
> —Jens
>
>
​I agree with J​ens. SQLite is not PostgreSQL, or even MySQL. For my part,
if I want a fancy data base, I go with PostgreSQL. At least for me, SQLite
is a really nice relational data store with an SQL interface which I use
when, in the past, I would have used some other application specific data
store (e.g. Berkeley DB or GDB). What I like about it is that I can, if I'm
careful, "scale up" from SQLite to a more powerful RDMBS like PostgreSQL if
my application needs to go from being "one user at a time" to "multiple
concurrent users". Just my opinion, of course. Others may reasonably
disagree.


--
Our calculus classes are an integral part of your education.

Maranatha! <><
John McKown
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: thousand separator for printing large numbers

Stephen Chrzanowski
In reply to this post by Dominique Devienne
Bad design begins where you start throwing formatting functions in a SQL
call that could be used as a calculated value.  You request information for
text, numbers, or blobs of binary data.  Nothing more, nothing less,
because it is assumed that you're going to do something productive with the
number.

Bringing in "Other Database Engines do it!" discussion ultimately fails in
that they only store data in one of three formats as well, and everything
that comes to the UI is formatted for human consumption.  Otherwise, you'd
be given 1s and 0s for a 64-bit integer.  And for those CLIs that output as
numbers with commas and such?  Thats still CLI and UI gravy, and not DB
specific bloat.

Any element that is to be portrayed to the users screen should be handled
by whatever UI engine is displaying the information, not something that
handles only three types of data.  The UI needs to translate locality
information.  The date/time in your windows/linux/consoles/etc are
presented to you formatted.  The date and time are stored as a number, not
"Friday, February 10, 2017 12:43:33pm".

SQLite is lite.  It is designed to be run on machines that have KILOBYTES
of memory.  Todays phones and devices that are all the rage do have
MEGABYTES to GIGABYTES of memory and storage, sure, but there are devices
out there that have literally KILOBYTES of data to be worked with.  When
you start adding beautification methods to something that needs to only
provide three types of data, you're adding bloat, plain and simple.  DRH
isn't playing to the majority of machines, he's playing to machines that
NEED to operate on ultra-low requirements.  Rightly so.

If the application of your choice isn't displaying the numbers as you want,
take it up with the authors of the software that link into the engine.  If
its your program that is failing, change the UI elements to do the
formatting for you.  It'll be much easier to do math on a raw integer than
a number that is converted between whatever kinds of different locales
there are.

Now, if you'd like, you could possibly throw a suggestion for the SQLite3
Client, sure, maybe with a particular command line  option, or, even an
option set in the CLI itself to format numbers to your OS's locale.  But to
have the SQLite3 engine start fart'n (ah-ha.. get it.. bloated? ... Wifes
right, I'm so immature sometimes) out user specific formatting??  Yeah...
Shouldn't be part of it.

On Fri, Feb 10, 2017 at 11:13 AM, Dominique Devienne <[hidden email]>
wrote:

> On Fri, Feb 10, 2017 at 4:44 PM, Hick Gunter <[hidden email]> wrote:
>
> First, SQLite is useful outside of purely embedded cases.
> When it's embedded, sure, write your own UDF, and be done with it.
>
> But when you use SQLite to create databases to be viewed in any SQLite
> client,
> as is the case here, you're limited to what SQLite provides out-of-the-box.
> The DB
> in question are a mix of a few raw-data tables in human-unfriendly form,
> and many
> views which are human friendly and slice-and-dice the raw data for easy
> analysis,
> and such views use functions like datetime(), printf(), etc...
>
<cut>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: thousand separator for printing large numbers

Dominique Devienne
In reply to this post by Jens Alfke-2
On Fri, Feb 10, 2017 at 6:21 PM, Jens Alfke <[hidden email]> wrote:

> > On Feb 10, 2017, at 5:59 AM, Dominique Devienne <[hidden email]>
> wrote:
> >
> > 2) ask DRH to consider enhancing SQLite's built-in printf() to support
> it out-of-the-box.
>
> I disagree; this is feature bloat.
>

Wow. Feature bloat from adding *1 character* formatting with maybe 20 lines
of code behind it.

> select printf('%''d', 1000)
1,000

POSIX style, i.e. using single quote, thus needs to be doubled in SQL

or maybe comma as in Python, except here it's not "{:,}" but "%,d".

Oh well. I'm evidently in the minority here. --DD
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: thousand separator for printing large numbers

Dominique Devienne
In reply to this post by Stephen Chrzanowski
On Fri, Feb 10, 2017 at 6:53 PM, Stephen Chrzanowski <[hidden email]>
wrote:

> Bringing in "Other Database Engines do it!" discussion [...]
>

When did I do that?


> Any element that is to be portrayed to the users screen should be handled
> by whatever UI engine is displaying the information, not something that
> handles only three types of data.  The UI needs to translate locality
> information.  The date/time in your windows/linux/consoles/etc are
> presented to you formatted.  The date and time are stored as a number, not
> "Friday, February 10, 2017 12:43:33pm".
>

And that's exactly why SQLite has date and time functions.
Notably the one converting a number of seconds since the Epoch
into a human readable date time. Which I also use in my views.
That's no different.


> SQLite is lite.  It is designed to be run on machines that have KILOBYTES
> of memory.  Todays phones and devices that are all the rage do have
> MEGABYTES to GIGABYTES of memory and storage, sure, but there are devices
> out there that have literally KILOBYTES of data to be worked with.  When
> you start adding beautification methods [...]
>

Adding what? printf() is already here, and already has formatting options.

If the application of your choice isn't displaying the numbers as you want,
>

You're mistaken. I am explicitly generating a string as a thousand-separated
number using an SQL expression, not explicitly asking as app to display
numbers one way or another. And using printf('%,d', num) instead of a big
and ugly (and limited to billions) SQL expression is a good thing.


> Now, if you'd like, you could possibly throw a suggestion for the SQLite3
> Client, sure, maybe with a particular command line  option, or, even an
> option set in the CLI itself to format numbers to your OS's locale.
>

Again, I don't want or need implicit number formatting. I do it explicitly.
And again again, I don't need/want locale-aware formatting. --DD
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: thousand separator for printing large numbers

Dominique Devienne
In reply to this post by Dominique Devienne
On Fri, Feb 10, 2017 at 6:56 PM, Dominique Devienne <[hidden email]>
wrote:

> On Fri, Feb 10, 2017 at 6:21 PM, Jens Alfke <[hidden email]> wrote:
>
>> > On Feb 10, 2017, at 5:59 AM, Dominique Devienne <[hidden email]>
>> wrote:
>> >
>> > 2) ask DRH to consider enhancing SQLite's built-in printf() to support
>> it out-of-the-box.
>>
>> I disagree; this is feature bloat.
>>
>
> Wow. Feature bloat from adding *1 character* formatting with maybe 20
> lines of code behind it.
>

I'm sure DRH could probably add it in his sleep in [1] , around the switch
line 236 with a new flag,
with room to spare in et_info.flags to store it, and with the actual
formatting code in less than 20 lines,
in that 1099 line file. So +2% in that one file which is a tiny subset (<
1%) of 100K+ lines of the amalgamation. --DD

[1] http://www.sqlite.org/src/artifact?ln=on&name=ff10a9b9902cd2af
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: thousand separator for printing large numbers

James K. Lowden
In reply to this post by Dominique Devienne
On Fri, 10 Feb 2017 10:46:24 +0100
Dominique Devienne <[hidden email]> wrote:

> 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?
...
> This is data meant to be viewed with any SQLite client, so kind of
> "report".

What you're really talking about here isn't a printf function in the
SQLite C API, but the printing done by the sqlite3 shell.  

Glancing through that code, the shell retrieves all data  as strings.
Since SQLite doesn't maintain datatype information for columns, that's
not surprising.  

Any standard printf implementation these days supports locale-specific
thousands separators.  If SQLite uses sprintf(3) under the hood to
convert floating point values to strings in support of
sqlite3_value_text, it could have access to that support.  All it
would need to do is call setlocale(3) in shell.c.  

--jkl
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: thousand separator for printing large numbers

Dominique Devienne
On Fri, Feb 10, 2017 at 7:40 PM, James K. Lowden <[hidden email]>
wrote:

> On Fri, 10 Feb 2017 10:46:24 +0100
> Dominique Devienne <[hidden email]> wrote:
>
> > 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?
> ...
> > This is data meant to be viewed with any SQLite client, so kind of
> > "report".
>
> What you're really talking about here isn't a printf function in the
> SQLite C API, but the printing done by the sqlite3 shell.
>

I am. Because the view itself calls printf.
Same SQL case expression of my original post.


> Any standard printf implementation these days supports locale-specific
> thousands separators.  If SQLite uses sprintf(3) under the hood to
> convert floating point values to strings in support of
> sqlite3_value_text, it could have access to that support.  All it
> would need to do is call setlocale(3) in shell.c.
>

it doesn't. Follow the link from my previous post. --DD
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: thousand separator for printing large numbers

Clemens Ladisch
In reply to this post by Dominique Devienne
Dominique Devienne wrote:
> On Fri, Feb 10, 2017 at 6:53 PM, Stephen Chrzanowski <[hidden email]>
> wrote:
>> The date and time are stored as a number, not
>> "Friday, February 10, 2017 12:43:33pm".
>
> And that's exactly why SQLite has date and time functions.
> Notably the one converting a number of seconds since the Epoch
> into a human readable date time.

The date format shown above cannot be generated by SQLite.
ISO8601 strings are human readable, but meant to be parsed by
computers, and strftime() gives you only zero-padded fields for
the same reason.


Regards,
Clemens
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: thousand separator for printing large numbers

Jens Alfke-2
In reply to this post by Dominique Devienne

> On Feb 10, 2017, at 2:45 AM, Dominique Devienne <[hidden email]> wrote:
>
> Honestly Clemens? There wouldn't be a built-in printf() and substr() etc...
> if that was the case.

Not really. Those aren’t necessarily intended to format data for display, and I’ve never used them for that. For example I might use printf() to construct a primary key string out of several values from a query, or substr to compare one component of a string value against something.
I wouldn't put UI-level formatting in SQL queries, because it doesn’t belong there; it belongs at the view level of the application, not the model.

—Jens

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
12