Skip to content
Related Articles

Related Articles

Improve Article

Convert the given numbers into words in Pl/SQL

  • Last Updated : 12 Jul, 2018

Prerequisite PL/SQL introduction
In PL/SQL code groups of commands are arranged within a block. A block group related declarations or statements. In declare part, we declare variables and between begin and end part, we perform the operations.

Given a number and task is to convert each digit of the number into words.
Examples:

Input: 47856
Output: Four Seven Eight Five Six

Input: 123456
Output: one two three four five six

Approach is to select the corresponding word of a particular number using decode function.
Below is the required implementation:




DECLARE 
  
    -- declare variable are num, num_to_word, str, len, c 
    -- and in above declare variable num, len, c are integer datatype  
    -- and num_to_word and str are varchar datatype 
    num         INTEGER
    num_to_word VARCHAR2(100); 
    str         VARCHAR2(100); 
    len         INTEGER
    c           INTEGER
BEGIN 
    num := 123456; 
  
    len := Length(num); 
  
    dbms_output.Put_line('Entered Number is: ' 
                         ||num); 
  
    FOR i IN 1..len LOOP 
        c := Substr(num, i, 1); 
  
        SELECT Decode(c, 0, 'Zero '
                         1, 'One '
                         2, 'Two '
                         3, 'Three '
                         4, 'Four '
                         5, 'Five '
                         6, 'Six '
                         7, 'Seven '
                         8, 'Eight '
                         9, 'Nine '
        INTO   str 
        FROM   dual; 
  
        num_to_word := num_to_word 
                       ||str; 
    END LOOP; 
  
    dbms_output.Put_line('Number to words: ' 
                         ||num_to_word); 
END
  
-- Program End 

Output :

Entered Number is: 123456
Number to words: One Two Three Four Five Six 

Attention reader! Don’t stop learning now. Learn SQL for interviews using SQL Course  by GeeksforGeeks.




My Personal Notes arrow_drop_up
Recommended Articles
Page :