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

Hi,

I sometime used the following case-clause within an SQL-Statement to format
numeric fields up to 999,999.99. You may enhance it and put it into an UDF.

CASE WHEN MyFld < 1000,00
THEN VARCHAR(DEC(MyFld, 11, 2))
WHEN MyFld BETWEEN 1000,00 AND 999999,99
THEN VARCHAR(INT(MyFld/1000)) CONCAT ',' CONCAT
CASE WHEN MOD(MyFld, 1000) = 0 THEN '000'
ELSE VARCHAR(INT(MOD(MyFld, 1000)))
END
CONCAT RIGHT(VARCHAR(MyFld), 3)
END

Mit freundlichen Grüßen / Best regards

Birgitta Hauser

"Shoot for the moon, even if you miss, you'll land among the stars." (Les
Brown)
"If you think education is expensive, try ignorance." (Derek Bok)
"What is worse than training your staff and losing them? Not training them
and keeping them!"

-----Ursprüngliche Nachricht-----
Von: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] Im Auftrag von Michael Rosinger
Gesendet: Monday, 15. June 2009 19:02
An: midrange-l@xxxxxxxxxxxx
Betreff: anyone have an SQL FUNCTION like this they can share?

List,

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