How to convert number into words postgresql

In this article I will show you how to convert number into words in postgresql. The question raises why and where we need this scenario. The answer is In some situation to show student CGPA in words in his/her degree report etc. To handle this scenario follow these steps.



1- Create a temporary table contains three columns

1. id which is auto generated
2. key character
3. value character

CREATE TABLE number_To_Words ( id SERIAL, key text, value text );

Now insert value in above created table.

Enjoy Free Tools: Free Online Text Tools

  

INSERT INTO number_To_Words (key,value) VALUES ('Zero', '0'), ('One', '1'),( 'Two', '2' ), ( 'Three', '3'), ( 'Four', '4' ), ( 'Five', '5' ), ( 'Six', '6' ), ( 'Seven', '7' ), ( 'Eight', '8'), ( 'Nine', '9'), ( 'Pt', '.');

The format of above created table looks like

2- Now create a function that convert number into words

1 CREATE OR REPLACE FUNCTION fnNumberToWords(n character varying) RETURNS character varying AS
2 $$
3 DECLARE
4   ch_array TEXT[];
5   result CHARACTER VARYING;
6   temp_result CHARACTER VARYING;
7 BEGIN
8  result := '';
9  select regexp_split_to_array(n,'') into ch_array;
10
11   FOR ch IN array_lower(ch_array, 1)..array_upper(ch_array, 1) LOOP
12      SELECT key FROM number_To_Words where value=ch_array[ch] :: CHARACTER VARYING  into temp_result;
13      result:= result || ' ' || temp_result;
14   END LOOP;
15 
16  RETURN result;
17 END;
18 $$ LANGUAGE PLPGSQL;

Code Description:

• In line no 1 we create a function named "fnNumberToWords" and pass a parameter n to that number, later we call that function to get words from given number.
• In line no 3 to 6 we declare three variables, ch_array, result and temp_result.
• In line no 9 we get cgpa in this format ([3,.,2,8,1])
• In line no 11 to 14 we declare a for loop and for each iteration concatenate cgpa in words.  

Now select and execute above function to create function. After executing above code call that function to get your desired result.

SELECT fnNumberToWords('3.281');