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()
>
>
>
>
> ************