afh@ornl.gov (HUNTLEY JR A F) (06/28/91)
ATTN: Sybase 4.0.1 EBF (Early Bug Fix) users on Sun4 SUBJECT: Transact-SQL str() function bug The str(@float_value, @length, @decimal) Transact-SQL function returns the character representation of @float_value, in a @length long character string, with @decimal characters to the right of the decimal point. We have observed strange behavior in this function for certain values that meet at least the following conditions: 1) there is a non-zero value farther to the right of the decimal point than the number of decimal places that you request 2) some(?) zero's to the immediate left of the decimal point (I think the problem only occurs if the MOST significant digit is the only non-zero value to the left of the decimal point) See below: select str(1000.02,9,1) -------- !000.0 It doesn't always convert the first digit to '!' (something easy to spot). If the first digit is not a '1' (or even sometimes when it is) then the value of the MOST significant digit is downshifted by 1 (i.e., 5000.0 shows as 4000.0). select str(1000.001,6,1) ------ 000.0 select str(30000.00234,7,2) ------- 20000.0 select str(1.001,3,1) --- 0.0 This function worked in 4.0.1 Production release, but hasn't worked since at least EBF398, dated Oct 26 1990. I don't know the status of the 4.2 Sybase dataserver release, or ports for other hardware. We are on SunOS 4.1 with DBE (DataBase Excelerator) 1.0, though I don't believe these are factors. Thought some of you Wall Street firms might be interested in this one. Al Huntley (afh@ornl.gov) (615) 576-7170