format number - SQL Server Forums

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

SQL Server 2008 Forums

Transact-SQL (2008)

format number

Author

Topic

arkiboys
Master Smack Fu Yak Hacker

1433 Posts

Posted - 2012-10-11 : 12:05:33

Hi,
I am trying to write a function wherebased on the passed parameters, the number gets formatted.
It seems to work fine except if you pass the following:
select dbo.fnFormatNumber(0.701, 2, 1, 0)

The return data should be 0.70% but it shows 0.7%

and the other example is if you pass:
select dbo.fnRiskReportFormatNumber(0, 2, 1, 1)

it should return 0.00% rather than 0%

I have placed question marks where I think the function needs working on

Can you see how this can be solved please?
Due to business rules restrictions, etc... this kind of formatting has to be done within this function.

Thanks

alter FUNCTION dbo.fnFormatNumber

(
@num [float], @dp [int], @pct [bit], @times100 [bit]
)
RETURNS varchar(20)
AS
BEGIN
declare @FormattedNum varchar(1000)
declare @NewNum float

--Must be dealing with a number...
if (ISNUMERIC(@num) = 1)
begin

set @NewNum = @num

if (@times100 = 1) -- multiply by 100
begin
set @NewNum = @NewNum * 100
end
if (@dp > 0) --round to specified decimal places...
begin
set @NewNum = ROUND(@NewNum, @dp)

--?????????????
--declare @format varchar(10) = '.'

--declare @i tinyint = 0
--while (@i < @dp)
-- begin
-- set @format += '0'

-- set @i += 1
-- end
end

if (@pct = 1) -- should there be a % sign
begin
set @FormattedNum = convert(varchar(20), @NewNum) + '%'
end
else
begin
set @FormattedNum = convert(varchar(20), @NewNum)
end
end

RETURN @FormattedNum
END

robvolk
Most Valuable Yak

15732 Posts

Posted - 2012-10-11 : 12:45:24

[code]ALTER FUNCTION dbo.fnFormatNumber(@num [FLOAT], @dp [INT], @pct [BIT], @times100 [BIT])
RETURNS VARCHAR(20)
AS
BEGIN
DECLARE @FormattedNum VARCHAR(21)

SET @Num = @num * CASE @times100 WHEN 1 THEN 100 ELSE 1 END
SET @FormattedNum = LTRIM(STR(@num,20,@dp)) + CASE WHEN @pct = 1 THEN '%' ELSE '' END

RETURN @FormattedNum
END[/code]Honestly this is simple enough that you don't need (and shouldn't use) a user-defined function for it. If you run this against large results (100K rows or more) you'll see a significant performance hit as a UDF.

And unless, AND ONLY UNLESS, you have users running queries directly in Management Studio (which they should not be doing either), there is NO business rule that requires numeric formatting in SQL. Every other option can do it more easily and effectively.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-10-11 : 15:17:44

Please be aware that ISNUMERIC can return 1 for some non numeric data as well

see below



SELECT ISNUMERIC('$'),ISNUMERIC(','),ISNUMERIC('-')

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

robvolk
Most Valuable Yak

15732 Posts

Posted - 2012-10-11 : 15:21:24

True, but since he's using ISNUMERIC() on a float, it will always return 1 unless the variable is NULL.

arkiboys
Master Smack Fu Yak Hacker

1433 Posts

Posted - 2012-10-12 : 05:00:29

quote:
Originally posted by robvolk

ALTER FUNCTION dbo.fnFormatNumber(@num [FLOAT], @dp [INT], @pct [BIT], @times100 [BIT])
RETURNS VARCHAR(20)
AS
BEGIN
DECLARE @FormattedNum VARCHAR(21)

SET @Num = @num * CASE @times100 WHEN 1 THEN 100 ELSE 1 END
SET @FormattedNum = LTRIM(STR(@num,20,@dp)) + CASE WHEN @pct = 1 THEN '%' ELSE '' END

RETURN @FormattedNum
END
Honestly this is simple enough that you don't need (and shouldn't use) a user-defined function for it. If you run this against large results (100K rows or more) you'll see a significant performance hit as a UDF.

And unless, AND ONLY UNLESS, you have users running queries directly in Management Studio (which they should not be doing either), there is NO business rule that requires numeric formatting in SQL. Every other option can do it more easily and effectively.

This is good. thank you so much...

arkiboys
Master Smack Fu Yak Hacker

1433 Posts

Posted - 2012-10-12 : 07:20:04

quote:
Originally posted by robvolk

ALTER FUNCTION dbo.fnFormatNumber(@num [FLOAT], @dp [INT], @pct [BIT], @times100 [BIT])
RETURNS VARCHAR(20)
AS
BEGIN
DECLARE @FormattedNum VARCHAR(21)

SET @Num = @num * CASE @times100 WHEN 1 THEN 100 ELSE 1 END
SET @FormattedNum = LTRIM(STR(@num,20,@dp)) + CASE WHEN @pct = 1 THEN '%' ELSE '' END

RETURN @FormattedNum
END
Honestly this is simple enough that you don't need (and shouldn't use) a user-defined function for it. If you run this against large results (100K rows or more) you'll see a significant performance hit as a UDF.

And unless, AND ONLY UNLESS, you have users running queries directly in Management Studio (which they should not be doing either), there is NO business rule that requires numeric formatting in SQL. Every other option can do it more easily and effectively.

Hello,
How do I format the number so that there is thousand separator...

robvolk
Most Valuable Yak

15732 Posts

Posted - 2012-10-12 : 07:28:28

You can hack something with the CONVERT function, there's an option to convert money data types to varchar with a flag to include commas, but you'd have to convert to money and possibly lose decimal places, plus remove the currency symbol. Again, do this kind of formatting elsewhere, SQL doesn't support it properly.

arkiboys
Master Smack Fu Yak Hacker

1433 Posts

Posted - 2012-10-12 : 08:33:21

quote:
Originally posted by robvolk

You can hack something with the CONVERT function, there's an option to convert money data types to varchar with a flag to include commas, but you'd have to convert to money and possibly lose decimal places, plus remove the currency symbol. Again, do this kind of formatting elsewhere, SQL doesn't support it properly.

I am using this in SSRS report and because I am using a matrix report, it does not allow me to format the value.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2012-10-12 : 10:05:18

quote:
I am using this in SSRS report and because I am using a matrix report, it does not allow me to format the value.

Go into the Expression builder for the matrix cell and try this:

=iif(Parameters!Percent.Value,
FormatPercent(Count(Fields!ID2.Value),Parameters!Decimal.Value),
FormatNumber(Count(Fields!ID2.Value),Parameters!Decimal.Value))

I set up report parameters for percent and decimal places, you can substitute whatever source you have for those settings. The same applies for the multiply by 100 parameter.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources