select left(convert(varchar, cast(12356000 as money), 1) , charindex('.', convert(varchar, cast(12356000 as money), 1))-1)
declare @value int,@i int,@j int,@s varchar(100) select @i=1, @j=0, @value=123000, @s=rtrim(@value)while @i<=(len(@value)-1)/3 begin select @s=stuff(@s,(len(@s)-@i*3+1)-@j,0,',') set @i=@i+1 set @j=@j+1 end select @s/* ---------------------------------------------------------------------------------------------------- 12,300,034(1 行受影响)*/
create function fn_qianwei(@s varchar(100)) returns varchar(100) as begin declare @result varchar(100),@i int if CHARINDEX('.',@s)>0 begin select @s=LEFT(@s,CHARINDEX('.',@s)-1) end select @i=LEN(@s) select @result='' while @i>3 begin select @result=','+RIGHT(@s,3)+@result select @s=LEFT(@s,@i-3) select @i=@i-3 end select @result=@s+@result return @result end select dbo.fn_qianwei('19800012.123')--19,800,012
select
@i=1,
@j=0,
@value=123000,
@s=rtrim(@value)while @i<=(len(@value)-1)/3
begin
select @s=stuff(@s,(len(@s)-@i*3+1)-@j,0,',')
set @i=@i+1
set @j=@j+1
end
select @s/*
----------------------------------------------------------------------------------------------------
12,300,034(1 行受影响)*/
returns varchar(100)
as
begin
declare @result varchar(100),@i int
if CHARINDEX('.',@s)>0
begin select @s=LEFT(@s,CHARINDEX('.',@s)-1)
end
select @i=LEN(@s)
select @result=''
while @i>3
begin
select @result=','+RIGHT(@s,3)+@result
select @s=LEFT(@s,@i-3)
select @i=@i-3
end
select @result=@s+@result
return @result
end
select dbo.fn_qianwei('19800012.123')--19,800,012