# round the value to the nearest

• Smash125

Hall of Fame

Points: 3527

I have a column called as NDM\$ What I want do it round it the nearest value example I am giving below

34.100->34%

39.8->40

35.4->35 some thing like that. please le me know how can I do that

• dbafromthecold@gmail.com

SSChampion

Points: 10079

There's a function in SQL Server called ROUND():-

http://msdn.microsoft.com/en-us/library/ms175003.aspx

• Eirikur Eiriksson

SSC Guru

Points: 182405

Smash125 (9/16/2014)

I have a column called as NDM\$ What I want do it round it the nearest value example I am giving below

34.100->34%

39.8->40

35.4->35 some thing like that. please le me know how can I do that

Quick suggestion, use the round function and set the third parameter to 0.

😎

Example

`SELECT 34.100 AS IN_VAL,ROUND(34.100,0,0) AS OUT_VAL UNION ALL`

``` SELECT 39.8 AS IN_VAL,ROUND(39.8,0,0) UNION ALL ```

`SELECT 35.4 AS IN_VAL,ROUND(35.4,0,0)`

Results

`IN_VAL OUT_VAL`

``` ------- -------- 34.100 34.000 39.800 40.000 35.400 35.000 ```

• Smash125

Hall of Fame

Points: 3527

Can make it as below

34.000->34%

40.000->40%

35.000->35%

• renanguilhem

Valued Member

Points: 64

That should do it

;

WITH

CTE AS

(

SELECT CAST(ROUND(VAL,0,0) AS VARCHAR(10)) AS VAL

FROM 'YOURTABLE'

)

SELECT SUBSTRING(VAL,1,2) + '%' AS NDM\$

FROM CTE

• Smash125

Hall of Fame

Points: 3527

LEFT(CAST(ROUND([Val],0,0) as nvarchar(10)),2) + '%' AS [val]

to get the results some like this

34.100 34.000 ->34%

39.800 40.000 ->40%

35.400 35.000 ->35%

But this does not holds good when the percentage is 100% this holds when the percentage two characters. Can any body guide how to solve this

• Sean Lange

SSC Guru

Points: 286496

Smash125 (9/19/2014)

LEFT(CAST(ROUND([Val],0,0) as nvarchar(10)),2) + '%' AS [val]

to get the results some like this

34.100 34.000 ->34%

39.800 40.000 ->40%

35.400 35.000 ->35%

But this does not holds good when the percentage is 100% this holds when the percentage two characters. Can any body guide how to solve this

The best way to solve this is leave the formatting to the front end. You should pass numeric values in this case to the front end. There the % can be added. If you turn everything into formatted strings the front end can't accurately sort or do any types of calculations without first removing the formatting.

--EDIT--

And why would you use nvarchar? Last time I checked all values for numbers will fit easily in the standard ASCII set so no need for the extended characters here.

_______________________________________________________________