float/real轉換為字符誤區

<!-- [if gte mso 9]><xml> <w:WordDocument> <w:View>Normal</w:View> <w:Zoom>0</w:Zoom> <w:PunctuationKerning/> <w:DisplayHorizontalDrawingGridEvery>0</w:DisplayHorizontalDrawingGridEvery> <w:DisplayVerticalDrawingGridEvery>2</w:DisplayVerticalDrawingGridEvery> <w:ValidateAgainstSchemas/> <w:SaveIfXMLInvalid>false</w:SaveIfXMLInvalid> <w:IgnoreMixedContent>false</w:IgnoreMixedContent> <w:AlwaysShowPlaceholderText>false</w:AlwaysShowPlaceholderText> <w:Compatibility> <w:SpaceForUL/> <w:BalanceSingleByteDoubleByteWidth/> <w:DoNotLeaveBackslashAlone/> <w:ULTrailSpace/> <w:DoNotExpandShiftReturn/> <w:AdjustLineHeightInTable/> <w:BreakWrappedTables/> <w:SnapToGridInCell/> <w:WrapTextWithPunct/> <w:UseAsianBreakRules/> <w:DontGrowAutofit/> <w:UseFELayout/> </w:Compatibility> <w:BrowserLevel>MicrosoftInternetExplorer4</w:BrowserLevel> </w:WordDocument> </xml><![endif]--><!-- [if gte mso 9]><xml> <w:LatentStyles DefLockedState="false" LatentStyleCount="156"> </w:LatentStyles> </xml><![endif]-->

<!-- [if gte mso 10]> <mce:style><!-- /* Style Definitions */ table.MsoNormalTable {mso-style-name:表格內文; mso-tstyle-rowband-size:0; mso-tstyle-colband-size:0; mso-style-noshow:yes; mso-style-parent:""; mso-padding-alt:0cm 5.4pt 0cm 5.4pt; mso-para-margin:0cm; mso-para-margin-bottom:.0001pt; mso-pagination:widow-orphan; font-size:10.0pt; font-family:"Times New Roman"; mso-fareast-font-family:"Times New Roman"; mso-ansi-language:#0400; mso-fareast-language:#0400; mso-bidi-language:#0400;} -->

<!-- [endif]-->

--類型轉換

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

*/