My problem: I have a table (imported from Excel to SQL Server 2008) which stores numbers in NVARCHAR(255) format.
Trying to convert these columns to FLOAT, MONEY, NUMERIC failed with CONVERT or with CAST functions:
ORIGBAL = CAST(ORIGBAL AS FLOAT)
ORIGBAL = CAST(ORIGBAL AS MONEY)
ORIGBAL = CAST(ORIGBAL AS MONEY)
ORIGBAL = CONVERT(decimal(12,2),ORIGBAL)
The message is in all cases (or similar fail error message):
Error converting data type nvarchar to numeric.
Sounds like, because you have numeric data stored in a string column, you have bad data. One way to identify it is:
SELECT key_column, ORIGBAL
FROM dbo.tablename
WHERE ORIGBAL LIKE '%[^-.0-9]%';
Fix that data (it may contain commas, but probably the damage is worse than that, like some values with completely non-numeric data - otherwise cast to MONEY should have worked). If you find that a lot of rows contain commas then you can simply run an update:
UPDATE dbo.tablename SET ORIGBAL = REPLACE(ORIGBAL, ',', '');
Then run the above query again. If no rows are returned, now you can convert to "normal" numeric types instead of MONEY or FLOAT which really should be reserved for very specific scenarios IMHO.
Without fixing the data, you can make your life more complicated by using a CASE expression in all of your queries:
SELECT key_column, CONVERT(DECIMAL(12,2), CASE
WHEN ORIGBAL LIKE '%[^-.0-9]%' THEN ORIGBAL END
FROM dbo.tablename
...
This will yield NULL values instead of bad, non-numeric values. However it could still fail, e.g. that expression will pass for 0.0.0.44.02 which obviously can't be converted to a decimal. As an example:
DECLARE @x TABLE(ORIGBAL NVARCHAR(255));
INSERT @x VALUES('bob'),('0.0.0.44.02'),('32500.40');
SELECT ORIGBAL, [status] = CASE WHEN ORIGBAL LIKE '%[^.0-9]%'
THEN 'needs correcting' ELSE 'all good' END
FROM @x;
Results:
bob needs correcting
0.0.0.44.02 all good
32500.40 all good
To identify cases with multiple decimal points, you could do:
SELECT ORIGBAL, [status] = CASE WHEN ORIGBAL LIKE '%[^.0-9]%'
OR LEN(ORIGBAL)-LEN(REPLACE(ORIGBAL,'.','')) > 1
THEN 'needs correcting' ELSE 'all good' END
FROM @x;
Results:
bob needs correcting
0.0.0.44.02 needs correcting
32500.40 all good
You still may have values that exceed the eventual convert, e.g. '12345678901234.56' is too big for DECIMAL(12,2).
In SQL Server 2012, you will have shorthand for the above expression:
SELECT key_column, TRY_CONVERT(DECIMAL(12,2), ORIGBAL)
FROM dbo.tablename
...
...which will yield NULL values even for more complex values like 0.0.0.44.02.
CAST doesn't react well to commas and dollar signs. You might have better luck with CONVERT...
SELECT CONVERT(money, '$1,123.45')
If you have to have it in decimal, convert to money and then decimal...
SELECT CAST(CONVERT(money, '$1,123.45') AS decimal(12,2))
