Format Strings For Fractional Numbers And Currency

/*
Format strings for fractional Numbers and Currency values

Return Values: VARCHAR
Parameters: @n Specifies numeric expression to format.
@sFormat: Specifies one or more format codes that determine how the expression is formatted.

The following table lists the available format codes.

9.000000|00 |09
12.100000|### |12
12345.120000|### ### ###.000|12 345.120
12345.120000|### ### ###.###|12 345.12
12345.120000|$ ### ### ###.000|$ 12 345.120
12345.120000|### ### ###.### $|12 345.12 $
12345.120000|$ ###,###,###.000|$ 12,345.120
12345.120000|### ### ###.000|12 345.120
12345.120000| |12345
1.120000|### ### ###.000|1.120
12.120000|### ### ###.000|12.120
123.120000|### ### ###.000|123.120
1234.120000|### ### ###.000|1 234.120
12345.120000|### ### ###.000|12 345.120
123456.120000|### ### ###.000|123 456.120
1234567.120000|### ### ###.000|1 234 567.120
12345678.120000|### ### ###.000|12 345 678.120
123456789.120000|### ### ###.000|123 456 789.120
1234567890.120000|### ### ###.000|1234 567 890.120
12345678901.120000|### ### ###.000|12345 678 901.120
123456789012.120000|### ### ###.000|123456 789 012.120

*/

CREATE FUNCTION xNumberFormat(@n NUMERIC(38, 4), @sFormat VARCHAR(255))
RETURNS VARCHAR(255) AS
BEGIN
DECLARE @sRet VARCHAR(255), @i TINYINT, @j INT, @nDec TINYINT, @sNumber VARCHAR(255), @cF CHAR(1), @cR CHAR(1), @sE VARCHAR(255), @sX VARCHAR(255)

SELECT @sE = '', @i = LEN(@sFormat)
WHILE @i > 0 AND SUBSTRING(@sFormat, @i, 1) NOT IN ('#', '0') SELECT @sE = SUBSTRING(@sFormat, @i, 1) + @sE, @i = @i -1
SELECT @sFormat = LEFT(@sFormat, @i), @sX = '', @i = 1
WHILE @i < LEN(@sFormat) AND SUBSTRING(@sFormat, @i, 1) NOT IN ('#', '0') SELECT @sX = @sX + SUBSTRING(@sFormat, @i, 1), @i = @i +1
SELECT @sFormat = RIGHT(@sFormat, LEN(@sFormat) - @i + 1)

IF @n = 0 AND CHARINDEX('0', @sFormat) = 0 AND @sE = '' AND @sX = '' RETURN ''

SET @nDec = CHARINDEX('.', @sFormat)
IF @nDec > 0 SET @nDec = LEN(@sFormat) - @nDec

SET @sNumber = RTRIM(LTRIM(STR(@n, 255, @nDec)))

IF @nDec > 0 SET @nDec = @nDec + 1

SET @sRet = RIGHT(@sNumber, @nDec)

IF @nDec > 0
BEGIN
SET @i = 1
WHILE RIGHT(@sRet, 1) = '0' AND SUBSTRING(@sFormat, LEN(@sFormat) - @i + 1, 1) = '#' SELECT @sRet = LEFT(@sRet, LEN(@sRet) - 1), @i = @i + 1
IF @sRet = '.' SET @sRet = ''
END

SELECT @i = @nDec + 1, @j = @nDec + 1
WHILE @i <= LEN(@sFormat) AND @j <= LEN(@sNumber)
BEGIN
SELECT @cF = SUBSTRING(@sFormat, LEN(@sFormat) - @i + 1, 1), @cR = SUBSTRING(@sNumber, LEN(@sNumber) - @j + 1, 1)
IF @cF NOT IN ('#', '0')
IF @j = LEN(@sNumber) AND @n < 0 SET @i = @i + 1 ELSE SELECT @sRet = @cF + @sRet, @i = @i + 1
ELSE
SELECT @sRet = @cR + @sRet, @i = @i + 1, @j = @j +1
END
IF @j <= LEN(@sNumber) SET @sRet = LEFT(@sNumber, LEN(@sNumber) - @j + 1) + @sRet
WHILE @i <= LEN(@sFormat) AND SUBSTRING(@sFormat, @i - @j + 1 , 1) = '0' SELECT @sRet = '0' + @sRet, @i = @i + 1
RETURN @sX + @sRet + @sE
END