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