Re: Number Conversion Function

[justin <justin@xxxxxxxxxxxxxxx>]

Tom Lane wrote:

Tino Wildenhain <tino@xxxxxxxxxxxxxxxxxxx> 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