# How to output a decimal column as string ?

 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 262●4●7●9 accept rate: 25%

 Take a look at Breck's blog entry Readable, Correct, Concise Regards Thomas answered 04 Feb '10, 11:31 Thomas Dueme... 2.7k●29●39●65 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 :)
 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% 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 ``````
 ``````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? answered 05 Feb '10, 15:35 Martin 9.0k●130●166●257 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:) @Martin: Needless to say, I have just learned that from the other question:) @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
 toggle preview community wiki:

By Email:

Markdown Basics

• *italic* or _italic_
• **bold** or __bold__
• 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,484 times

last updated: 05 Feb '10, 15:35