Why is casting failing when I am using ISNUMERIC()?

user_11353249 0
Viewed 112
SELECT CAST([Revenue Size (M)] AS float)
FROM Raw_Data A
WHERE ISNUMERIC([Revenue Size (M)]) = 1

I get an error:

Error converting data type nvarchar to float

Revenue Size (M) Samples: 7371 ,7372 ,4899 ,59.5 ,1.4 ,2 ,3400

casting tsql sql-server sql
2 Answers

There are multiple examples where isnumeric() returns 1 but the value cannot be converted to a floating point number. Four examples are:

  • '.'
  • '-'
  • '-.'
  • '$1'

You can see this in this SQL Fiddle.

The better course of action is to use try_cast():

SELECT TRY_CAST([Revenue Size (M)] AS float)
FROM Raw_Data A
WHERE TRY_CAST([Revenue Size (M)] AS float) IS NOT NULL;
user_11440350

Gordon is correct +1

However, I tend to use MONEY when reasonable ... It seems to be a little more forgiving.

Example

Select AsMoney = try_convert(money,'$5,125')
      ,AsFloat = try_convert(float,'$5,125')
      ,AsInt   = try_convert(int,'$5,125')
      ,AsDec   = try_convert(decimal(10,2),'$5,125')

Results

AsMoney     AsFloat     AsInt       AsDec
5125.00     NULL        NULL        NULL
user_15700000
Related