Rounding a value to million

• Andrew van Zyl

  SSC-Addicted

  Points: 498

  More actions

  April 21, 2005 at 5:25 am

  #67193

  Hi Folks

  Is there an easy way to display a value such as 1 342 671 as 1,3?

  I constructed a case statement that manipulates the string based on the length, I am sure someone has a more elegant way of doing it?

  declare @Value varchar(10)

   select @Value = 9500681

   if len(@Value) = 8

              begin   

                          Select @Value = left(@Value, len(@Value)-6) + ',' + substring(@Value, 3,3)

              end

  else if len(@Value) = 7

              begin   

                          Select @Value = left(@Value, len(@Value)-6) + ',' + substring(@Value, 2,3)

              end

  else if len(@Value) = 6

              begin   

                          Select @Value = '0,' + substring(@Value, 1,3)

              end

  else if len(@Value) = 5

              begin   

                          Select @Value = '0,0' + substring(@Value, 1,3)

              end

  else if len(@Value) = 4

              begin   

                          Select @Value = '0,00' + substring(@Value, 1,3)

              end

  Select @Value

• WilburSmith

  Hall of Fame

  Points: 3225

  More actions

  April 21, 2005 at 5:38 am

  #554041

  ROUND function

   

  select round(1342671.000/1000000,1) ==> 1.3

  select round(13426.000/1000000,1) ==> 0.0

  etc.

   

  Ville