CODEWITHSUNDEEP
sqlsql-server-2008
NoviceToDotNet
NoviceToDotNet

Reputation: 10805

How to make the result set of two digit in the SQL query?

I have the following query but the results in distance are more than one digit, I want it to be 2 digit only.

UPDATE Customer 
SET Distance = CAST(CAST(REPLACE(REPLACE(distance, 'km' , '' ), 'miles', '')as float) * 1.3 * 0.62137 AS NVARCHAR) + 'Miles' 
FROM customer

If I have result like 2.3434453433 then I want it to change to 2.3

Upvotes: 2

Views: 467

Answers (4)

Romesh
Romesh

Reputation: 2274

Approach :

Here is SQLFiddel Demo for Approach

select Convert(varchar(3),convert(numeric(5,1),(2.3434453433)))

Solution :

UPDATE Customer 
   set Distance= Convert(varchar(3),Convert(Numeric(5,1),
                    CAST(CAST(REPLACE(REPLACE(distance, 'km' , '' ),
                   'miles', '')as float) * 1.3 * 0.62137 AS NVARCHAR))) + 'Miles'

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1270091

It is unclear to me why you would want to store the value as a string instead of just formatting it on output.

Your expression seems a bit confused, multiplying by 1.3 and multiplying by 0.62. I think you want something like this:

UPDATE Customer
    set Distance = (case when right(distance, 3) = ' km'
                         then replace(str(cast(left(distance, charindex(' ', distance) - 1),
                                       as float)/0.62137, 6, 1), ' ', '') + 'Miles'
                         else distance
                    end)

The str() function converts a number to a specified string format with a given length and precision.

However, it seems like you really should change the Customer table to have a distance, a unit, and a "DistanceMiles" field, which is computed column combining the values from the other two. It would be something like:

    DistanceMiles as (case when Units = 'Mile' 
                           then replace(str(Distance, 6, 1), ' ', '')+' Miles'
                           when Units = 'km'
                           then replace(str(Distance/0.62137, 6, 1), ' ', '')+' Miles'
                      end)

Upvotes: 1

Dinup Kandel
Dinup Kandel

Reputation: 2505

You can use the round function if you the round value 

UPDATE Customer set Distance=
    CAST(round(CAST(REPLACE(REPLACE(distance, 'km' , '' ), 'miles', '')as float) * 1.3 * 0.62137,1) AS NVARCHAR) 
    + 'Miles' FROM customer

Upvotes: 1

Luis LL
Luis LL

Reputation: 2993

SELECT 
CAST(CAST(CAST(REPLACE(REPLACE(distance, 'km' , '' ), 'miles', '')as float) * 1.3 * 0.62137 AS DECIMAL(19,1)) AS NVARCHAR) 
+ 'Miles' FROM customer

Upvotes: 1

Related Questions