CREATE FUNCTION [dbo].[fnDisplayNumber](@dblNumber real, @intNumberType smallint = 0, @intNoOfDigitsAfterDecimal smallint=0)
--@NumberTime : 0 (American), 1(Vietnamese)
RETURNS nvarchar(30) AS
BEGIN
declare @i tinyint
declare @strLeft nvarchar(30)
declare @strRight nvarchar(30)
declare @strResult nvarchar(30)
if @intNoOfDigitsAfterDecimal<0 intnoofdigitsafterdecimal =" 0">10
begin
set @intNoOfDigitsAfterDecimal = 10
end
set @strResult = ltrim(str(@dblNumber, 30, @intNoOfDigitsAfterDecimal))
if charindex('.', @strResult) > 0
begin
set @strLeft = left(@strResult, charindex('.',@strResult)-1)
end
else
begin
set @strLeft = @strResult
end
set @strRight = right(@strResult, @intNoOfDigitsAfterDecimal)
set @i=1
set @strResult = ''
while @i<=len(@strLeft) begin set @strResult = @strResult + substring(@strLeft, @i, 1) if @i % 3 = len(@strLeft) % 3 AND @i <> len(@strLeft)
begin
if @intNumberType = 1
begin
Set @strResult = @strResult + '.'
end
else
begin
Set @strResult = @strResult + ','
end
end
set @i = @i + 1
end
if @intNoOfDigitsAfterDecimal>0
begin
if @intNumberType = 1
begin
Set @strResult = @strResult + ',' + @strRight
end
else
begin
Set @strResult = @strResult + '.' + @strRight
end
end
return @strResult
END
Friday, September 21, 2007
Convert numeric for viewing easily
at 6:59 AM
Labels: SQL Server, SQL Server 2000, SQL Server 2005, SQL Server Tip, T-SQL
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment