Postgres Pro
Downloads
Home   >   mailing lists

Thread: format integer

format integer

From
ml@ft-c.de
Date:
Hello, 

I need a integer format with different decimal places 
The integer should have 6 diggits. (or 7 or 8)

Example (6-diggits)
input       -> output
123456.789  -> 123456  
1234.56789  ->   1234.56
12.3456789  ->     12.3456

but when it is more then 10^6 then 
12345678.9  -> 12345678 
 
Is there a pg function for this task?

Franz

Re: format integer

From
"David G. Johnston"
Date:

On Sunday, January 24, 2021, <ml@ft-c.de> wrote:

I need a integer format with different decimal places
The integer should have 6 diggits. (or 7 or 8)
 

No, there isn’t a function to apply this convoluted formatting rule to non-integer numbers.  You will need to write your own.

David J.

Re: format integer

From
Steve Midgley
Date:


On Sun, Jan 24, 2021 at 8:06 AM David G. Johnston <david.g.johnston@gmail.com> wrote:

On Sunday, January 24, 2021, <ml@ft-c.de> wrote:

I need a integer format with different decimal places
The integer should have 6 diggits. (or 7 or 8)
 

No, there isn’t a function to apply this convoluted formatting rule to non-integer numbers.  You will need to write your own.

David J.

I've had to deal with something similar in the past, strangely. My solution was to convert the numeric formats to strings, and process there to get the right rule set regarding number of digits (and preserving all digits left of the period where required). Then convert back to your original numeric format. The one thing I'd mention as an edge case is that not all number systems use period as the significant digit delimiter, so be sure you guarantee the number formatting when you convert to string is what you expect when using this approach. I'd guess you could also solve this with logic and math (floor/ceiling/modulus, etc) but I found that using strings and regex was much easier for me.

Steve

Re: format integer

From
Torge Kummerow
Date:
Well strange requirement.

Guess you could do it with
case when x < 10 then ... else when x < 100 then ....

Don't think you'll find anything native.

Am 24. Januar 2021 15:57:02 MEZ schrieb ml@ft-c.de:
Hello, 

I need a integer format with different decimal places 
The integer should have 6 diggits. (or 7 or 8)

Example (6-diggits)
input       -> output
123456.789  -> 123456  
1234.56789  ->   1234.56
12.3456789  ->     12.3456

but when it is more then 10^6 then 
12345678.9  -> 12345678 
 
Is there a pg function for this task?

Franz






--
Diese Nachricht wurde von meinem Android-Gerät mit K-9 Mail gesendet.

Re: format integer

From
ml@ft-c.de
Date:
On Sun, 2021-01-24 at 09:06 -0700, David G. Johnston wrote:
> 
> On Sunday, January 24, 2021, <ml@ft-c.de> wrote:
> > 
> > I need a integer format with different decimal places 
> > The integer should have 6 diggits. (or 7 or 8)
> >  
> > 
> 
> No, there isn’t a function to apply this convoluted formatting rule
> to non-integer numbers.  You will need to write your own.
> 
> David J.
> 

My idea is
SELECT round(1.2345678912, 5 - least(log(1.2345678912)::int,5) );
SELECT round(12.345678912, 5 - least(log(12.345678912)::int,5) );
SELECT round(123.45678912, 5 - least(log(123.45678912)::int,5) );
SELECT round(1234.5678912, 5 - least(log(1234.5678912)::int,5) );
SELECT round(12345.678912, 5 - least(log(12345.678912)::int,5) );
SELECT round(123456.78912, 5 - least(log(123456.78912)::int,5) );
SELECT round(1234567.8912, 5 - least(log(1234567.8912)::int,5) );
SELECT round(12345678.912, 5 - least(log(12345678.912)::int,5) );
SELECT round(123456789.12, 5 - least(log(123456789.12)::int,5) );

as content for a function 
with arguments (value numeric, diggits int)

Have s.o. a better idea?

Franz