RE: [SQL] Type Conversion: int4 -> Money

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(at)pro-ns(dot)net] wrote:
>
> -----Original Message-----
> From: secret <secret(at)kearneydev(dot)com>
> To: Herouth Maoz <herouth(at)oumail(dot)openu(dot)ac(dot)il>
> Cc: pgsql-sql(at)postgreSQL(dot)org <pgsql-sql(at)postgreSQL(dot)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()
>
>
>
>
> ************