Displaying Currency with Adaptive Server/SQL Anywhere

SQL Anywhere, also known as Adaptive Server Anywhere, does not provide support to display values as currency.

So, I had to write my own function. Since I’m copying my SQL from Oracle, I want to easily replace the TO_CHAR( amount, ‘$9,999.99′) expressions. So, this function is named TO_MONEY() and has the same signature as TO_CHAR()’s. I just replace CHAR with MONEY in SQL Anywhere.

TO_MONEY( IN ad_value double, IN as_format varchar(50) DEFAULT ‘$9,999.99′ )

ALTER FUNCTION "dba"."to_money"(
    IN ad_value double,
    IN as_format varchar(50) DEFAULT '$9,999.99' )
RETURNS varchar(50)
DETERMINISTIC
BEGIN
    DECLARE ls_value varchar(50);
    DECLARE ls_v varchar(50);
    DECLARE len integer;
    DECLARE ls_separator char(1);

    SET ls_separator = '.';

    SET ls_value = CAST( ROUND(ad_value*100,0) AS CHAR );
    SET len = LENGTH( ls_value );

    SET ls_v = RIGHT( ls_value, 2 );
    SET ls_value = SUBSTR( ls_value, 1, len - 2 );
    SET len = len - 2;

    WHILE len > 3 LOOP
        SET ls_v = RIGHT( ls_value, 3 ) + ls_separator + ls_v;
        SET ls_value = SUBSTR( ls_value, 1, len - 3 );
        SET len = len - 3;

        SET ls_separator = ',';
    END LOOP;

    IF len > 0 THEN
        SET ls_v = ls_value + ls_separator + ls_v;
    ELSEIF len = 0 THEN
        SET ls_v = '0' + ls_separator + ls_v;
    END IF;

	RETURN '$' + ls_v;
END