How to convert more than seven digit to words using sql ?

by Satyendra Shukla
(kolkata (india))


I want to convert total salary received during the year into words. How do I do this using SQL?


You won't be able to do this in pure SQL. You would need to use Oracle PL/SQL or some other language as well. The reason for this is that you need to take different actions depending on the value of the number. For example if the 2nd least significant digit (the tens value) is between 2 and 9 then you need to get the units seperately. However if the tens value is 1 then you need to combine the tens and units into one word.

A very rough algorithm using the SQL functions MOD (to find the remainder) and DIV (to divide) would be:


  SELECT salary INTO sal FROM employees

  tensandunits := sal MOD 100000
  if tensandunits < 20 and tensandunits > 10 then
    word := CASE tensandunits OF
    11: 'eleven'
    12: 'twelve'
    13: 'thirteen'
    14: 'fourteen'
    15: 'fifteen'
    16: 'sixteen'
    17: 'seventeen'
    18: 'eighteen'
    19: 'nineteen'
    end case
  elsif tensandunits>=20 then word :=
  CASE tensandunits DIV 10 OF
    2: 'twenty'
    3: 'thirty'
    4: 'fourty'
    5: 'fifty'
    6: 'sixty'
    7: 'seventy'
    8: 'eighty'
    9: 'ninety'
    end case
  else /* decode the units*/
end if

Hundreds, hundred thousands and millions could be handled in the same way as the units (just append the word hundred/hundred thousand/million as appropriate).

Thousands and ten thousands would have to be handled the same way as tens and units.

A complete implementation in VB can be found at http://xl.barasch.com/cCo11432.htm or see this Python implementation

You might also want to review our Oracle SQL tutorials especially designed for beginners. Also see our Oracle training page for information about formal training courses both on-line and in person.

Comments for How to convert more than seven digit to words using sql ?

Average Rating starstarstarstarstar

Click here to add your own comments

Sep 20, 2010
Rating
starstarstarstarstar
up to 7 digits it can be converted by pure sql
by: SATYENDRA SHUKLA

Up to 7 digits can be converted by pure sql. Supose i want to convert salary in to words for all employees from employees table then the command will be

select salary, to_char(to_date(salary,'j'),'jsp') from employees;


Click here to add your own comments

Join in and write your own page! It's easy to do. How? Simply click here to return to Oracle Questions.