Re: [sqlite] column alignment of views and tables;
oops you need to select the string length from the column width select substring('', 1, 16 - length(printf("%2.f", price))) || printf("%2.f", price) from prices Paul www.sandersonforensics.com skype: r3scue193 twitter: @sandersonforens Tel +44 (0)1326 572786 http://sandersonforensics.com/forum/content.php?195-SQLite-Forensic-Toolkit -Forensic Toolkit for SQLite email from a work address for a fully functional demo licence On 4 April 2017 at 15:07, Paul Sanderson wrote: > Oops would need to subtract the string length from the column width you > want - but hopefully you get the idea :) > > > Paul > www.sandersonforensics.com > skype: r3scue193 > twitter: @sandersonforens > Tel +44 (0)1326 572786 <+44%201326%20572786> > http://sandersonforensics.com/forum/content.php?195-SQLite- > Forensic-Toolkit -Forensic Toolkit for SQLite > email from a work address for a fully functional demo licence > > On 4 April 2017 at 15:05, Paul Sanderson > wrote: > >> Just shooting out so no time to test. But could you try something like >> >> select substring('', 1, length(printf("%2.f", price))) >> || printf("%2.f", price) from prices >> >> >> >> >> Paul >> www.sandersonforensics.com >> skype: r3scue193 >> twitter: @sandersonforens >> Tel +44 (0)1326 572786 <+44%201326%20572786> >> http://sandersonforensics.com/forum/content.php?195-SQLite-F >> orensic-Toolkit -Forensic Toolkit for SQLite >> email from a work address for a fully functional demo licence >> >> On 4 April 2017 at 14:47, Hans M. van der Meer >> wrote: >> >>> Simon, thanks. >>> Now at last, I know how to continue. >>> >>> 2017-04-04 15:03 GMT+02:00 Simon Slavin : >>> >>> > >>> > On 4 Apr 2017, at 11:25am, Hans M. van der Meer >>> > wrote: >>> > >>> > > I am building a simple bookkeeping. >>> > > With PHP and SQLite i now have tables and views with columns for >>> values >>> > and >>> > > prices: not nicely aligned because decimal values are aligned >>> different >>> > > from values that are interpreted as integers. >>> > >>> > Numeric values stored in a SQLite database should be stored as numbers. >>> > This allows you to do calculations on them. They should not have >>> alignment >>> > because they should not be text. >>> > >>> > > I like to create reports in which the column of prices and >>> > > *values are aligned to the right and all figures with two decimals >>> behind >>> > > the decimal point.* >>> > > I can not find a solution, so the question is how to achieve this >>> > >>> > SQLite is a database system. It is used for storing and retrieving >>> > information, not formatting it for people. When presenting your >>> numbers to >>> > people, do your formatting in PHP using sprintf() or vprintf(). >>> > >>> > http://php.net/manual/en/function.sprintf.php >>> > http://php.net/manual/en/function.vprintf.php >>> > >>> > See example #5 for sprintf() for "padding" which a word relating to >>> > alignment. >>> > >>> > Simon. >>> > ___ >>> > sqlite-users mailing list >>> > sqlite-users@mailinglists.sqlite.org >>> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users >>> > >>> ___ >>> sqlite-users mailing list >>> sqlite-users@mailinglists.sqlite.org >>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users >>> >> >> > ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] column alignment of views and tables;
Just shooting out so no time to test. But could you try something like select substring('', 1, length(printf("%2.f", price))) || printf("%2.f", price) from prices Paul www.sandersonforensics.com skype: r3scue193 twitter: @sandersonforens Tel +44 (0)1326 572786 http://sandersonforensics.com/forum/content.php?195-SQLite-Forensic-Toolkit -Forensic Toolkit for SQLite email from a work address for a fully functional demo licence On 4 April 2017 at 14:47, Hans M. van der Meer wrote: > Simon, thanks. > Now at last, I know how to continue. > > 2017-04-04 15:03 GMT+02:00 Simon Slavin : > > > > > On 4 Apr 2017, at 11:25am, Hans M. van der Meer > > wrote: > > > > > I am building a simple bookkeeping. > > > With PHP and SQLite i now have tables and views with columns for values > > and > > > prices: not nicely aligned because decimal values are aligned different > > > from values that are interpreted as integers. > > > > Numeric values stored in a SQLite database should be stored as numbers. > > This allows you to do calculations on them. They should not have > alignment > > because they should not be text. > > > > > I like to create reports in which the column of prices and > > > *values are aligned to the right and all figures with two decimals > behind > > > the decimal point.* > > > I can not find a solution, so the question is how to achieve this > > > > SQLite is a database system. It is used for storing and retrieving > > information, not formatting it for people. When presenting your numbers > to > > people, do your formatting in PHP using sprintf() or vprintf(). > > > > http://php.net/manual/en/function.sprintf.php > > http://php.net/manual/en/function.vprintf.php > > > > See example #5 for sprintf() for "padding" which a word relating to > > alignment. > > > > Simon. > > ___ > > sqlite-users mailing list > > sqlite-users@mailinglists.sqlite.org > > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > > > ___ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] column alignment of views and tables;
On Tue, Apr 4, 2017 at 3:47 PM, Hans M. van der Meer wrote: > Simon, thanks. > Now at last, I know how to continue. > But SQLite itself ships with a portable version of printf too. So you can convert your integer or real typed columns into text typed ones for display, directly in SQL, as an alternative to doing it in PHP. --DD https://sqlite.org/lang_corefunc.html#printf > 2017-04-04 15:03 GMT+02:00 Simon Slavin : > > SQLite is a database system. It is used for storing and retrieving > > information, not formatting it for people. When presenting your numbers > to > > people, do your formatting in PHP using sprintf() or vprintf(). > > > > http://php.net/manual/en/function.sprintf.php > > http://php.net/manual/en/function.vprintf.php > > > > See example #5 for sprintf() for "padding" which a word relating > to alignment. > ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] column alignment of views and tables;
Simon, thanks. Now at last, I know how to continue. 2017-04-04 15:03 GMT+02:00 Simon Slavin : > > On 4 Apr 2017, at 11:25am, Hans M. van der Meer > wrote: > > > I am building a simple bookkeeping. > > With PHP and SQLite i now have tables and views with columns for values > and > > prices: not nicely aligned because decimal values are aligned different > > from values that are interpreted as integers. > > Numeric values stored in a SQLite database should be stored as numbers. > This allows you to do calculations on them. They should not have alignment > because they should not be text. > > > I like to create reports in which the column of prices and > > *values are aligned to the right and all figures with two decimals behind > > the decimal point.* > > I can not find a solution, so the question is how to achieve this > > SQLite is a database system. It is used for storing and retrieving > information, not formatting it for people. When presenting your numbers to > people, do your formatting in PHP using sprintf() or vprintf(). > > http://php.net/manual/en/function.sprintf.php > http://php.net/manual/en/function.vprintf.php > > See example #5 for sprintf() for "padding" which a word relating to > alignment. > > Simon. > ___ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] column alignment of views and tables;
On 4 Apr 2017, at 11:25am, Hans M. van der Meer wrote: > I am building a simple bookkeeping. > With PHP and SQLite i now have tables and views with columns for values and > prices: not nicely aligned because decimal values are aligned different > from values that are interpreted as integers. Numeric values stored in a SQLite database should be stored as numbers. This allows you to do calculations on them. They should not have alignment because they should not be text. > I like to create reports in which the column of prices and > *values are aligned to the right and all figures with two decimals behind > the decimal point.* > I can not find a solution, so the question is how to achieve this SQLite is a database system. It is used for storing and retrieving information, not formatting it for people. When presenting your numbers to people, do your formatting in PHP using sprintf() or vprintf(). http://php.net/manual/en/function.sprintf.php http://php.net/manual/en/function.vprintf.php See example #5 for sprintf() for "padding" which a word relating to alignment. Simon. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] column alignment of views and tables;
Hi users, I am building a simple bookkeeping. With PHP and SQLite i now have tables and views with columns for values and prices: not nicely aligned because decimal values are aligned different from values that are interpreted as integers. I like to create reports in which the column of prices and *values are aligned to the right and all figures with two decimals behind the decimal point.* I can not find a solution, so the question is how to achieve this Hans ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users