CODEWITHSUNDEEP
sqlsql-server
Hoody
Hoody

Reputation: 3381

SQL IsNumeric not working

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

Upvotes: 9

Views: 33451

Answers (11)

Singaravelan
Singaravelan

Reputation: 839

I am also facing this issue and I solved by below method. I am sharing this because it may helpful to some one.

declare @g varchar (50)
 set @g=char(10)
 select isnumeric(@g),@g, isnumeric(replace(replace(@g,char(13),char(10)),char(10),''))

Upvotes: 0

Salazaar
Salazaar

Reputation: 384

It seems that isnumeric has some Problems:

http://www.sqlhacks.com/Retrieve/Isnumeric-problems (via internet archive)

According to that Link you can solve it like that:

select
cast(Reserve as decimal)
from MyReserves
where MyReserves is not null
and MyReserves * 1 = MyReserves

Upvotes: 4

Derek Tomes
Derek Tomes

Reputation: 4007

IsNumeric is a problem child -- SQL 2012 and later has TRY_CAST and TRY_CONVERT

If you're on an earlier version then you can write a function that'll convert to a decimal (or NULL if it won't convert). This uses the XML conversion functions that don't throw errors when the number won't fit ;)

-- Create function to convert a varchar to a decimal (returns null if it fails)
IF EXISTS( SELECT * FROM sys.objects WHERE object_id = OBJECT_ID( N'[dbo].[ToDecimal]' ) AND type IN( N'FN',N'IF',N'TF',N'FS',N'FT' ))
    DROP FUNCTION [dbo].[ToDecimal];
GO

CREATE FUNCTION ToDecimal
(   
    @Value VARCHAR(MAX)
)
RETURNS DECIMAL(18,8)
AS
BEGIN
    -- Uses XML/XPath to convert @Value to Decimal because it returns NULL it doesn't cast correctly
    DECLARE @ValueAsXml XML
    SELECT @ValueAsXml = Col FROM (SELECT (SELECT @Value as Value FOR XMl RAW, ELEMENTS) AS Col) AS test
    DECLARE @Result DECIMAL(38,10)
    -- XML/XPath will return NULL if the VARCHAR can't be converted to a DECIMAL(38,10)
    SET @Result =  @ValueAsXml.value('(/row/Value)[1] cast as xs:decimal?', 'DECIMAL(38,10)')
    RETURN CASE -- Check if the number is within the range for a DECIMAL(18,8)
        WHEN @Result >= -999999999999999999.99999999 AND @Result <= 999999999999999999.99999999
            THEN CONVERT(DECIMAL(18,8),@Result) 
        ELSE 
            NULL
    END
END

Then just change your query to:

select dbo.ToDecimal(Reserve) from MyReserves

Upvotes: 2

renegm
renegm

Reputation: 620

Use try_cast (sql 2012) 

select
try_cast(Reserve as decimal)
from MyReserves

Upvotes: 5

Matthew Chapman
Matthew Chapman

Reputation: 11

I had this same problem and it turned out to be scientific notation such as '1.72918E-13' To find this just do where Reserve LIKE '%E%'. Try bypassing these and see if it works. You'll have to write code to convert these to something usable or reformat your source file so it doesn't store any numbers using scientific notation.

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269763

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.

Upvotes: 8

Sean Gahan
Sean Gahan

Reputation: 11

Just a heads up on isnumeric; if the string contains some numbers and an 'E' followed by some numbers, this is viewed as an exponent. Example, select isnumeric('123E0') returns 1.

Upvotes: 1

Dalex
Dalex

Reputation: 3625

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

Upvotes: 10

Larry Lustig
Larry Lustig

Reputation: 50970

Try (for example):

select
cast(Reserve as decimal(10,2))
from MyReserves

Numeric/Decimal generally want a precision an scale.

Upvotes: 0

Barry Kaye
Barry Kaye

Reputation: 7761

IsNumeric is possibly not ideal in your scenario as from the highlighted Note on this MSDN page it says "ISNUMERIC returns 1 for some characters that are not numbers, such as plus (+), minus (-), and valid currency symbols such as the dollar sign ($)."

Also there is a nice article here which further discusses ISNUMERIC.

Upvotes: 0

ipr101
ipr101

Reputation: 24236

isnumeric is not 100% reliable in SQL - see this question Why does ISNUMERIC('.') return 1?

I would guess that you have value in the reserve column that passes the isnumeric test but will not cast to decimal.

Upvotes: 1

Related Questions