views:

150

answers:

1

Is there a particularly easy way to convert a number like "21.08" into "Twenty One and 08/100" using MySQL?

I know in Oracle there was a trick you could use that involved working with Julian dates. It would get the job done in a line or so but it doesn't appear to work in MySQL (since it doesn't support Julian dates).

It's not a particularly hard problem in a "real" programming language but the thought of writing it out as a stored procedure or function is dreadful.

+1  A: 

Curious as to why you're doing this at the database layer instead of at the presentation layer...

If you really, really want to do this with MySQL, you could create two lookup tables called e.g. "ones" and "tens" that stored the English representation and then perform a query on each digit. Extract the digits by casting the number to a string and iterating backward from the decimal point, then performing a lookup in the appropriate table. Perhaps a third table could be used to supply strings like "Hundred", "Thousand", etc.

That's the most straightforward solution I can see, but it's going to be painful to write and probably quite brittle when it comes to internationalization. Also, it clutters the schema with lookup tables that don't have anything to do with your data.

Maybe writing a User-Defined Function (UDF) would be a better solution, though I imagine it will still be pretty time-consuming.

Philip Hanson
I was having the exact same thought as you. I recently inherited a mess of an application and that's just the way it was being done. I found a better solution, Crystal Reports has a ToWords() function which is where I needed it to appear anyway. You still gave an acceptable solution so +1 and accepted.
colithium