pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Sferacarta Software <sferac@bo.nettuno.it>
To: Jerome Knobl <jknobl@mandanet.ch>
To: pgsql-sql@postgresql.org
Subject: Re[2]: [SQL] Howto format a float8 number?
Date: Mon, 14 Dec 1998 15:11:56 +0100
Message-ID: <8633.981214@bo.nettuno.it> (raw)
References: <l03110709b29193093d09@[147.233.159.109]>

HM> At 9:59 +0200 on 7/12/98, Jerome Knobl wrote:

>> I want to print a floating number like this :
>>     10.1 -> 10.10
>> or : 10000.1 -> 10'000.10
>> or : 0 -> 0.00
>> etc...

You can write your own function to format data.
I wrote the following function to format int4 using pl/pgSQL:

create function format(int4,text) returns text as
'declare
        fbak text;
        vbak int4;
        out text;
        sign boolean;
        num char(1);
        i int2;
        lf int2;
        bf int2;
        lv int2;
        sval text;
begin
        vbak := $1;
        fbak := $2;

        if lv > lf then
                raise exception ''the value % is greater than %'',$1,$2;
        end if;


        if vbak < 0 then
                sign := ''t'';
              vbak := -(vbak);
        else
                sign := ''f'';
        end if;

        lf := length(fbak);
        bf := lf;
        sval := text(vbak);
        lv := length(sval);

        while (lv>0 or lf>0) loop
                if lv > 0 then
                        if lf=0 then
                                raise exception ''the value % is greater than %'
                        end if;
                        if substr(fbak,lf,1)=''#'' then
                                num := substr(sval,lv,1);
                                out := substr(fbak,1,lf - 1) || num;
                                if bf > lf then
                                        fbak := out ||  substr(fbak,lf+1);
                                else
                                        fbak := out;
                                end if;
                                lf := lf - 1;
                                lv := lv - 1;
                        else
                                lf := lf - 1;
                        end if;
                else
                        if sign then
                                sign := ''false'';
                                fbak := substr(fbak,1,lf - 1) || (''-'' || subst
                        else
                                fbak := substr(fbak,1,lf - 1) || ('' '' || subst
                        end if;
                        lf := lf - 1;
                end if;
        end loop;

        return fbak;
end;
' language 'plpgsql';

EXAMPLES:

select format(13954323,'#(##)##-###');
format     
-----------
1(39)54-323
(1 row)

select format(31121998,'## ## ####');
    format
----------
31 12 1998
(1 row)

select format(311298,'##/##/19##');
    format
----------
31/12/1998
(1 row)

select format(1311000,'#,###,###');
format   
---------
1,311,000
(1 row)

-Jose'-





Message-ID: <8633.981214@bo.nettuno.it>
Permalink:  ../8633.981214@bo.nettuno.it/
Also on:    postgresql.org/message-id/8633.981214@bo.nettuno.it

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-sql@postgresql.org
  Cc: sferac@bo.nettuno.it, jknobl@mandanet.ch
  Subject: Re: [SQL] Howto format a float8 number?
  In-Reply-To: <8633.981214@bo.nettuno.it>
  References: <l03110709b29193093d09@[147.233.159.109]> <8633.981214@bo.nettuno.it>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox