I couldn't find any system defined sproc or func in Sql Server to handle this request. So I set out to build a simple func to return USD in digits.
[This was one of my earliest Transact Sql sprocs that I wrote in Sql Server 5.0, I just converted the sproc logic to funcs in Sql 2000 here. There are CLR functions out there to do this job (for Sql 2005) however the Sql server instance was 2000, so I was not able to use Sql CLR function for this.]
Here are the main logic
1. Convert 0 to 9 to digits(1 to 'One', 2 to 'Two'...)
2. Convert 10 to 19 to digits (10 to 'Ten', 11 to 'Eleven'...)
3. Convert 10*2 to 10*9 to digits (20 to 'Twenty', 30 to 'Thirty'...)
4. Convert 100*x by appending step 1 result with 'Hundred' (100 to 'One'+' Hundreds'..)
5. Convert thousands, millions etc by appending appropriate labels. This is based on how the numbers are grouped to obtain thousands, millions etc in the requested currency
6. Take 2 decimal points and apply step 1 or 2 and append the result
7. Wrap all these funcs into an entry point function, loop through the numbers and append the results to a string.
You can easily alter the logic to add more currencies or port this to other RDBMS that supports funcs.
You can change the funcs with sprocs if your rdbms doesn't support funcs
That's all there to it!
download
-Paul
