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