【葉子函數分享十】将數字轉換千分位分隔形式

--建立函數(原創：dobear_0922)

create function [dbo].[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,','), @[email protected]

       end

       select @right=left(stuff(@str,1,charindex('.',@str),''), @n),@len=4

       while @len <=len(@right)

       begin

           select @right=stuff(@right,@len,0,','), @[email protected]+4

       end

       set @end= @left+'.'[email protected]

    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,','), @[email protected]

       end

       select @right=left(stuff(@str,1,charindex('.',@str),''), @n),@len=4

       while @len <=len(@right)

       begin

           select @right=stuff(@right,@len,0,','), @[email protected]+4

       end

       set @end= @left

    end

    return @end

end

--測試示例

select [dbo].[getformatstring](2645433,2)

--運作結果

--如果小數點後面不需要處理的話，我們可以不用函數直接：

select convert(varchar, convert(money, 2645433), 1)

本文來自CSDN部落格，轉載請标明出處：http://blog.csdn.net/maco_wang/archive/2011/03/18/6260274.aspx