CODEWITHSUNDEEP
sqlsql-servert-sqlcasting
Yash
Yash

Reputation: 369

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

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

Upvotes: 2

Views: 296

Answers (2)

John Cappelletti
John Cappelletti

Reputation: 81930

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

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269445

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;

Upvotes: 5

Related Questions