Problem with CAST(textValue AS FLOAT) in SQL Server 2012

[f***@gmail.com]

This query works fine in SQL Server 2008 but fails in 2012
SELECT TestID FROM ParameterEntries WHERE ParameterID=239 AND CAST(TextValue AS FLOAT) = 123

in 2012:
Error converting data type varchar to float.

[f***@gmail.com]

Could this because there are non-numeric values in the column TextValue?

I found 2 null values and entries with multiple minus signs '----' and decimal points '.....' and '-00000' and removed those records.

The form in this case prevent the user from entering any non-numeric characters.

NOTE:Other forms allow alpha characters into the same column with different ParameterID values, that is why the column is not declared as a numeric type.

SELECT * FROM ParameterEntries
WHERE ParameterID = 239
ORDER BY TextValue

Returns > 127,000 records, so it is difficult to tell if any non-numeric characters are mixed in, but the 1st char appears to be numeric by scrolling through all start with 0-9.

[f***@gmail.com]

Found a record that had a TextValue of '-------252' that caused the problem.
Not sure why I didn't see it when I ordered the records by TextValue and deleted the '--' '----' and other records that started with a minus sign.

[Erland Sommarskog]

Post by f***@gmail.com
This query works fine in SQL Server 2008 but fails in 2012
SELECT TestID
FROM ParameterEntries
WHERE ParameterID=239 AND CAST(TextValue AS FLOAT) = 123
Error converting data type varchar to float.

A query like this one is like a loaded gun. Even there are no non-numeric
values with ParameterID 239, there is no guarantee that SQL Server does
not attempt the conversion for all rows.

This used to be tricky to sort out, but thankfully, it has become a lot
simpler in SQL 2012:

SELECT TestID
FROM ParameterEntries
WHERE ParameterID=239 AND TRY_CAST(TextValue AS FLOAT) = 123

TRY_CAST returns NULL when then values does not convert.