display values with two precision values

Question

Post reply

display values with two precision values

nabajyoti.b

SSCrazy

Points: 2177
More actions
January 8, 2009 at 3:31 am

#155103

I have a column columnx of datatype float from a tabletablex and another column columny of the same table which is of datatype nvarchar(2).
First comlumn percent of columny is found and then
concatenate the two and put the results in a third columnn which is of datatype nvarchar(8).
For example if columny contains A and columnx contains 1
it should give A100.00%
I am using the following code:
select [tblInvoiceDetail].[columny] + convert(nvarchar(5),([columnx]*100.00)) + '%' AS TaxCode from tablex
which is only displaying A100% without the decimals. Could you please let me know, how do i go about this. Also, given the fact that the destination column is of nvarchar(8).

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

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

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 1

DECLARE @columnx FLOAT, @columny nvarchar(2)

SELECT @columnx = 1, @columny = 'A'

SELECT @columny + convert(varchar(5),@columnx*100.00) + '%',

@columny + STR(@columnx*100, 6,2) + '%'

Output:

-------- ---------

A100% A100.00%

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

nabajyoti.b SSCrazy Points: 2177 More actions · Answer 2

DECLARE @columnx FLOAT, @columny nvarchar(2)

SELECT @columnx = 0.05, @columny = 'A'

SELECT @columny + STR(@columnx*100, 6,2) + '%'

displays A 5.00% which is having 2 spaces in between A and 5

whereas it should display A5.00%

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 3

nabajyoti.b (1/8/2009)
DECLARE @columnx FLOAT, @columny nvarchar(2) SELECT @columnx = 0.05, @columny = 'A'
SELECT @columny + STR(@columnx*100, 6,2) + '%'
displays A 5.00% which is having 2 spaces in between A and 5
whereas it should display A5.00%

DECLARE @columnx FLOAT, @columny nvarchar(2)

SELECT @columnx = 0.05, @columny = 'A'

SELECT @columny + LTRIM(STR(@columnx*100, 6,2)) + '%'

Sometimes you have to hand over the fish.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

nabajyoti.b SSCrazy Points: 2177 More actions · Answer 4

Thanks....

i should have used LTRIM without writing it here 🙂

nabajyoti.b SSCrazy Points: 2177 More actions · Answer 5

Oh! that is going to be fine as the field is percentage and logically it itself defining the rule.

so it will never be 101 percent or 101 per 100 as the column is always <= 1

so it wont exceed 100.

SimonD-978595 SSChasing Mays Points: 618 More actions · Answer 6

Yes, apologies for that. I deleted my post when I reread the query as it became obvious to me that 1 = 100% and you were dealing with fractions.

No probs,

Si