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
30 pts.
Discuss This Question: 7 Replies