Please be aware that the content in SAP SQL Anywhere Forum will be migrated to the SAP Community in June and this forum will be retired.

I have an issue only when the value is less than 1.

Table decimal column value = 0.5, Required output format = '0.5'

I am getting it as '.5' , i need it as '0.5'

select 0.5, string( 0.5 ), stringf( 0.5 ), cast( 0.5 as varchar(20) ), convert( varchar(20), 0.5 )

The "stringf" function used above is downloaded from http://www.universalthread.com

ASA Version 9.0.2.3702

asked 04 Feb '10, 11:03

Kumar's gravatar image

Kumar
262479
accept rate: 25%


Take a look at Breck's blog entry Readable, Correct, Concise

Regards Thomas

permanent link

answered 04 Feb '10, 11:31

Thomas%20Duemesnil's gravatar image

Thomas Dueme...
2.7k293965
accept rate: 17%

@TDuemesnil, thank you. First thing i did is the omission of the $ symbol from the code, I am in middle east :-). And both working perfectly for 2 decimals, but there are some glitches when i increase it to 3.

(04 Feb '10, 12:52) Kumar
1

Well, of course I'm going to up-vote both answers :)

(04 Feb '10, 13:23) Breck Carter

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 :-

sql result

permanent link

answered 04 Feb '10, 12:46

Kumar's gravatar image

Kumar
262479
accept rate: 25%

See the record 19 & 20, it is not prefixed with a 0 (zero). Increasing the decimals also have an effect on the thousand separator comma, on almost all the results.

(04 Feb '10, 13:31) Kumar
2

You should adopt the following line

IF amount BETWEEN -.999 AND -.001
OR amount BETWEEN  .001 AND  .999
(04 Feb '10, 19:17) Thomas Dueme...
select LTRIM(STR(-.99,30,3))

It will result in -0.990 but it will not have a thousand separator, but this might be unimportant?

permanent link

answered 05 Feb '10, 15:35

Martin's gravatar image

Martin
9.0k130169257
accept rate: 14%

STR() hs the side effect that is expects an approximate numeric value. As such with large numbers, rounding errors could result, cf. the question [http://sqlanywhere-forum.sap.com/questions/399/is-12345678901234566-00-the-correct-result-of-str-12345678901234567-0001-40-2]. This may or may not be important:)

(05 Feb '10, 16:31) Volker Barth

@Martin: Needless to say, I have just learned that from the other question:)

(05 Feb '10, 16:33) Volker Barth

@Volker: Thanks, I am aware that it's no perfect solution, but I had the feeling that maybe a suboptimal but simple solution might already help here. If Kumar really wants to work with the data, he will probably use a programmatic way to retrieve the data and then he has the power of the programming language to do any necessary formatting.

(06 Feb '10, 18:08) Martin
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×90
×22
×10

question asked: 04 Feb '10, 11:03

question was seen: 4,676 times

last updated: 05 Feb '10, 15:35