Thread

1. Howto format a float8 number?

   Jérome Knöbl <jknobl@mandanet.ch> — 1998-12-07T07:59:44Z

   Hello,
   
   I'm sorry, I look all the documentation, but I didn't find how to format
   a float8 number.
   
   I want to print a floating number like this :
       10.1 -> 10.10
   or : 10000.1 -> 10'000.10
   or : 0 -> 0.00
   etc...
   
   If you have some idea, write my!
   
   Thank a lot.
   
   JK
   
   
   
   
   

2. Re: [SQL] Howto format a float8 number?

   Herouth Maoz <herouth@oumail.openu.ac.il> — 1998-12-07T14:14:23Z

   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...
   
   To the best of my knowledge, you do that on the Front End, using the
   formatting facilities of whatever language you are using for your
   application.
   
   Herouth
   
   --
   Herouth Maoz, Internet developer.
   Open University of Israel - Telem project
   http://telem.openu.ac.il/~herutma
   
   
   
   

3. Re[2]: [SQL] Howto format a float8 number?

   jose' soares <sferac@bo.nettuno.it> — 1998-12-14T14:11:56Z

   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'-