formatting varchar as percentage

Question

formatting varchar as percentage

frdrckmitchell7

Ten Centuries

Points: 1351
More actions
November 28, 2013 at 7:19 am

#285627

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

Viewing 9 posts - 1 through 8 (of 8 total)

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

Luis Cazares SSC Guru Points: 183706 More actions · Answer 1

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

LinksUp SSCertifiable Points: 6526 More actions · Answer 2

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/

Alexander Suprun SSCertifiable Points: 6253 More actions · Answer 3

Your client application should take care of formatting.

Alex Suprun

frdrckmitchell7 Ten Centuries Points: 1351 More actions · Answer 4

Hi Luis

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

Kind regards

frdrckmitchell7 Ten Centuries Points: 1351 More actions · Answer 5

Hi Alex

Unfortunately it does not, hence my question.

Kind regards

ben.brugman SSChampion Points: 13369 More actions · Answer 6

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

David Burrows SSC Guru Points: 65143 More actions · Answer 7

CAST(CAST(someVariable as int) as varchar(30)) + '%'

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

Jeff Moden SSC Guru Points: 1004414 More actions · Answer 8

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.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)