RE: [SQL] Type Conversion: int4 -> Money - Mailing list pgsql-sql

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 <secret@kearneydev.com>
> > To: Herouth Maoz <herouth@oumail.openu.ac.il>
> > Cc: pgsql-sql@postgreSQL.org <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()
> > 
> > 
> > 
> > 
> > ************