Sybase convert issue from float to varchar

Question

First, I need to mention that my current sybase db version is Adaptive Server Enterprise 12.5.4. I aim to convert float data type into varchar via sybase convert function in order to concat several of these kinds of variables, format and store in string type.

Unfortunately, it is not the case. Simply using convert(varchar(20), float_var) or cast() function cannot correctly return the precise value.

For example, ...

declare @float_var float
begin
    select @float_var =345.1237    --from table actually
    select convert(varchar(20),@float_var)     --return 345.1236999999
end

The incorrect string results returned occasionally have 99999 or 00001 suffix.

I tried many function including specify the precision, but there are still several cases not working on it. The sybase internal function does not exactly work on it.

I suppose this is a gerneral issue while using Sybase DB, however few answer found in serach. During my past experience, Sybase store procedure gammer always has sort of tolerance in runtime and internal fix when error encounter. This issue make me confused how Sybase works internally. Any advice would be appreciated, thanks in advance.

Solution 1

there are a couple of possible solutions for this.

firstly, let's try to convert the float to decimal first, then to varchar.

select cast(cast(@float_var as decimal(13,4)) as varchar)

alternatively, and this is where my ASE memory might fail me a little, would be to use the STR function like so:

Select ltrim(str(@float_var, 25, 5))

You have to TRIM the output as the STR function padding empty spaces on to the left of the result

Solution 2

this works for me:

declare @float_var float
begin
    select @float_var = 96.332 
    select cast(cast(@float_var as decimal(13,4)) as varchar)     -- Returns 96.3320
end

declare @float_var float
begin
    select @float_var = 345.1237
    select cast(cast(@float_var as decimal(13,4)) as varchar)     -- Returns 345.1237
end

Comments

[Bobby Tang]

Considering the precsiion of float data type, I tried to use case #1 cast(cast(@float_var as decimal(38,15)) as varchar), but still several number can not pass the test, like 96.332 returning 96.331999999999994

[SQLGuru]

what is your exact desired result??

[Bobby Tang]

Thanks for your prompt response. The main purpose for doing this is to convert float data type to string data type without losing precision, or in other words the value of this float type column should be entirely equivalent with its converted string while displaying in the select view. I need a generic function to cover this(from float to varchar) without losing any precision or scale. @SQLGuru

[Bobby Tang]

Yeah, reference to the function decimal(precision, scale), currently precision=13,scale=4, if the scale above 4, this is hard code value. Execuse me, for one more question, would I better to investigate the maximum of scale refering to the column type of my table, and hardcode for this scale of this decimal function in order to meet the demand of fitting the each exact value inside this column while converting?

[Bobby Tang]

Note that this real float value(define defalut float data type) came from the upstream system, it is quite difficult for me to evaluate the max scale or precision when accessing the data. So this is why I need a generic function to cover all the possible cases.

[SQLGuru]

yeah, I would use the hiest value for the scale... why not simply use the existing data type definition for that column. Then convert to varchar...