Post reply

Looking for a quick fix to data formatting problem

  • August 21, 2009 at 6:11 am

    #385225

    I recently imported some data from Excel into a table. Three columns contain two digit whole numbers with two digit decimals (XX.XX). Most of these cells contain trailing decimals out to 15 places. For example, if the value is supposed to be 21.51, instead it is 21.510000000000002.

    Is there a quick way to correct these errors, or am I stuck manually correcting each cell? The datatype for the column is nvarchar(50).

    Thanks.

  • August 21, 2009 at 7:09 am

    #1042795

    Once the data is in the table I would change the columns data type to decimal provided the data in the column is decimal format:

    -- Check column in table contains only numeric values

    SELECT [Column] FROM [Table] WHERE ISNUMERIC([Column]) = 0

    -- If any records were returned above this wont work...

    ALTER TABLE [Table]

    ALTER COLUMN [Column] DECIMAL(10,2)

    Dave

  • August 21, 2009 at 7:13 am

    #1042800

    I was about to ask what the reason is for storing numbers as nvarchar in the first place. Makes it rather awkward using them in calculations, don't you think?

    Anyhow, if you insist doing it that way, consider this:

    DECLARE @MyTable TABLE

    (

    MyColumn nvarchar ( 50 )

    )

    INSERT INTO @MyTable ( MyColumn )

    SELECT '21.510000000000002' UNION ALL

    SELECT '999'

    SELECT * FROM @MyTable

    UPDATE @MyTable

    SET MyColumn = LEFT ( MyColumn, CHARINDEX ( '.', MyColumn ) + 2 )

    WHERE CHARINDEX ( '.', MyColumn ) > 0

    SELECT * FROM @MyTable

    Note the WHERE clause. If you leave that out, then numbers with more than two digits and no decimal point would be truncated to the leftmost two digits.

    Regards,

    Jan

    --------------------------------------------------------------------------
    A little knowledge is a dangerous thing (Alexander Pope)
    In order for us to help you as efficiently as possible, please read this before posting (courtesy of Jeff Moden)[/url]

  • August 21, 2009 at 7:19 am

    #1042808

    I didn't design the database. 😀

    Thanks for the answers, though. 🙂

Viewing 4 posts - 1 through 3 (of 3 total)

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