Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

formatting varchar as percentage Expand / Collapse
Author
Message
Posted Thursday, November 28, 2013 7:19 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Wednesday, August 31, 2016 8:08 AM
Points: 87, Visits: 271
Hi all the clever ones

I have a variable

someVariable(varchar(30), null) in our database

The vaues in this variable look like this:
8.000
7.000
6.000

How can I have these values displayed as a percentage?
8%
7%
6%

Kind regards
Fred
Post #1518359
Posted Thursday, November 28, 2013 9:04 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 1:32 PM
Points: 7,843, Visits: 16,961
What would happen with a value like 8.352?


Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?


How to post data/code on a forum to get the best help: Option 1 / Option 2
Post #1518373
Posted Thursday, November 28, 2013 9:52 AM


Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Yesterday @ 9:15 PM
Points: 675, Visits: 3,584
This link should explain what needs to be done.

http://www.sqlservercentral.com/Forums/Topic558696-338-1.aspx

However, you may need an additional cast since your numerics are stored in a non-numeric datatype.

Also, if you look at the solution, it is a lot of work to get what you want and depending upon how many rows this is being applied against, it could slow the query down. That is why it is recommended that formatting of this nature be done at the presentation layer instead of the backend.


__________________________________________________________________________________________________________
How to Post to get the most: http://www.sqlservercentral.com/articles/Best+Practices/61537/
Post #1518384
Posted Thursday, November 28, 2013 2:24 PM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Yesterday @ 3:38 PM
Points: 336, Visits: 1,501
Your client application should take care of formatting.


Alex Suprun
Post #1518408
Posted Thursday, November 28, 2013 11:03 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Wednesday, August 31, 2016 8:08 AM
Points: 87, Visits: 271
Hi Luis

It's always rounded to the full decimal, ie, 7.000, 8.000.

Kind regards
Post #1518442
Posted Thursday, November 28, 2013 11:04 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Wednesday, August 31, 2016 8:08 AM
Points: 87, Visits: 271
Hi Alex

Unfortunately it does not, hence my question.

Kind regards
Post #1518443
Posted Friday, November 29, 2013 2:20 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: 2 days ago @ 7:13 AM
Points: 437, Visits: 2,110
Alexander Suprun (11/28/2013)
Your client application should take care of formatting.


Sometimes the client application is less suetable for the formatting.

For example I sometimes do the formatting within SQL-server when serving for the Reporting services. Although within the reporting there are plenty of possibilities to do 'presentation', you always need plenty of locations to get this in the code.

So although SSRS should do all presentation functions, I do often shift presentation code to a Stored procedure, for me this makes the code more flexible and cheaper to maintain.

Ben
Post #1518474
Posted Friday, November 29, 2013 6:56 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 10:29 AM
Points: 7,802, Visits: 9,177
CAST(CAST(someVariable as int) as varchar(30)) + '%'


Far away is close at hand in the images of elsewhere.

Anon.

Post #1518545
Posted Friday, November 29, 2013 12:31 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Yesterday @ 9:11 PM
Points: 41,438, Visits: 38,772
frdrckmitchell7 (11/28/2013)
Hi Alex

Unfortunately it does not, hence my question.

Kind regards


Understood by why not fix the client side? What is preventing that?


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

Helpful Links:
How to post code problems
How to post performance problems
Post #1518593
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse