Received: from fundgroup.com (guru.fundgroup.com [166.70.154.138]) by hub.org (8.9.3/8.9.3) with ESMTP id LAA10465 for ; Fri, 10 Sep 1999 11:04:16 -0400 (EDT) (envelope-from guru@fundgroup.com) Received: from mat.fundgroup.com (mat.fundgroup.com [192.168.1.15]) by fundgroup.com (8.9.3/8.9.3) with SMTP id JAA25759 for ; Fri, 10 Sep 1999 09:08:27 -0600 Received: by localhost with Microsoft MAPI; Fri, 10 Sep 1999 09:04:07 -0600 Message-ID: <01BEFB6B.70651DE0.guru@fundgroup.com> From: Mathew White To: "'pgsql-sql@postgreSQL.org'" Subject: RE: [SQL] Type Conversion: int4 -> Money Date: Fri, 10 Sep 1999 09:04:06 -0600 X-Mailer: Microsoft Internet E-mail/MAPI - 8.0.0.4211 MIME-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit Here is my solution (it will "Dollize" an infinitely large number). Okay, so it's not as elegant as Mark's. And it's klunky. But it works. This requires two subroutines: ### DOLLIZE: Turn any number into a dollar format string sub dollize { undef(@tmp); undef($tmp); $i = -1; @tmp = split/\./,(decimize($_[0])); if ($tmp[0] =~ /-/) { $tmp[0] =~ s/-//g; $neg = 1; } else { $neg = 0; } if (length($tmp[0]) > 3) { foreach (0..length($tmp[0])-1) { $tmp = $tmp . substr($tmp[0],length($tmp[0])-1-$_,1); $i++; if ($i == 2 && $_ ne (length($tmp[0])-1)) { $i = -1; $tmp = $tmp . ","; } } $tmp = reverse($tmp); } else { $tmp = $tmp[0]; } if ($neg == 1) { $tmp = "(\$" . $tmp . "." . $tmp[1] . ")"; } else { $tmp = "\$" . $tmp . "." . $tmp[1]; } return $tmp; } ### DECIMIZE: Make sure regular numbers have the proper number of decimals sub decimize { if ($_[0] =~ /\./) { @tmp=split/\./,$_[0]; if (length($tmp[1]) == 2) { return $_[0]; } elsif (length($tmp[1]) == 1) { return $_[0] . "0"; } elsif (length($tmp[1]) == 0) { return $_[0] . "00"; } elsif (length($tmp[1]) > 2) { return $tmp[0] . "." . substr($tmp[1],0,2); } else { # return int($_[0]*1000) / 1000; # For float rounding return $_[0]; } } elsif ($_[0] eq "") { return "0.00"; } else { return $_[0] . ".00"; } } For MONEY types, this might also be helpful: ### NUMIZE: Change Currency strings into numbers sub numize { $tmp = $_[0]; $tmp =~ s/[\$,]//g; if ($tmp =~ /\(/) { $tmp =~ s/[\(\)]//g; $tmp = "-" . $tmp; } # $tmp = int($tmp*1000) / 1000; # For float rounding return $tmp; } On Friday, September 10, 1999 8:36 AM, Mark Wright [SMTP:mwright@pro-ns.net] wrote: > > -----Original Message----- > From: secret > To: Herouth Maoz > Cc: pgsql-sql@postgreSQL.org > Date: Thursday, September 09, 1999 3:31 PM > Subject: Re: [SQL] Type Conversion: int4 -> Money > > > >Herouth Maoz wrote: > > > > ... > > > Perl... I ended up writing my own formatting function... It has > > one > that > >does ####.## however not one that'll do the nice 123,456.33 ... :) > > Do you > know > >if there are any public modules that do such things? > > > from the Perl Cookbook (a Perl-programmer must have!): > sub commify > { > my $text = reverse $_[0]; > $text =~ s/(\d\d\d)(?=\d)(?!\d*\.)/$1,/g; > return scalar reverse $text; > } > > If you want a locale-using solution, and can use C, there's code in > the > pgsql source that does this. Look at: > > /src/backend/utils/adt/cash.c cash_out() > > > > > ************