Print Numbers in words

0 pts.
Tags:
Career Development
Certifications
CRM
Database
DataManagement
DB2
Desktops
Development
Functional
Lifecycle development
Management
Microsoft Windows
Oracle
OS
Performance/Load
Security
Servers
Software
Software Quality Assurance
Software testing tools
SQL
SQL Server
Tech support
Windows
By using a single SQL Query Is it possible to print numbers in words. Example : To print "5,550 Dollars" in words Expected Query result : "Five Thousand Five Hundred and Fifty Dollars Only" Is it Posssible by a single query
ASKED: December 18, 2005  10:11 PM
UPDATED: December 29, 2005  10:28 AM

Answer Wiki

Thanks. We'll let you know when a new response is added.

Although it’s entirely possible, build a function to do it. Here’s an example function written for DB2 in C:

Define TO_WORDS(DECIMAL(11,2)) RETURNS VARCHAR as an external function named to_words. Here’s the C definition:

void to_words(char *result, _Decimal(11,2) *arg, short *outNull, short *inNull) {
_Decimal(11,2) value = *arg; // Hold local copy

*result = 0; // Empty string so far
*outNull = *inNull; // Result is null iff input is
if (*inNull<0) // Null?
return; // Yes – Done
if (value<0) { // Negative?
strcpy(result, “Negative “); // Yes – Output first word
value = -value; // Take absolute value
}
if (!value) { // Special case of 0?
strcpy(result, “Zero”); // Yes – Output it
return;
}
if (value>1.99) { // Normal case
to_words_low(result, (int)value/1000000, “Million “);
to_words_low(result, ((int)value/1000)%1000, “Thousand”);
to_words_low(result, ((int)value%1000), “”);
strcat(result, “Dollars “);
}
else
if (value>=1 && value<2)
strcat(result, “One Dollar “);
if (value>1 && value%1)
strcat(result, “And “)
value = (value % 1) * 100;
if (!value)
return;
if (value>1)
to_words_low(result, value, “Cents”);
else
strcat(result”One Cent”);
}

void to_words_low(char *result, int value, char *ending) {
static char *set1[]={”One “,”Two “,”Three “,”Four “,’Five “,”Six “,”Seven “,”Eight “,”Nine “,
“Ten “,”Eleven “,”Twelve “,”Thirteen “,”Fourteen “,”Fifteen “,”Sixteen “,”Seventeen “,
“Eighteen “,”Nineteen “},
*set2[]=”Twenty “,”Thirty “,”Forty “,”Fifty “,”Sixty “,”Seventy “,”Eighty “,”Ninety “};

if (!value)
return;
if (value>99) {
to_words_low(result, value/100, “Hundred “);
value %= 100;
}
if (value>19) {
strcat(result, set2[value/10-2]);
value %= 10;
}
if (value)
strcat(result, set1[value-1]);
strcat(result, ending);
}

Note that (1) you can write this in any computer language you like; the algorithm will be the same.

(2) If switching from English to another spoken language, the algorithm will change slightly (as if you need others).

(3) You CAN do this all in straight SQL, but it will take about 3 times as long, and you’ll have to write it again every time you need it.


Sheldon Linker
Linker Systems, Inc.
www.linkersystems.com
sol@linker.com
800-315-1174
+1-949-552-1904

Discuss This Question: 7  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • Srinudev
    Check this... SQL>select to_char(to_date(112004,'J'), 'JSP') from dual; SQL>ONE HUNDRED TWELVE THOUSAND FOUR Regards Srinivas.V
    0 pointsBadges:
    report
  • Tjones
    Here is what Tom Kyte has to say on the subject: http://asktom.oracle.com/pls/ask/f?p=4950:8:11004214828373033368::NO::F4950_P8_DISPLAYID,F4950_P8_CRITERIA:1407603857650,
    180 pointsBadges:
    report
  • Jolora
    As others have said, create a function and call it in your SELECT statement. For example, if you create a function called TO_WORDS, you can use SELECT COL1, COL3, TO_WORD(CAL5), ... Here is a fast and comprehensive function for you: ---------------------------------------------------- declare t varchar2(1000 ); function to_words( i_this_number in number ) return varchar2 is /* Written by Joe Begenwald, 2005 anthony.j.begenwald@everestkc.net */ this_number varchar2(38) := to_char(i_this_number); this_number_len number := length( this_number ); working_len number := length( this_number ); working_string varchar2(3); pos number; output_string varchar2(1000); idx pls_integer := 1; /* the following list goes well past 38 digits. the extra names are there just because I know them! nothing past UNDECILLION will ever get used. */ type names_t is table of varchar2(100); names names_t := names_t( null ,'thousand' ,'million' ,'billion' ,'trillion' ,'quadrillion' ,'quintillion' ,'sextillion' ,'septillion' ,'octillion' ,'nonillion' ,'decillion' ,'undecillion' ,'duodecillion' ,'tredecillion' ,'quattuordecillion' ,'quindecillion' ,'sexdecillion' ,'septendecillion' ,'octodecillion' ,'novemdecillion' ,'vigintillion' ); begin while working_len > 0 loop if working_len > 2 then pos := working_len - 2; else pos := 1; end if; working_string := substr( this_number, pos, least(working_len,3) ); working_len := working_len - length(working_string); output_string := lower( to_char( trunc(sysdate) + to_number(working_string)/86400, 'SSSSSSP' ) ) || ' ' || names(idx) || ' ' || output_string; idx := idx + 1; end loop; return output_string; end to_words; begin dbms_output.put_line( '-' ); dbms_output.put_line( to_words( 1 ) ); dbms_output.put_line( '-' ); dbms_output.put_line( to_words( 12 ) ); dbms_output.put_line( '-' ); dbms_output.put_line( to_words( 123 ) ); dbms_output.put_line( '-' ); dbms_output.put_line( to_words( 1234 ) ); dbms_output.put_line( '-' ); dbms_output.put_line( to_words( 12345 ) ); dbms_output.put_line( '-' ); dbms_output.put_line( to_words( 123456 ) ); dbms_output.put_line( '-' ); dbms_output.put_line( to_words( 1234567 ) ); dbms_output.put_line( '-' ); dbms_output.put_line( to_words( 12345678 ) ); dbms_output.put_line( '-' ); dbms_output.put_line( to_words( 123456789 ) ); dbms_output.put_line( '-' ); dbms_output.put_line( to_words( 1234567890 ) ); dbms_output.put_line( '-' ); dbms_output.put_line( to_words( 12345678901 ) ); dbms_output.put_line( '-' ); dbms_output.put_line( to_words( 123456789012 ) ); dbms_output.put_line( '-' ); dbms_output.put_line( to_words( 1234567890123 ) ); dbms_output.put_line( '-' ); dbms_output.put_line( to_words( 12345678901234 ) ); dbms_output.put_line( '-' ); dbms_output.put_line( to_words( 123456789012345 ) ); dbms_output.put_line( '-' ); dbms_output.put_line( to_words( 1234567890123456 ) ); dbms_output.put_line( '-' ); dbms_output.put_line( to_words( 12345678901234567 ) ); dbms_output.put_line( '-' ); dbms_output.put_line( to_words( 123456789012345678 ) ); dbms_output.put_line( '-' ); dbms_output.put_line( to_words( 1234567890123456789 ) ); dbms_output.put_line( '-' ); dbms_output.put_line( to_words( 12345678901234567890 ) ); dbms_output.put_line( '-' ); dbms_output.put_line( to_words( 123456789012345678901 ) ); end; ----------------------------------------------------
    0 pointsBadges:
    report
  • Sanmails
    i am aware of this select to_char(to_date(123123,J),JSP) from dual, But this will not go beyond certain value. And i have already writtern a function to print numbers in words. I just want to know whether it is possible to do in in single query. Using decode function something like that. Anyway thanks for your ideas. Cheers!
    0 pointsBadges:
    report
  • DonKennedy
    Gee, I did this about 20 years ago in COBOL and it was not anywhere near that complex?
    0 pointsBadges:
    report
  • Nikunj
    Try this simple select... It will convert atleast between 1 and 5373484. select decode( sign( &&num ), -1, 'Negative ', 0, 'Zero', NULL ) || decode( sign( abs(&num) ), +1, to_char( to_date( abs(&num),'J'),'Jsp') ) || ' Dollars Only' from dual / Enjoy. Nikunj
    0 pointsBadges:
    report
  • Jolora
    One more time.... Here is a PL/SQL function that returns the integer passed to it as words. I'll leave it as an excersize for the stupid, uh, I mean student, to use it to handle decimals and dollors/cents.... function to_words( i_this_number in number ) return varchar2 is /* Written by Joe Begenwald, 2005 anthony.j.begenwald@everestkc.net */ this_number varchar2(38) := to_char(i_this_number); this_number_len number := length( this_number ); working_len number := length( this_number ); working_string varchar2(3); pos number; output_string varchar2(1000); idx pls_integer := 1; /* the following list goes well past 38 digits. the extra names are there just because I know them! nothing past UNDECILLION will ever get used. */ type names_t is table of varchar2(100); names names_t := names_t( null ,'thousand' ,'million' ,'billion' ,'trillion' ,'quadrillion' ,'quintillion' ,'sextillion' ,'septillion' ,'octillion' ,'nonillion' ,'decillion' ,'undecillion' ,'duodecillion' ,'tredecillion' ,'quattuordecillion' ,'quindecillion' ,'sexdecillion' ,'septendecillion' ,'octodecillion' ,'novemdecillion' ,'vigintillion' ); begin while working_len > 0 loop if working_len > 2 then pos := working_len - 2; else pos := 1; end if; working_string := substr( this_number, pos, least(working_len,3) ); working_len := working_len - length(working_string); output_string := lower( to_char( trunc(sysdate) + to_number(working_string)/86400, 'SSSSSSP' ) ) || ' ' || names(idx) || ' ' || output_string; idx := idx + 1; end loop; return output_string; end to_words;
    0 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following