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

Advertisements

The URI to TrackBack this entry is: https://dmcmeans.wordpress.com/2008/01/11/displaying-currency-with-adaptive-serversql-anywhere/trackback/

RSS feed for comments on this post.

4 CommentsLeave a comment

  1. There is in fact a Money field available as well as a numeric type to handle currency.

  2. Yes. Money is NUMERIC(19,4). But I don’t see any support for displaying the number as currency (e.g. $19,264.23). Did I miss it?

  3. […] #3 Re: problema con procedimiento almacenado Proba usando esta funcion "to_money" https://dmcmeans.wordpress.com/2008/0…rsql-anywhere/ […]

  4. Fix the errors…

    ALTER FUNCTION “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 );

    IF len = 1 THEN
    IF ls_value = ‘0’ THEN
    Set ls_v = ‘0’ + ls_separator + ’00’;
    ELSE
    Set ls_v = ‘0’ + ls_separator + ‘0’ + ls_value;
    End if;
    Else

    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;
    End if;

    RETURN ‘$’ + ls_v;
    END


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: