SQL Error converting data type nvarchar to float

0
votes

I am getting an error

Error converting data type nvarchar to float

in this part of my stored procedure:

 SELECT 
     CAST(RTRIM(Value) AS FLOAT)     
 FROM 
     table_1
 WHERE 
     ISNUMERIC(RTRIM(Value)) = 1 
     AND RTRIM(Value) NOT LIKE '%[^0-9.]%'

I tried to track down the value that would have caused this but was unable to do so and now the procedure runs successfully so I don't have the ability to track down the problematic value format.

If someone can suggest a way I would like to know what value could have pass through the 2 checks we have.

2
sqlsql-serversql-server-2008
What version of SQL Server are you working with? - Zohar Peled
Instead of float cast decimal(18,4) it supports better numbers like "18.4". - xCloudx8
Your logic looks airtight to me. Are you sure that it wasn't working completely perhaps after you had removed all problematic data? - Tim Biegeleisen
SELECT case when Value <> '' then cast(cast(value as nvarchar(50)) as float) ELSE 0.0 to find problem value - user3127554
For starters, ISNUMERIC('-') = 1, but CAST('-' AS FLOAT) is an error. Then there's the fact that WHERE doesn't always protect you, although that should not be in effect for a trivial SELECT like this. TRY_CAST and TRY_CONVERT are your friends, if you have them; CASE often bails you out as well (but is unfortunately still not foolproof in the face of some aggressive optimizations). - Jeroen Mostert

2 Answers

0
votes

Use TRY_CONVERT() instead 

SELECT CONVERT(DECIMAL(10, 0), RTRIM(Value)) as Col
FROM table_1
WHERE TRY_CONVERT((DECIMAL(10, 0), Value) IS NOT NULL;
--WHERE ISNUMERIC(RTRIM(Value)) = 1 AND RTRIM(Value) NOT LIKE '%[^0-9.]%'
0
votes

If you had SQL Server 2012 or newer you could use TRY_CONVERT instead of ISNUMERIC

SELECT TRY_CONVERT(FLOAT, RTRIM(Value))
FROM table_1
WHERE RTRIM(Value) NOT LIKE '%[^0-9.]%'

In your case you should add additional conditions to the WHERE clause (I was able to come up with two):

SELECT 
     CAST(RTRIM(Value) AS FLOAT)     
 FROM 
     table_1
 WHERE 
     ISNUMERIC(RTRIM(Value)) = 1 
     AND RTRIM(Value) NOT LIKE '%[^0-9.]%'
     AND RTRIM(Value) != '.'
     AND RTRIM(Value) NOT LIKE '%.%.%'