Percentage Functionality in t-SQL

Question

Post reply

Percentage Functionality in t-SQL

Gangadhara MS

Hall of Fame

Points: 3377
More actions
June 3, 2011 at 3:26 am

#241633

Dear All,
I need to implement the percentage functionality in T-SQL which is very similar to Excel 2007 where i can round off and display proper value.
And also need to concatenate the % symbol to it,pls help me.
Input Output
Ex: -4.0283739395038591 --> -4%
9.8039215686274508 --> 1%
-6.358128786459309 --> -6%
Thanks,
Ganga

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

s_osborne2 SSCertifiable Points: 6726 More actions · Answer 1

Hi,

This should do it.

DECLARE @table TABLE (number DECIMAL(19,15))

INSERT INTO @table VALUES (-4.0283739395038591)

INSERT INTO @table VALUES (9.803921568627450)

INSERT INTO @table VALUES (-6.358128786459309)

SELECT CONVERT(VARCHAR(4),CONVERT(INT,ROUND(CAST(number AS DECIMAL(4,2)), 0))) + '%'

FROM @table

Thanks,

Simon

MCSE: Data Platform
MCSE: Business Intelligence
Follow me on Twitter: @WazzTheBadger
LinkedIn Profile: Simon Osborne

drew.allen SSC Guru Points: 76984 More actions · Answer 2

This is something that is best left to the presentation layer.

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

Gangadhara MS Hall of Fame Points: 3377 More actions · Answer 3

Hi,

I am using the data format as below.

DECLARE @table TABLE (number nvarchar(max))

INSERT INTO @table VALUES (4.5267489711934158E-2)

INSERT INTO @table VALUES (8.8356729975227088E-2)

INSERT INTO @table VALUES (9.9356025758969638E-2)

SELECT CONVERT(VARCHAR(4),CONVERT(INT,ROUND(CAST(number AS DECIMAL(4,2)), 0))) + '%'

FROM @table

But its not returning with proper result.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

drew.allen (6/3/2011)
This is something that is best left to the presentation layer.

Completely agree. This is something that should be done in the client app, not in the database.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

Gangadhara MS (6/5/2011)
DECLARE @table TABLE (number nvarchar(max))

Why on earth are you using an NVarchar(max)? Expecting numbers with 2 billion unicode digits somehow?

But its not returning with proper result.

With that level of detail, there's not a chance in hell anyone's going to be able to help you.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

nigel. SSChampion Points: 11636 More actions · Answer 6

Gangadhara MS (6/5/2011)
Hi,
I am using the data format as below.
DECLARE @table TABLE (number nvarchar(max))
INSERT INTO @table VALUES (4.5267489711934158E-2)
INSERT INTO @table VALUES (8.8356729975227088E-2)
INSERT INTO @table VALUES (9.9356025758969638E-2)
SELECT CONVERT(VARCHAR(4),CONVERT(INT,ROUND(CAST(number AS DECIMAL(4,2)), 0))) + '%'
FROM @table
But its not returning with proper result.

A clue:

You need to multiply by 100.0 somewhere in there ! 🙂

Again (as Gail asked), why nvarchar(max)?

Nigel
How to post questions