Re: Number Conversion Function - Mailing list pgsql-general

Tom Lane wrote:

  Tino Wildenhain <tino@living-examples.com> writes:


    I would not recommend to do this within the database. Thats typical
a job for your presentation layer.



... but having said that, I think the "money" datatype has a function
for this.  Whether that's of any use to you I dunno; money is pretty
restrictive about what it can handle.

            regards, tom lane



I disagree the database is the wrong place, there are cases it makes
sense.

I have looked for what Tom talks about for the money type i can't find
any thing??  so I wrote a function primary purpose is used with checks
but with a little modification will work for anyone one and has no
practical limitation .   It will work to Quintillion.

CREATE OR REPLACE FUNCTION spellNumericValue( pValue numeric)
  RETURNS text AS
$BODY$
DECLARE
  _dollar bigint = trunc(pValue)::text;
  _cents int = ((pValue - trunc(pValue))*100)::int;
  _spelledAmount text = '' ;
  _brokenOut int[] ;
  _pos integer = 0;
  _word text ;
BEGIN
   
    --lets breakout the number into hundreds into a array
    WHILE _dollar > 0 loop
        _brokenOut = array_append(_brokenOut, (_dollar%1000)::int);
        _dollar = trunc(_dollar/1000);
        _pos = _pos + 1;
    End Loop;

    --this works on numbers between 1 to 999 transforming into english
words. then goes to the
    --next set of numbers in the array working backwards as the array
was loaded backwards
    --Meaning the highest value is in the last element of the array
_brokenOut
    --This also assumes words thousands millions, billions... occurs
every 10^3 .
    while _pos > 0 loop
        if _brokenOut[_pos] >99 then
            SELECT CASE
                    WHEN _brokenOut[_pos]
>899 THEN 'Nine Hundred ' 
                    WHEN _brokenOut[_pos]
>799 THEN 'Eight Hundred ' 
                    WHEN _brokenOut[_pos]
>699 THEN 'Seven Hundred ' 
                    WHEN _brokenOut[_pos]
>599 THEN 'Six Hundred '  
                    WHEN _brokenOut[_pos]
>499 THEN 'Five Hundred ' 
                    WHEN _brokenOut[_pos]
>399 THEN 'Four Hundred ' 
                    WHEN _brokenOut[_pos]
>299 THEN 'Three Hundred ' 
                    WHEN _brokenOut[_pos]
>199 THEN 'Two Hundred ' 
                    WHEN _brokenOut[_pos]
>99 THEN 'One Hundred ' 
                    else ''
                end
            into _word;
            _spelledAmount = _spelledAmount || _word ;
        end if;

        Select Case
                WHEN _brokenOut[_pos]%100 = 10 THEN 'Ten '
                WHEN _brokenOut[_pos]%100 = 11 THEN 'Eleve
'
                WHEN _brokenOut[_pos]%100 = 12 THEN 'Twelve
'
                WHEN _brokenOut[_pos]%100 = 13 THEN
'Thirteen' 
                WHEN _brokenOut[_pos]%100 = 14 THEN
'Fourteen' 
                WHEN _brokenOut[_pos]%100 = 15 THEN
'Fifteen' 
                WHEN _brokenOut[_pos]%100 = 16 THEN
'Sixteen' 
                WHEN _brokenOut[_pos]%100 = 17 THEN
'Seventeen' 
                WHEN _brokenOut[_pos]%100 = 18 THEN
'Eighteen'
                WHEN _brokenOut[_pos]%100 = 19 THEN
'Nineteen' 
                WHEN _brokenOut[_pos]/10%10=2 THEN 'Twenty
' 
                WHEN _brokenOut[_pos]/10%10=3 THEN 'Thirty
' 
                WHEN _brokenOut[_pos]/10%10=4 THEN 'Fourty
' 
                WHEN _brokenOut[_pos]/10%10=5 THEN 'Fifty '

                WHEN _brokenOut[_pos]/10%10=6 THEN 'Sixty '

                WHEN _brokenOut[_pos]/10%10=7 THEN 'Seventy
' 
                WHEN _brokenOut[_pos]/10%10=8 THEN 'Eighty
' 
                WHEN _brokenOut[_pos]/10%10=9 THEN
'Ninety '  
                ELSE ''
            End
        into _word;
        _spelledAmount = _spelledAmount || _word;

        if _brokenOut[_pos]%100 < 10 or _brokenOut[_pos]%100 > 20
then
            SELECT CASE
                    WHEN _brokenOut[_pos]%10
=1 THEN 'One ' 
                    WHEN _brokenOut[_pos]%10
=2 THEN 'Two' 
                    WHEN _brokenOut[_pos]%10
=3 THEN 'Three ' 
                    WHEN _brokenOut[_pos]%10
=4 THEN 'Four ' 
                    WHEN _brokenOut[_pos]%10
=5 THEN 'Five ' 
                    WHEN _brokenOut[_pos]%10
=6 THEN 'Six ' 
                    WHEN _brokenOut[_pos]%10
=7 THEN 'Seven ' 
                    WHEN _brokenOut[_pos]%10
=8 THEN 'Eight ' 
                    WHEN _brokenOut[_pos]%10
=9 THEN 'Nine ' 
                    ELSE ''
                end
            into _word;
   
            _spelledAmount = _spelledAmount || _word;
        end if ;
       
        If _pos = 2 then
            _spelledAmount = _spelledAmount || 'Thousand ';
        elsif _pos = 3  then
            _spelledAmount = _spelledAmount || 'Million';
        elsif _pos = 4  then
            _spelledAmount = _spelledAmount || 'Billion ';
        elsif _pos = 5 then
            _spelledAmount = _spelledAmount || 'Trillion ';
        elsif _pos = 6 then
            _spelledAmount = _spelledAmount || 'Quadrillion ';
        elsif _pos = 7 then
            _spelledAmount = _spelledAmount || 'Quintillion ';
        else
            _spelledAmount = _spelledAmount || '';
        end if;

        _pos = _pos-1;
    end loop;

    if _cents = 0 then
        _spelledAmount = _spelledAmount || ' and Zero cents';
    else
        _spelledAmount = _spelledAmount || 'and ' || _cents::text ||
'/100 cents';
    end if ;
    return _SpelledAmount;
   
END;
$BODY$
  LANGUAGE 'plpgsql' VOLATILE