Re: [sqlite] column alignment of views and tables;

[Paul Sanderson]

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;

[Paul Sanderson]

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;

[Dominique Devienne]

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;

[Hans M. van der Meer]

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;

[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