The following code encounters an error when executed in Microsoft Server Management Studion:
USE [DST]
GO
Select
CAST([Balance] as float)
FROM [RAW_XXX]
WHERE ISNUMERIC(Balance) = 1
Msg 8114, Level 16, State 5, Line 2 Error converting data type varchar to float.
I thought that the ISNUMERIC would exclude anything that can not be cast or converted.
It is a massive database in SQLServer 2012 so I am unsure how to find the data that is causing the error.
Use TRY_CONVERT to flush out the offending records:
SELECT *
FROM [RAW_XXX]
WHERE TRY_CONVERT(FLOAT, Balance) IS NULL;
The issue with your current logic is that something like $123.45 would be true according to ISNUMERIC, but would fail when trying to cast as floating point.
By the way, if you wanted a more bare bones way of finding records not castable to float you could just rely on LIKE:
SELECT *
FROM [RAW_XXX]
WHERE Balance NOT LIKE '%[^0-9.]%' AND Balance NOT LIKE '%.%.%';
The first LIKE condition ensures that Balance consists only of numbers and decimal points, and the second condition ensures that at most one decimal point appears. Checkout the demo below to see this working.
$405.27 would pass ISNUMERIC but not the CAST
SELECT * FROM RAW_XXX WHERE ISNUMERIC(Balance) <>1to find the data causing your error?