Re: Spelled-out #'s in SQL*Plus

In article <mdicksonCtnsxn.IFA_at_netcom.com> mdickson_at_netcom.com (Mike Dickson) writes:
> The SQL statement that produces a spelled-out number for a check-writing
> program died during the v6-v7 migration. We're looking for a
> general-purpose routine that will take a number and return the
> spelled-out string, ie 1234 becomes One Thousand Two Hundred and
> Thirty-Four. Anybody out there done something like this?
> --
> Mike Dickson (Dir, Information Systems) | Voice (512) 459-1000
> Lutheran Social Services of the South | Fax (512) 452-6855
> Austin, Texas USA | Internet mdickson_at_netcom.com
> "Not my employer's opinions, unless they agree with it"

below is a shell/awk script I converted from a "c" function i wrote. I think it'd be pretty easy to take this logic and write a pl-sql function/procedure to do the same thing. If I get the time (doubtful), I'll post it. If anyone else takes the initiative - please post.

• dlrs_to_words ---- #!/bin/sh if echo $1 | grep -v '^[0-9]*\.[0-9]*$' > /dev/null then echo "argument must contain only numbers and a period" exit 1 fi echo "$1" | awk '{dlrs = int($0) cents = int(($0 - dlrs) * 100 + .5) nbrs[0] = "" nbrs[1] = "one" nbrs[2] = "two" nbrs[3] = "three" nbrs[4] = "four" nbrs[5] = "five" nbrs[6] = "six" nbrs[7] = "seven" nbrs[8] = "eight" nbrs[9] = "nine" nbrs[10] = "ten" nbrs[11] = "eleven" nbrs[12] = "twelve" nbrs[13] = "thirteen" nbrs[14] = "fourteen" nbrs[15] = "fiveteen" nbrs[16] = "sixteen" nbrs[17] = "seventeen" nbrs[18] = "eighteen" nbrs[19] = "nineteen" nbrs[20] = "twenty" nbrs[30] = "thirty" nbrs[40] = "fourty" nbrs[50] = "fifty" nbrs[60] = "sixty" nbrs[70] = "seventy" nbrs[80] = "eighty" nbrs[90] = "ninety" if (dlrs > 1000000) { print "Number greater than one million, too big" exit } if (dlrs == 0) nbrstr = "no" if (dlrs >= 100000) { nbrstr = nbrs[int(dlrs / 100000)] " hundred " dlrs = dlrs % 100000 thousflag++ # should thousand be put in nbrstr } if (dlrs >= 1000) { nbr = int(dlrs / 1000) if (nbr > 20) tens = int(nbr / 10) * 10 else tens = nbr if (ones = nbr - tens) dash = "-" # dash in twenty-one to ninety-nine nbrstr = nbrstr nbrs[tens] dash nbrs[ones] dlrs = dlrs % 1000 dash = "" thousflag++ } if (thousflag) nbrstr = nbrstr " thousand " if (dlrs >= 100) { nbrstr = nbrstr nbrs[int(dlrs / 100)] " hundred " dlrs = dlrs % 100 } if (dlrs > 20) tens = int(dlrs / 10) * 10 else tens = dlrs if (ones = dlrs - tens) dash = "-" nbrstr = nbrstr nbrs[tens] dash nbrs[ones] printf "%s %2d/00\n", nbrstr, cents }'
• end dlrs_to_words ------ -- David R. Thrash dthrash_at_sbctri.sbc.com Compuserve: 76217,1304

Thrash & Company

9102 Garland Road, Suite 216                   Voice:           214.327.1972
Dallas, Texas 75218                            Facsimile:       214.327.3510