DECLARE @D DECIMAL(10,5) SET @D=12.01000SELECT CONVERT(VARCHAR,CAST(@D AS FLOAT)) /* 12.01 */
declare @dec decimal(8,5) set @dec= 12.01000 select str(@dec,5,2)
SELECT value = LEFT(value, 1 + LEN(value) - PATINDEX('%[^0]%', REVERSE(value))) FROM( SELECT value = CONVERT(varchar(20), value) FROM( -- 要转换的数据 SELECT value = 12.01000 )A )AA
declare @i decimal(15,5) set @i=12.01 select cast(cast(@i as float)as varchar(5)) /*----- 12.01*/
create function fn_dec2varchar(@dec decimal(18,6)) returns varchar(20) as begin return(cast(cast(@dec as decimal(18,2)) as varchar(20))) end --调用 select dbo.fn_dec2varchar(12.01000)
用转换为 float 的方法可能得不到正确结果, 因为 float 是用来表示非精确数据的, 转换过程可能会导致数据与原始的不一样 参考下面的测试DECLARE @D DECIMAL(10,7) SET @D=12.0100101SELECT @D, CONVERT(VARCHAR,CAST(@D AS FLOAT)) /* -- 丢失数据了 ------------ ------------------------------ 12.0100101 12.01 */SELECT value = LEFT(value, 1 + LEN(value) - PATINDEX('%[^0]%', REVERSE(value))) FROM( SELECT value = CONVERT(varchar(20), value) FROM( -- 要转换的数据 SELECT value = @D )A )AA /* value -------------------- 12.0100101 */
if exists(select Name from sysobjects where name='fnTrimLZero' and type='FN') Drop Function fnTrimLZero go create Function fnTrimLZero(@Num numeric(18,5)) returns varchar(23) as begin declare @Str varchar(23) set @Str=reverse(convert(varchar(23),@Num)) --将传入的参数反转以后,如果为整数返回小数点前面的部分 if convert(float,substring(@Str,1,charindex('.',@Str)-1))=convert(float,0) begin set @Str=substring(convert(varchar(23),@Num),1,charindex('.',convert(varchar(23),@Num))-1) return @Str end --用循环把第一个是0的字符删除 while left(@Str,1)='0' begin set @Str=stuff(@Str,1,1,'')--删除第一个为0的字符 end set @Str=reverse(@Str) return @Str end go select dbo.fnTrimLZero(487.17950) 这是我自己项目中用的一个函数,当然各位有更好的代码欢迎抛砖... 用float肯定是错误的,并且小数位数不确定,numeric(18,2)这种形式肯定也是错的!!!
create function func(@num decimal(18,6)) returns varchar(20) as begin select @num = cast(@num , as varchar(20)); while (charindex('0',@num ,len(@num )) >0) then begin @num = subString(@num ,1,len(@num )-1) end
return @num end
if exists(select Name from sysobjects where name='fnTrimLZero' and type='FN') Drop Function fnTrimLZero go create Function fnTrimLZero(@Num numeric(18,5)) returns varchar(23) as begin RETURN(( SELECT value = LEFT(value, 1 + LEN(value) - PATINDEX('%[^0.]%', REVERSE(value))) FROM( SELECT value = CONVERT(varchar(23), value) FROM( -- 要转换的数据 SELECT value = @Num )A )AA )) end go select dbo.fnTrimLZero(487.17950), dbo.fnTrimLZero(487.000) godrop function fnTrimLZero/* 结果 ----------------------- ----------------------- 487.1795 487(1 行受影响) */
这个感觉不用函数也可以实现吧, SELECT CONVERT(VARCHAR,CAST(@D AS MONEY))
SET @D=12.01000SELECT CONVERT(VARCHAR,CAST(@D AS FLOAT))
/*
12.01
*/
declare @dec decimal(8,5)
set @dec= 12.01000
select str(@dec,5,2)
value = LEFT(value, 1 + LEN(value) - PATINDEX('%[^0]%', REVERSE(value)))
FROM(
SELECT
value = CONVERT(varchar(20), value)
FROM(
-- 要转换的数据
SELECT value = 12.01000
)A
)AA
set @i=12.01
select cast(cast(@i as float)as varchar(5))
/*-----
12.01*/
returns varchar(20)
as
begin
return(cast(cast(@dec as decimal(18,2)) as varchar(20)))
end
--调用
select dbo.fn_dec2varchar(12.01000)
参考下面的测试DECLARE @D DECIMAL(10,7)
SET @D=12.0100101SELECT @D, CONVERT(VARCHAR,CAST(@D AS FLOAT))
/* -- 丢失数据了
------------ ------------------------------
12.0100101 12.01
*/SELECT
value = LEFT(value, 1 + LEN(value) - PATINDEX('%[^0]%', REVERSE(value)))
FROM(
SELECT
value = CONVERT(varchar(20), value)
FROM(
-- 要转换的数据
SELECT value = @D
)A
)AA
/*
value
--------------------
12.0100101
*/
Drop Function fnTrimLZero
go
create Function fnTrimLZero(@Num numeric(18,5)) returns varchar(23)
as
begin
declare @Str varchar(23)
set @Str=reverse(convert(varchar(23),@Num))
--将传入的参数反转以后,如果为整数返回小数点前面的部分
if convert(float,substring(@Str,1,charindex('.',@Str)-1))=convert(float,0)
begin
set @Str=substring(convert(varchar(23),@Num),1,charindex('.',convert(varchar(23),@Num))-1)
return @Str
end
--用循环把第一个是0的字符删除
while left(@Str,1)='0'
begin
set @Str=stuff(@Str,1,1,'')--删除第一个为0的字符
end
set @Str=reverse(@Str) return @Str
end
go
select dbo.fnTrimLZero(487.17950)
这是我自己项目中用的一个函数,当然各位有更好的代码欢迎抛砖...
用float肯定是错误的,并且小数位数不确定,numeric(18,2)这种形式肯定也是错的!!!
returns varchar(20)
as
begin
select @num = cast(@num , as varchar(20));
while (charindex('0',@num ,len(@num )) >0) then
begin
@num = subString(@num ,1,len(@num )-1)
end
return @num
end
Drop Function fnTrimLZero
go
create Function fnTrimLZero(@Num numeric(18,5)) returns varchar(23)
as
begin
RETURN((
SELECT
value = LEFT(value, 1 + LEN(value) - PATINDEX('%[^0.]%', REVERSE(value)))
FROM(
SELECT
value = CONVERT(varchar(23), value)
FROM(
-- 要转换的数据
SELECT value = @Num
)A
)AA
))
end
go
select dbo.fnTrimLZero(487.17950), dbo.fnTrimLZero(487.000)
godrop function fnTrimLZero/* 结果
----------------------- -----------------------
487.1795 487(1 行受影响)
*/
SELECT CONVERT(VARCHAR,CAST(@D AS MONEY))