Re: anyone have an SQL FUNCTION like this they can share?

Michael Rosinger wrote:

Per a customer request, I need to be able to format a DEC()
number with commas (hundreds, thousands, millions, etc.) and a
decimal point with leading zeroes suppressed. I don't believe
there is a scalar function that can provide the commas. Before I
write the function, I was wondering if anyone had a similar
function they'd be willing to share. TIA

Of course that is a character result for a decimal numeric data; i.e. the result of the function is a character string, with input being decimal. If it has to be a function, then the best bet is probably creating an external function which applies numeric editing using the edit word or edit code feature, e.g. %editc of RPG, to do the conversion in one instruction. Typically however, such activity is left to the UI, not to the SQL, because the resultant data type as character string is no longer functionally numeric. That may be problematic if the UI enables saving the result set as it was obtained, yet the user expects to be able to manipulate the saved row data as numeric. It might be worthwhile clarifying what is the user interface being used, for which the task of formatting would need to be done by the SQL; the interface may already have a means to accomplish leaving the database data numeric while casting from numeric to character only in its presentation.

For example, the default behavior of the default report writer for STRSQL is to include the thousand separator, for which the opposite function, that is to remove the thousand separators, is discussed and a solution given as an external function in:
http://www.itjungle.com/fhg/fhg030106-story02.html

I thought I had written such a function as LANGUAGE SQL, and had posted that somewhere on the web, but I can not find it. I did find one searching "decimal to character" [not "numeric to character"] in a DB2 SQL Cookbook at either of:
http://www.scribd.com/doc/3148630/DB2-SQL-Cookbook
http://mysite.verizon.net/Graeme_Birchall/cookbook/HTM_SQL.html

Regards, Chuck