| Author |
 Topic  |
|
|
arkiboys
Flowing Fount of Yak Knowledge
1346 Posts |
 Posted - 10/11/2012 : 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
USA
15568 Posts |
Posted - 10/11/2012 : 12:45:24
|
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. |
 |
|
|
visakh16
Very Important crosS Applying yaK Herder
India
48076 Posts |
Posted - 10/11/2012 : 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
USA
15568 Posts |
Posted - 10/11/2012 : 15:21:24
|
| True, but since he's using ISNUMERIC() on a float, it will always return 1 unless the variable is NULL. |
|
|
|
arkiboys
Flowing Fount of Yak Knowledge
1346 Posts |
Posted - 10/12/2012 : 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... |
Edited by - arkiboys on 10/12/2012 05:10:26 |
|
|
|
arkiboys
Flowing Fount of Yak Knowledge
1346 Posts |
Posted - 10/12/2012 : 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
USA
15568 Posts |
Posted - 10/12/2012 : 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
Flowing Fount of Yak Knowledge
1346 Posts |
Posted - 10/12/2012 : 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
USA
15568 Posts |
Posted - 10/12/2012 : 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. |
|
|
| |
Topic |
|
|
|
format number
