sqlite> create table x(x numeric);
sqlite> insert into x values ('0012');
sqlite> select typeof(x), x from x;
integer|12
sqlite> select printf('%04d', x) from x;
0012Presentation is a user/application problem. Not a database data problem. -- The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume. >-----Original Message----- >From: sqlite-users <[email protected]> On >Behalf Of Shawn Wagner >Sent: Sunday, 13 October, 2019 17:26 >To: SQLite mailing list <[email protected]> >Subject: Re: [sqlite] Possible bug in storing text values in numeric >columns > >I wouldn't call that conversion, or any other, lossless unless it can be >turned back into a string that's character for character identical with >the >one that was originally inserted. > >On Sun, Oct 13, 2019, 4:10 PM Igor Tandetnik <[email protected]> wrote: > >> On 10/13/2019 5:11 PM, Shawn Wagner wrote: >> > The documentation for a column with NUMERIC affinity says >> > >> >> When text data is inserted into a NUMERIC column, the storage class >of >> > the text is converted to INTEGER or REAL (in order of preference) if >such >> > conversion is lossless and reversible. >> >> "Lossless and reversible" here clearly means that the numerical value >is >> preserved, not that the exact text representation is. Thus, a couple >> paragraphs down in the same article, you'll find >> >> "A string might look like a floating-point literal with a decimal point >> and/or exponent notation but as long as the value can be expressed as >an >> integer, the NUMERIC affinity will convert it into an integer. Hence, >the >> string '3.0e+5' is stored in a column with NUMERIC affinity as the >integer >> 300000, not as the floating point value 300000.0." >> >> It is clear in this example that '3.0e+5' will in fact get coerced to a >> number, and that number will *not* in fact be rendered as '3.0e+5' when >> converted back to text. >> -- >> Igor Tandetnik >> >> _______________________________________________ >> sqlite-users mailing list >> [email protected] >> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users >> >_______________________________________________ >sqlite-users mailing list >[email protected] >http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users _______________________________________________ sqlite-users mailing list [email protected] http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
