Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
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 Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, May 8, 2014 2:25 AM
Points: 45, Visits: 165
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


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 6:25 PM
Points: 3,333, Visits: 7,193
What would happen with a value like 8.352?


Luis C.
I am a great believer in luck, and I find the harder I work the more I have of it. Stephen Leacock

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1518373
Posted Thursday, November 28, 2013 9:52 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Today @ 12:46 AM
Points: 341, Visits: 1,301
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


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, July 17, 2014 3:21 PM
Points: 185, Visits: 917
Your client application should take care of formatting.


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

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, May 8, 2014 2:25 AM
Points: 45, Visits: 165
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 Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, May 8, 2014 2:25 AM
Points: 45, Visits: 165
Hi Alex

Unfortunately it does not, hence my question.

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

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, July 21, 2014 2:19 AM
Points: 246, Visits: 1,166
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 @ 8:45 AM
Points: 7,048, Visits: 6,797
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-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 12:14 AM
Points: 36,735, Visits: 31,186
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

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

Add to briefcase

Permissions Expand / Collapse