Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

SQL IsNumeric not working

Tags:

sql

sql-server

The reserve column is a varchar, to perform sums on it I want to cast it to a deciaml. But the SQL below gives me an error

select
cast(Reserve as decimal)
from MyReserves

Error converting data type varchar to numeric.

I added the isnumeric and not null to try and avoid this error but it still persists, any ideas why?

select
cast(Reserve as decimal)
from MyReserves
where isnumeric(Reserve ) = 1
and MyReserves is not null
like image 304
Hoody Avatar asked Sep 27 '11 10:09

Hoody


2 Answers

See here: CAST and IsNumeric

Try this:

WHERE IsNumeric(Reserve + '.0e0') = 1 AND reserve IS NOT NULL

UPDATE

Default of decimal is (18,0), so

declare @i nvarchar(100)='12121212121211212122121'--length is>18 
SELECT ISNUMERIC(@i) --gives 1
SELECT CAST(@i as decimal)--throws an error
like image 153
Dalex Avatar answered Oct 14 '22 06:10

Dalex


Gosh, nobody seems to have explained this correctly. SQL is a descriptive language. It does not specify the order of operations.

The problem that you are (well, were) having is that the where does not do the filtering before the conversion takes place. Order of operations, though, is guaranteed for a case statement. So, the following will work:

select cast(case when isnumeric(Reserve) = 1 then Reserve end as decimal)
from MyReserves
where isnumeric(Reserve ) = 1 and MyReserves is not null

The issue has nothing to do with the particular numeric format you are converting to or with the isnumeric() function. It is simply that the ordering of operations is not guaranteed.

like image 24
Gordon Linoff Avatar answered Oct 14 '22 08:10

Gordon Linoff