Format strings for fractional Numbers and Currency

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

Script Library

Format strings for fractional Numbers and Currency

Author

Topic

Hunglech
Starting Member

16 Posts

Posted - 2005-11-17 : 02:16:19

/*
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

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-11-17 : 03:22:12

Well. Usually this type of currency formation is done in the presentation layer easily using Format function. But this can be used when you export data to text files

I think this is simple

Declare @m money
set @m=123456789012.120000
select replace(convert(varchar(30),@m,1),',',' ') as FormatedCurrency

Madhivanan

Failing to plan is Planning to fail

Hunglech
Starting Member

16 Posts

Posted - 2005-11-17 : 03:38:17

Thanks for your post, with this function i can convert to string format like 009 or 123,456,789 012.120$.. that i didn't found in function Convert

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2005-11-17 : 10:06:51

123,456,789 012.120$??? Yeah, I'm sure I'll have a LOT of requests for that format. And once you export it, I pitty the poor dba who has to write code to REVERSE FORMAT your string back into something usable.

alemos
Starting Member

16 Posts

Posted - 2007-11-16 : 13:49:37

Great function! I am going to mod it a little to include decimal separators and such and then I'll post my modification. Thanks!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources