SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 format number
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

arkiboys
Flowing Fount of Yak Knowledge

1416 Posts

Posted - 10/11/2012 :  12:05:33  Show Profile  Reply with Quote
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
15668 Posts

Posted - 10/11/2012 :  12:45:24  Show Profile  Visit robvolk's Homepage  Reply with Quote
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.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 10/11/2012 :  15:17:44  Show Profile  Reply with Quote
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/

Go to Top of Page

robvolk
Most Valuable Yak

USA
15668 Posts

Posted - 10/11/2012 :  15:21:24  Show Profile  Visit robvolk's Homepage  Reply with Quote
True, but since he's using ISNUMERIC() on a float, it will always return 1 unless the variable is NULL.
Go to Top of Page

arkiboys
Flowing Fount of Yak Knowledge

1416 Posts

Posted - 10/12/2012 :  05:00:29  Show Profile  Reply with Quote
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
Go to Top of Page

arkiboys
Flowing Fount of Yak Knowledge

1416 Posts

Posted - 10/12/2012 :  07:20:04  Show Profile  Reply with Quote
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...
Go to Top of Page

robvolk
Most Valuable Yak

USA
15668 Posts

Posted - 10/12/2012 :  07:28:28  Show Profile  Visit robvolk's Homepage  Reply with Quote
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.
Go to Top of Page

arkiboys
Flowing Fount of Yak Knowledge

1416 Posts

Posted - 10/12/2012 :  08:33:21  Show Profile  Reply with Quote
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.
Go to Top of Page

robvolk
Most Valuable Yak

USA
15668 Posts

Posted - 10/12/2012 :  10:05:18  Show Profile  Visit robvolk's Homepage  Reply with Quote
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.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000