create function GetFormatString(@dec decimal(28,8), @n int) returns varchar(32) as begin declare @str varchar(32), @len int, @left varchar(32), @right varchar(32) set @str= round(@dec,@n) select @left=left(@str,charindex('.',@str)-1),@len=len(@left)-2 while @len>1 begin select @left=stuff(@left,@len,0,','), @len=@len-3 end select @right=left(stuff(@str,1,charindex('.',@str),''), @n),@len=4 while @len<=len(@right) begin select @right=stuff(@right,@len,0,','), @len=@len+4 end return @left+'.'+@right end goselect dbo.GetFormatString(123645.7889, 2)+'±'+'MON' union all select dbo.GetFormatString(123645.7889, 3)+'±'+'MON' union all select dbo.GetFormatString(123645.7889, 4)+'±'+'MON' /* ------------------------------------- 123,645.79±MON 123,645.789±MON 123,645.788,9±MON(3 行受影响) */drop function dbo.GetFormatString
select convert(varchar,cast(cast('123645.7889' as money)+0.005 as decimal(18,3)),1)+'±'+'MON'
declare @s varchar(20) set @s=convert(varchar,cast('123645' as money),1) select left(@s,len(@s)-2)+right(round('0.7889',3),3)+'±'+'MON' /* --------------- 123,645.789±MON */
select convert(varchar(20),cast('123645.7889' as money),1) -------------------- 123,645.79
create function GetFormatString(@dec decimal(28,8), @n int) returns varchar(32) as begin declare @str varchar(32), @len int, @left varchar(32), @right varchar(32),@end varchar(32) if @n!='0' BEGIN set @str= round(@dec,@n) select @left=left(@str,charindex('.',@str)-1),@len=len(@left)-2 while @len>1 begin select @left=stuff(@left,@len,0,','), @len=@len-3 end select @right=left(stuff(@str,1,charindex('.',@str),''), @n),@len=4 while @len<=len(@right) begin select @right=stuff(@right,@len,0,','), @len=@len+4 end set @end= @left+'.'+@right end else BEGIN set @str= round(@dec,@n) select @left=left(@str,charindex('.',@str)-1),@len=len(@left)-2 while @len>1 begin select @left=stuff(@left,@len,0,','), @len=@len-3 end select @right=left(stuff(@str,1,charindex('.',@str),''), @n),@len=4 while @len<=len(@right) begin select @right=stuff(@right,@len,0,','), @len=@len+4 end set @end= @left+@right end return @end end
select convert(varchar,cast('123645.7889' as money+0.005),1)+'±'+'MON'
select convert(varchar,cast('123645.7889' as money)+0.005,1)+'±'+'MON'
接近 '+' 之處的語法不正確。千分位
111,222,333
select convert(varchar,STR('123645.7889',10, 3),1)+'±'+'MON'
自己写函数
[/Quote。]
select STR('123645.7889',10, 3)
returns varchar(32) as
begin
declare @str varchar(32), @len int, @left varchar(32), @right varchar(32)
set @str= round(@dec,@n)
select @left=left(@str,charindex('.',@str)-1),@len=len(@left)-2
while @len>1
begin
select @left=stuff(@left,@len,0,','), @len=@len-3
end
select @right=left(stuff(@str,1,charindex('.',@str),''), @n),@len=4
while @len<=len(@right)
begin
select @right=stuff(@right,@len,0,','), @len=@len+4
end
return @left+'.'+@right
end
goselect dbo.GetFormatString(123645.7889, 2)+'±'+'MON'
union all select dbo.GetFormatString(123645.7889, 3)+'±'+'MON'
union all select dbo.GetFormatString(123645.7889, 4)+'±'+'MON'
/*
-------------------------------------
123,645.79±MON
123,645.789±MON
123,645.788,9±MON(3 行受影响)
*/drop function dbo.GetFormatString
set @s=convert(varchar,cast('123645' as money),1)
select left(@s,len(@s)-2)+right(round('0.7889',3),3)+'±'+'MON'
/*
---------------
123,645.789±MON
*/
--------------------
123,645.79
create function GetFormatString(@dec decimal(28,8), @n int)
returns varchar(32) as
begin
declare @str varchar(32), @len int, @left varchar(32), @right varchar(32),@end varchar(32)
if @n!='0'
BEGIN set @str= round(@dec,@n)
select @left=left(@str,charindex('.',@str)-1),@len=len(@left)-2
while @len>1
begin
select @left=stuff(@left,@len,0,','), @len=@len-3
end
select @right=left(stuff(@str,1,charindex('.',@str),''), @n),@len=4
while @len<=len(@right)
begin
select @right=stuff(@right,@len,0,','), @len=@len+4
end
set @end= @left+'.'+@right
end
else
BEGIN set @str= round(@dec,@n)
select @left=left(@str,charindex('.',@str)-1),@len=len(@left)-2
while @len>1
begin
select @left=stuff(@left,@len,0,','), @len=@len-3
end
select @right=left(stuff(@str,1,charindex('.',@str),''), @n),@len=4
while @len<=len(@right)
begin
select @right=stuff(@right,@len,0,','), @len=@len+4
end
set @end= @left+@right
end
return @end
end