How to convert more than seven digit to words using sql ?
by Satyendra Shukla
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
elsif tensandunits>=20 then word :=
CASE tensandunits DIV 10 OF
else /* decode the units*/
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.