float/real轉換為字符誤區

 

 

 

--類型轉換

 

declare @T table ( Num float ( 38)) --float/real 轉換為字符

insert @T select 1230

union all select 1234

union all select 123.45

union all select 123.456

union all select 123.4567

union all select 123.45678

 

select *, rtrim ( Num) as [ 直接轉換為字符 ] from @T

/*

Num                    直接轉換為字符

---------------------- -----------------------

1230                   1230

1234                   1234

123.45                 123.45

123.456                123.456

123.4567               123.457 -- 有誤

123.45678              123.457 -- 有誤

*/

 

 

select

    [ 轉換為字符 ]= case when len ( Num)= charindex ( '.' , Num) then stuff ( Num, len ( Num), 1, '' ) else Num end

from

(

    select

        Num=left( Num, Len ( Num)- patindex ( '%[^0]%' , reverse ( Num))+ 1)

    from (

        select rtrim ( cast ( Num as decimal ( 38, 10))) as Num from @T

        ) T

) T2

/*

轉換為字符

--------------------

1230

1234

123.45

123.456

123.4567

123.45678

 

*/