display values upto 1 decimal without function.

Question

Post reply

display values upto 1 decimal without function.

abhas

SSCarpal Tunnel

Points: 4681
More actions
September 11, 2014 at 4:42 am

#397329

Hi All,
I am having values as below:
99.87
99.96
8.67
And my output should be as:
99.8
99.9
8.6
How can I do this
Thanks
Abhas
September 11, 2014 at 5:34 am

This was removed by the editor as SPAM

Viewing 15 posts - 1 through 15 (of 21 total)

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

Eugene Elutin SSC Guru Points: 59322 More actions · Answer 1

If you output to client, do it there.

If you need this values for futher use in SQL, you have multiple options.

Some here:

SELECT ROUND(val,1,1)

,CONVERT(DECIMAL(10,1),ROUND(val,1,1))

FROM (VALUES (99.87), (99.96), (8.67)) sampledata(val)

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help[/url]

Shadab Shah SSCarpal Tunnel Points: 4759 More actions · Answer 2

I guess this might help

SELECT cast (round(11.63,1) as numeric(12,1))

/*Replace your column name instead 11.63*/

Eugene Elutin SSC Guru Points: 59322 More actions · Answer 3

Shadab Shah (9/11/2014)
I guess this might help
SELECT cast (round(11.63,1) as numeric(12,1))
/*Replace your column name instead 11.63*/

Have you tested it with just one value?

Try:

SELECT cast (round(11.67,1) as numeric(12,1))

OP wants value truncation not rounding. So, third parameter in ROUND function does it...

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help[/url]

Eugene Elutin SSC Guru Points: 59322 More actions · Answer 4

jacksonandrew321 (9/11/2014)
Hi u can use as
SELECT CAST(AVG(values) AS DECIMAL(10,1))
FROM tablename;
Thanks

Yep, or he could use:

SELECT CASE WHEN SUM(values * 1.234) + MAX(values)/64 > 1 THEN 'Good' ELSE 'Also Good' END AS BestResults

FROM tablename;

or any other select query which does select and applies some different functions...

Nothing stops SQL Server to successfully compile a query as long as its sytax is valid!

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help[/url]

abhas SSCarpal Tunnel Points: 4681 More actions · Answer 5

Hi All,

Thanks for reply.

But is it possible search "." and select only one character after ".". because dataype is varchar here.

kindly help.

Thanks,

Abhas.

Eugene Elutin SSC Guru Points: 59322 More actions · Answer 6

abhas (9/11/2014)
Hi All,
Thanks for reply.
But is it possible search "." and select only one character after ".". because dataype is varchar here.
kindly help.
Thanks,
Abhas.

It is possible by plain stupid... Why do you keep numeric data as varchar?

Bythe way given code would work for varchars as long as you have numeric values there:

SELECT val, ROUND(val,1,1)

,CONVERT(DECIMAL(10,1),ROUND(val,1,1))

FROM (VALUES ('99.87'), ('99.96'), ('8.67')) sampledata(val)

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help[/url]

abhas SSCarpal Tunnel Points: 4681 More actions · Answer 7

Hi,

put varchar datatype, why because actual data is like: Sorry for not mentioning earlier.

99.87%, 99.96%, 8.67%

Thanks,

Abhas.

abhas SSCarpal Tunnel Points: 4681 More actions · Answer 8

Hi all,

in addition above.

Hi,

put varchar datatype, why because actual data is like: Sorry for not mentioning earlier.

99.87%, 99.96%, 8.67%

and out put want as

99.8%

99.9%

8.6%

Thanks,

Abhas.

thomashohner SSCrazy Eights Points: 9856 More actions · Answer 9

I'm not too god with these but this works as long as the % doesn't go over 99.99

SELECT SUBSTRING('99.87%', LEN('99.87%') -5,4)+'%'

***SQL born on date Spring 2013:-)

Eugene Elutin SSC Guru Points: 59322 More actions · Answer 10

abhas (9/11/2014)
Hi all,
in addition above.
Hi,
put varchar datatype, why because actual data is like: Sorry for not mentioning earlier.
99.87%, 99.96%, 8.67%
and out put want as
99.8%
99.9%
8.6%
Thanks,
Abhas.

Even worst that I thought!

Why wouldn't you just strip off %, store data as it should be eg. numeric, then use it as numeric, and if whatever client wants to format it as percentage let it do it. Or, at the end, add % whenever you asked to extract this column data. Otherwise, what are you going top do if the next requirement will: Please calculate something based on this percentage value.

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help[/url]

thomashohner SSCrazy Eights Points: 9856 More actions · Answer 11

Ignore my first attempt here you go.

SELECT substring('99.78%',1,len('99.78%')-2) + '%'

***SQL born on date Spring 2013:-)

Isip Bata SSC Veteran Points: 200 More actions · Answer 12

SELECT val

,STUFF(val, CHARINDEX('%', val)-1, 1, '') AS truncval

FROM (VALUES ('99.87%'), ('99.96%'), ('8.67%')) AS sample(val)

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 13

Just use the optional 3rd value in the ROUND function:

SELECT cast (round(11.67,1,1) as numeric(12,1))