Results 1 to 6 of 6
  1. #1
    Join Date
    Mar 2010
    Posts
    3

    Unanswered: SQL Server - Removing Exponential Notation

    I am exporting a large amount of data from SQL Server 2005 to Excel. Most of the data appears as I want it to- without the exponential notation. Any values smaller than 0.0001 (i.e. four zeros to the right of the decimal) are displayed as, for example, 7E-05 rather than 0.00007.
    I know that I could use a fixed format, however the values in this field have a broad range of precisions (e.g. 1250, 1.3, 0.00345, 0.000001) and I would like to maintain the original number formats.
    I also realize that Excel can convert exponential notation to non-exponential, however I am including a '<' sign with many values. That being said, I am concatenating a string (with '<') with a numeric field into a new string field before exporting. Unfortunately when Server concatenates this fields it does so with the exponential format so that my values appear as <1E-05.

    I know that with Oracle you can use SQLPLUS to set column formats for queries. Is there anything like that which can be used in MS SQL Server?

    Thanks in advance...

  2. #2
    Join Date
    Mar 2010
    Posts
    3
    anyone? no ideas?

  3. #3
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    What is the datatype of the underlying column(s)? SQL Server actually manages your data, the presentation layer (your web page or application like Excel) should manage formatting data for your locale.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  4. #4
    Join Date
    Mar 2010
    Posts
    3
    Pat,
    First I am converting a float into varchar
    cast(round(parameter_result.result_value, 8) as varchar(20)) result
    and then I am concatenating the 'result' field with the 'qualifier' (i.e. "<") field in another select statement.
    Obviously SQL Server decides that when the 'result_value' is cast, it is done with the 1E-05 format. That is fine when the result is not less than the value, as applying general number formatting in Excel takes care of it. But when it is <1E-05, then extra steps are required in Excel to deal with it.

  5. #5
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    I feel dirty
    Code:
    DECLARE    @f  AS FLOAT(53)
    
    SELECT  @f  = 0.0000000001
    
    SELECT  *
          , SUBSTRING(fv, 1, 1 + LEN(fv) - PATINDEX('&#37;[^0]%', REVERSE(fv)))
    FROM    
            (
                SELECT  f       = @f
                      , fv      = CAST(CAST(@f AS DECIMAL(28, 20)) AS VARCHAR)
            ) AS formats
    EDIT - changed for SQL Server 2005 (missed that first time)
    Last edited by pootle flump; 04-06-10 at 15:40.

  6. #6
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Quote Originally Posted by pootle flump View Post
    I feel dirty
    It's good to know that you're enjoying yourself!

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •