567.3400这个小数,转成字符串后,如何去除后面没有用的零呢?即转成"567.34"而不是"567.3400"

select dbo.clsZero('56.5600')



/*

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

56.56



（所影响的行数为 1 行）

*/

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

--功能：将一个字符型的数字末尾没有意义的零去除，如 "23.800" 转换成 "23.8"

--创建：2008-2-26

--更新：2007-2-26 11:26

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

ALTER     function clsZero(@strin varchar(50))

returns varchar(50)

begin

	declare @strout varchar(50)

	set @strout = CASE

            WHEN CHARINDEX('.', @strin) = 0

                THEN @strin

            WHEN RIGHT(@strin, PATINDEX('%[^0]%', REVERSE(@strin))) LIKE '.%'

                THEN LEFT(@strin, LEN(@strin) - PATINDEX('%[^0]%', REVERSE(@strin)))

            ELSE LEFT(@strin, LEN(@strin) - PATINDEX('%[^0]%', REVERSE(@strin)) + 1)

        END

	return @strout

end

如果是字符串中存储的数字数据需要去掉小数中的尾部0， 则参考：





SELECT

	col,

	col_convert = CASE

			WHEN CHARINDEX('.', col) = 0

				THEN col

			WHEN RIGHT(col, PATINDEX('%[^0]%', REVERSE(col))) LIKE '.%'

				THEN LEFT(col, LEN(col) - PATINDEX('%[^0]%', REVERSE(col)))

			ELSE LEFT(col, LEN(col) - PATINDEX('%[^0]%', REVERSE(col)) + 1)

		END

FROM(

	SELECT col = '100' UNION ALL

	SELECT col = NULL UNION ALL

	SELECT col = '.100' UNION ALL

	SELECT col = '.100100' UNION ALL

	SELECT col = '0.' UNION ALL

	SELECT col = '0' UNION ALL

	SELECT col = '100.1010' UNION ALL

	SELECT col = '100.0000' 

)A



-- 结果：

col      col_convert

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

100      100

NULL     NULL

.100     .1

.100100  .1001

0.       0

0        0

100.1010 100.101

100.0000 100



(8 行受影响)

declare @i numeric(12,4)

        ,@s varchar(10)



set @i=567.34

set @s=cast(cast(@i as numeric(12,2)) as varchar)



select @s



--得到结果是：

/*

---------- 

567.3400



（所影响的行数为 1 行）

*/



--如何可以实现，得到下面结果，即删除后面没有用的零

/*

---------- 

567.34



（所影响的行数为 1 行）

*/

declare @i numeric(12,4)

        ,@s varchar(10)



set @i=567.34

set @s=cast(@i as decimal(12,2))

select @s

declare @i numeric(38,24)

        ,@s varchar(100)



set @i=2000

select @s= case when charindex('.',reverse(@i))=patindex('%[^0]%',reverse(@i)) 

                then left(@i,charindex('.',@i)-1) 

                else left(@i,len(@i)+1-patindex('%[^0]%',reverse(@i))) end 

select @i = 1+@s

select @s,@i

declare @i numeric(38,37)

        ,@s varchar(100)



set @i=1.123456789012345678901234

select @s= case when charindex('.',@i)> 0 then left(@i,len(@i)+1-patindex('%[^0]%',reverse(@i))) else  rtrim(@i) end 

select @s

/*

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

1.123456789012345678901234

*/

declare @i numeric(38,37)

        ,@s varchar(10)



set @i=0.123456789012345678901234

set @s=cast(cast(@i as float)as varchar)



select @i, @s

/*

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

.1234567890123456789012340000000000000   0.123457

*/

declare @i numeric(12,4)

        ,@s varchar(10)



set @i=567.34

set @s=cast(cast(@i as float)as varchar)



select @s

declare @a table(a varchar(20))

insert into @a

select '2.0300'

union all

select '22.0500'

union all

select '-2.0500'

union all

select '2.000'

union all

select '-2.000'

 

select case when right(col,1)='0' then left(col,len(col)-2)

            else col

        end as col

from (

select (case when left(a,1)='-' then '-'+reverse(cast(reverse(substring(a,2,8000)) as float)) 

            else reverse(cast(reverse(a) as float)) 

end) as col

from @a ) t

declare @a varchar(20)

set @a='12.03400'

select reverse(cast(reverse(@a) as float))

select reverse(cast(reverse(cast(12.03400 as varchar)) as float))