34,837
社区成员




declare @i numeric(12,4)
,@s varchar(10)
set @i=567.34
set @s=cast(@i as varchar)
select @s
--得到结果是:
/*
----------
567.3400
(所影响的行数为 1 行)
*/
--如何可以实现,得到下面结果,即删除后面没有用的零
/*
----------
567.34
(所影响的行数为 1 行)
*/
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))