將資料轉換成金錢格式

go
alter function dbo.fnConvertToMoneyFormat(@Money varchar(100))
returns varchar(100)
as
begin
 set @Money=cast(cast(@Money as numeric(18,2)) as varchar(100))
 --select @Money 金額;

 declare @Sql varchar(500)='',@EndPosition int;
 set @EndPosition=CHARINDEX('.',@Money)-1;
 --select @EndPosition;

 declare @Tail varchar(2);
 set @Tail=SUBSTRING(@Money,@EndPosition+2,2);
 --select @Tail;

 declare @PreDecimalPointPosition int=@EndPosition

 while @EndPosition>0
 begin
  if @PreDecimalPointPosition<>@EndPosition and (@PreDecimalPointPosition-@EndPosition)%3=0
   set @Sql=SUBSTRING(@Money,@EndPosition,1)+','+@Sql;
  else
   set @Sql=SUBSTRING(@Money,@EndPosition,1)+@Sql;

  set @EndPosition-=1;
 end
 set @Sql+=','+@Tail;

 return @Sql;
end

--select dbo.fnConvertToMoneyFormat('7123456789101112.789') 轉換後