August 21, 2009 at 6:11 am
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
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
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
August 21, 2009 at 7:19 am
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