I copied code from Breck's blog, and did a small change to accept 3 decimals.
ALTER FUNCTION "DBA"."dollar_number_string" ( IN amount DECIMAL ( 30, 3 ) )
RETURNS VARCHAR ( 50 )
BEGIN
DECLARE string_amount VARCHAR ( 500 );
DECLARE insertion_point INTEGER;
DECLARE @sqlcode INTEGER;
DECLARE @sqlstate VARCHAR ( 5 );
DECLARE @errormsg VARCHAR ( 32767 );
IF amount IS NULL THEN
RETURN NULL
END IF;
SET string_amount = STRING (
IF amount BETWEEN -.99 AND -.01
OR amount BETWEEN .01 AND .99
THEN '0'
ELSE ''
ENDIF,
ABS ( amount ) );
SET insertion_point = LENGTH ( string_amount ) - 6;
WHILE insertion_point > 0 LOOP
SET string_amount = INSERTSTR ( insertion_point, string_amount, ',' );
SET insertion_point = insertion_point - 3;
END LOOP;
RETURN STRING (
IF amount < 0.00
THEN '-'
ELSE ''
ENDIF,
'',
string_amount );
EXCEPTION
WHEN OTHERS THEN
SELECT SQLCODE, SQLSTATE, ERRORMSG()
INTO @sqlcode, @sqlstate, @errormsg;
MESSAGE STRING (
'EXCEPTION in dollar_number_string at ',
CURRENT TIMESTAMP,
': SQLCODE = ', @sqlcode,
', SQLSTATE = ', @sqlstate,
', ERRORMSG() = ', @errormsg )
TO CONSOLE DEBUG ONLY;
RETURN '???';
END
ALTER FUNCTION "dba"."dollars"( in a decimal(30,3) )
RETURNS varchar(50)
BEGIN
declare s varchar(50);
declare i integer;
If a is null then
return null
end if;
set s=string( if a between -.99 and -.01 OR a between .01 and .00 then '0' else '' endif, abs(a));
set i=length(s) - 6;
while i > 0 loop
set s=insertstr(i,s,',');
set i= i - 3;
end loop;
return string( if a <0.00 then '-' else '' endif, '', s );
END
And here is the code to test both :-
SELECT *
FROM (
SELECT CAST ( NULL AS VARCHAR ( 100 ) ) AS "input", dollar_number_string ( NULL ) AS "output", dollars( NULL ) AS "output2"
UNION ALL
SELECT '-0', dollar_number_string ( -0 ), dollars ( -0 )
UNION ALL
SELECT '-.1', dollar_number_string ( -.1 ), dollars( -.1 )
UNION ALL
SELECT '-.99', dollar_number_string ( -.99 ), dollars( -.99 )
UNION ALL
SELECT '-.994', dollar_number_string ( -.994 ), dollars( -.994 )
UNION ALL
SELECT '-.995', dollar_number_string ( -.995 ), dollars( -.995 )
UNION ALL
SELECT '-1', dollar_number_string ( -1), dollars( -1)
UNION ALL
SELECT '-1.1', dollar_number_string ( -1.1 ), dollars( -1.1 )
UNION ALL
SELECT '-23.45', dollar_number_string ( -23.45 ), dollars( -23.45 )
UNION ALL
SELECT '-123.45', dollar_number_string ( -123.45 ), dollars( -123.45 )
UNION ALL
SELECT '-9123.45', dollar_number_string ( -9123.45 ), dollars( -9123.45 )
UNION ALL
SELECT '-99123.45', dollar_number_string ( -99123.45 ), dollars( -99123.45 )
UNION ALL
SELECT '-999123.45', dollar_number_string ( -999123.45 ), dollars( -999123.45 )
UNION ALL
SELECT '-9999123.45', dollar_number_string ( -9999123.45 ), dollars ( -9999123.45 )
UNION ALL
SELECT '-123456789012345678.90', dollar_number_string ( -123456789012345678.90 ), dollars( -123456789012345678.90 )
UNION ALL
SELECT '0', dollar_number_string ( 0 ), dollars( 0 )
UNION ALL
SELECT '.1', dollar_number_string ( .1 ), dollars( .1 )
UNION ALL
SELECT '.99', dollar_number_string ( .99 ), dollars( .99 )
UNION ALL
SELECT '.994', dollar_number_string ( .994 ), dollars( .994 )
UNION ALL
SELECT '.995', dollar_number_string ( .995 ), dollars( .995 )
UNION ALL
SELECT '1.00', dollar_number_string ( 1.00 ), dollars( 1.00 )
UNION ALL
SELECT '1.1', dollar_number_string ( 1.1 ), dollars( 1.1 )
UNION ALL
SELECT '23.45', dollar_number_string ( 23.45 ), dollars( 23.45 )
UNION ALL
SELECT '123.45', dollar_number_string ( 123.45 ), dollars( 123.45 )
UNION ALL
SELECT '9123.45', dollar_number_string ( 9123.45 ), dollars( 9123.45 )
UNION ALL
SELECT '99123.45', dollar_number_string ( 99123.45 ), dollars( 99123.45 )
UNION ALL
SELECT '999123.45', dollar_number_string ( 999123.45 ), dollars( 999123.45 )
UNION ALL
SELECT '9999123.45', dollar_number_string ( 9999123.45 ), dollars( 9999123.45 )
UNION ALL
SELECT '123456789012345678.90', dollar_number_string ( 123456789012345678.90 ), dollars( 123456789012345678.90 )
) AS testing
ORDER BY CAST ( "input" as DOUBLE );
Here is the output :-

answered
04 Feb '10, 12:46
Kumar
262●4●7●9
accept rate:
25%