pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Type Conversion: int4 -> Money
7+ messages / 5 participants
[nested] [flat]

* Type Conversion: int4 -> Money
@ 1999-07-06 18:49 secret <secret@kearneydev.com>
  1999-07-07 11:57 ` Re: [SQL] Type Conversion: int4 -> Money Herouth Maoz <herouth@oumail.openu.ac.il>
  0 siblings, 1 reply; 7+ messages in thread

From: secret @ 1999-07-06 18:49 UTC (permalink / raw)
  To: pgsql-sql

    Is there a way to convert an int4 into a money? I have a few reports
that require format of int4 as a money(I didn't use money for money
because it's very difficult to convert money into other types it
seems)...

    The only way I can figure out how to do it is a kludge, such as:

CREATE TABLE z(a int4, b money);

Fill a with appropriate values, then set b as "1".  SELECT a*b as a FROM
z... And your done... But there should be some easier way to convert an
int4 into a money rather than mulitply it?

--David





^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* Re: [SQL] Type Conversion: int4 -> Money
  1999-07-06 18:49 Type Conversion: int4 -> Money secret <secret@kearneydev.com>
@ 1999-07-07 11:57 ` Herouth Maoz <herouth@oumail.openu.ac.il>
  1999-09-09 20:03   ` Re: [SQL] Type Conversion: int4 -> Money secret <secret@kearneydev.com>
  0 siblings, 1 reply; 7+ messages in thread

From: Herouth Maoz @ 1999-07-07 11:57 UTC (permalink / raw)
  To: secret <secret@kearneydev.com>; +Cc: pgsql-sql

At 21:49 +0300 on 06/07/1999, secret wrote:


>     Is there a way to convert an int4 into a money? I have a few reports
> that require format of int4 as a money(I didn't use money for money
> because it's very difficult to convert money into other types it
> seems)...

What language do you use for creating the report? Most of the time, you can
get the integer in the frontend (the report program), and format it using
some formatting routine.


>     The only way I can figure out how to do it is a kludge, such as:
>
> CREATE TABLE z(a int4, b money);
>
> Fill a with appropriate values, then set b as "1".  SELECT a*b as a FROM
> z... And your done... But there should be some easier way to convert an
> int4 into a money rather than mulitply it?

You can multiply it without creating a special table for it. For example,
here is a test table:

testing=> select num, num * '1'::money from test1;
   num|?column?
------+-----------
     2|$2.00
     4|$4.00
   -14|($14.00)
    38|$38.00
   199|$199.00
100399|$100,399.00
(6 rows)

Is that what you wanted?

Herouth

--
Herouth Maoz, Internet developer.
Open University of Israel - Telem project
http://telem.openu.ac.il/~herutma





^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* Re: [SQL] Type Conversion: int4 -> Money
  1999-07-06 18:49 Type Conversion: int4 -> Money secret <secret@kearneydev.com>
  1999-07-07 11:57 ` Re: [SQL] Type Conversion: int4 -> Money Herouth Maoz <herouth@oumail.openu.ac.il>
@ 1999-09-09 20:03   ` secret <secret@kearneydev.com>
  0 siblings, 0 replies; 7+ messages in thread

From: secret @ 1999-09-09 20:03 UTC (permalink / raw)
  To: Herouth Maoz <herouth@oumail.openu.ac.il>; +Cc: pgsql-sql

Herouth Maoz wrote:

> At 21:49 +0300 on 06/07/1999, secret wrote:
>
> >     Is there a way to convert an int4 into a money? I have a few reports
> > that require format of int4 as a money(I didn't use money for money
> > because it's very difficult to convert money into other types it
> > seems)...
>
> What language do you use for creating the report? Most of the time, you can
> get the integer in the frontend (the report program), and format it using
> some formatting routine.
>
> >     The only way I can figure out how to do it is a kludge, such as:
> >
> > CREATE TABLE z(a int4, b money);
> >
> > Fill a with appropriate values, then set b as "1".  SELECT a*b as a FROM
> > z... And your done... But there should be some easier way to convert an
> > int4 into a money rather than mulitply it?
>
> You can multiply it without creating a special table for it. For example,
> here is a test table:
>
> testing=> select num, num * '1'::money from test1;
>    num|?column?
> ------+-----------
>      2|$2.00
>      4|$4.00
>    -14|($14.00)
>     38|$38.00
>    199|$199.00
> 100399|$100,399.00
> (6 rows)
>
> Is that what you wanted?
>
> Herouth
>
> --
> Herouth Maoz, Internet developer.
> Open University of Israel - Telem project
> http://telem.openu.ac.il/~herutma

    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?





^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* Re: [SQL] Type Conversion: int4 -> Money
@ 1999-09-10 14:35 Mark Wright <mwright@pro-ns.net>
  0 siblings, 0 replies; 7+ messages in thread

From: Mark Wright @ 1999-09-10 14:35 UTC (permalink / raw)
  To: pgsql-sql


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






^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* RE: [SQL] Type Conversion: int4 -> Money
@ 1999-09-10 15:04 Mathew White <guru@fundgroup.com>
  0 siblings, 0 replies; 7+ messages in thread

From: Mathew White @ 1999-09-10 15:04 UTC (permalink / raw)
  To: 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()
>
>
>
>
> ************




^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* RE: [SQL] Type Conversion: int4 -> Money
@ 1999-09-10 15:11 Mathew White <guru@fundgroup.com>
  0 siblings, 0 replies; 7+ messages in thread

From: Mathew White @ 1999-09-10 15:11 UTC (permalink / raw)
  To: 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()
> > 
> > 
> > 
> > 
> > ************




^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* Re: [SQL] Type Conversion: int4 -> Money
@ 1999-09-10 16:01 Kyle Bateman <kyle@actarg.com>
  0 siblings, 0 replies; 7+ messages in thread

From: Kyle Bateman @ 1999-09-10 16:01 UTC (permalink / raw)
  To: pgsql-sql@hub.org; +Cc: secret@kearneydev.com

Herouth Maoz wrote:

> testing=> select num, num * '1'::money from test1;
>    num|?column?
> ------+-----------
>      2|$2.00
>      4|$4.00
>    -14|($14.00)
>     38|$38.00
>    199|$199.00
> 100399|$100,399.00
> (6 rows)
>
> Is that what you wanted?
>
> Herouth
>

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

I use this in tcl to format a dollar amount.  Mine runs in the frontend,
but it could easily be made into a pltcl function:

#format for dollars, including commas
#------------------------------------------
#val:   the value as input by the user
proc comma_dollar {val} {
    if {$val == {}} {set val 0.00}
    if {$val < 0} {set sign {-}; set val [expr -$val]} else {set sign
{}}
    set val [format {%#.2f} $val]
    lassign [split $val .] dol cents
    set cdol {}
    for {set len [string length $dol]} {$len > 0} {incr len -3} {
        if {$cdol == {}} {
            set cdol "[string range $dol [expr $len - 3] [expr $len -
1]]"
        } else {
            set cdol "[string range $dol [expr $len - 3] [expr $len -
1]] $cdol
        }
    }
    return "$sign[join $cdol ,].$cents"
}

Attachments:

kyle.vcf (text/x-vcard, 290B) download begin:vcard n:Bateman;Kyle tel;fax:801-377-8096 tel;work:801-377-8033x101 x-mozilla-html:FALSE url:www.actiontarget.com org:Action Target Inc adr:;;PO Box 636;Provo;UT;84603;US version:2.1 email;internet:kyle@actiontarget.com title:President x-mozilla-cpt:;-15520 fn:Kyle Bateman end:vcard
^ permalink raw reply [nested|flat] 7+ messages in thread


end of thread, other threads:[~1999-09-10 16:01 UTC | newest]

Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
1999-07-06 18:49 Type Conversion: int4 -> Money secret <secret@kearneydev.com>
1999-07-07 11:57 ` Herouth Maoz <herouth@oumail.openu.ac.il>
1999-09-09 20:03   ` secret <secret@kearneydev.com>
1999-09-10 14:35 Re: [SQL] Type Conversion: int4 -> Money Mark Wright <mwright@pro-ns.net>
1999-09-10 15:04 RE: [SQL] Type Conversion: int4 -> Money Mathew White <guru@fundgroup.com>
1999-09-10 15:11 RE: [SQL] Type Conversion: int4 -> Money Mathew White <guru@fundgroup.com>
1999-09-10 16:01 Re: [SQL] Type Conversion: int4 -> Money Kyle Bateman <kyle@actarg.com>

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