Question 167874 in PostgreSQL Question posted in
The official documentation can be found here.

How can I create PostgreSQL function for I: 123 as O: One Two Three as well as Un Deux Trois?

How would one write a function in PostgreSQL to obtain a number in words?

Input: 123
Output: Un Deux Trois
One Two Three

Input: 123.00
Output: Un Deux Trois . Zéro Zéro
One Two Three . Zero Zero

I tried to create a function for French words but this is not it.

    Create or replace   FUNCTION ZeroToNine (dd_amt int)   
    RETURNS VARCHAR(50) 
    AS $$
    DECLARE fr_word VARCHAR(50);
     BEGIN

     fr_word:='';

       if dd_amt = 0 then   fr_word:='Zéro';
 
     elsif dd_amt = 1 then  fr_word:='Un';

     elsif dd_amt = 2 then  fr_word:='Deux';

     elsif dd_amt = 3 then  fr_word:='Trois';

     elsif dd_amt = 4 then    fr_word:='Quatre';

     elsif dd_amt = 5 then    fr_word:='Cinq';

     elsif dd_amt = 6 then    fr_word:='Six';

     elsif dd_amt = 7 then    fr_word:='Sept';

     elsif dd_amt = 8 then    fr_word:='Huit';

     elsif dd_amt = 9 then    fr_word:='Neuf';
        end if;
        end if;
Tags:

2

Answers


  1. 0

    This function will do the trick, but it takes a decimal as input, like your second example. It’s using some array functions to handle the input and output:

    CREATE OR REPLACE FUNCTION ZeroToNine (dd_amt DECIMAL) 
RETURNS TEXT 
LANGUAGE plpgsql AS 
$$ 
DECLARE
    _content    TEXT[]  := string_to_array(CAST(dd_amt AS TEXT),NULL);
    _slice      TEXT;
    _output     TEXT[];
BEGIN
    FOREACH  _slice IN ARRAY _content
    LOOP
        _output := _output || 
            CASE _slice
                WHEN '0' THEN 'Zéro'
                WHEN '1' THEN 'Un'
                WHEN '2' THEN 'Deux'
                WHEN '3' THEN 'Trois'
                WHEN '4' THEN 'Quatre'
                WHEN '5' THEN 'Cinq'
                WHEN '6' THEN 'Six'
                WHEN '7' THEN 'Sept'
                WHEN '8' THEN 'Huit'
                WHEN '9' THEN 'Neuf'
                WHEN '.' THEN '.'
            END CASE;
    END LOOP;
    
    RETURN array_to_string(_output, ' ');                                   
END;                    
$$;

SELECT ZeroToNine (01203.01);
    Login or Signup to reply.
  2. 0

    Try the following:

    CREATE FUNCTION ZeroToNine(dd_amt numeric)
  RETURNS text AS
$$
  DECLARE res text;
BEGIN
  
SELECT string_agg(t.dtext, ' ' ORDER BY nums.ord)
FROM (
  SELECT * FROM (VALUES
     ('.', '.'), ('1', 'One'), ('2', 'Two'),
     ('3', 'Three'), ('4', 'Four'), ('5', 'Five'),
     ('6', 'Six'), ('7', 'Seven'), ('8', 'Eight'),
     ('9', 'Nine'),('0', 'Zero')) dnames(digit, dtext)
) t JOIN 
  UNNEST(regexp_split_to_array(dd_amt::text, '')) WITH ORDINALITY AS nums(digit, ord) 
ON t.digit = nums.digit
INTO res;
RETURN res;
  
END
$$
LANGUAGE plpgsql;

    See demo

    The regexp_split_to_array returns its result as an array of text.

    Here, the result of this function for (123.00) will be {1,2,3,.,0,0}.

    Then use the unnest function with ordinality on the resulting array from the regexp_split_to_array function. The use of ordinality is to get the correct order of input number digits for the next step. The output of this step will be:

    digit   ord
1       1
2       2
3       3
.       4
0       5
0       6

    Now, we join this result with the subquery t to get the corresponding digit text. To group digit texts in a single text we use the string_agg function ordered by the order we got from the ordinality. The final result of this will be:

    One Two Three . Zero Zero

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search