Geek Answers Handbook

SQL IsNumeric not working

The reserve column is varchar, to run the amounts on it I want to pass it to deciaml. But the SQL below gives me an error

select
cast(Reserve as decimal)
from MyReserves

Error converting varchar to numeric data type.

I added isnumeric and not null to try to 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
+5
source share
10 answers

It seems that isnumeric has some problems:

http://www.sqlhacks.com/Retrieve/Isnumeric-problems (link prohibited as of 04/30/2014)

According to this link you can solve it like this:

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

See here: CAST and IsNumeric

Try the following:

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

UPDATE

(18,0),

declare @i nvarchar(100)='12121212121211212122121'--length is>18 
SELECT ISNUMERIC(@i) --gives 1
SELECT CAST(@i as decimal)--throws an error
+8

, , . SQL - . .

, (, ) , where , . . , :

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

, isnumeric(). .

+3

try_cast (sql 2012)

select
try_cast(Reserve as decimal)
from MyReserves
+2

isnumeric 100% SQL - . ISNUMERIC ('.') 1?

, , isnumeric, .

+1

isnumeric; "E", , . , select isnumeric ('123E0') 1.

+1

, , "1.72918E-13". , , Reserve LIKE "% E%". , . , , .

+1

IsNumeric, , , MSDN, : "ISNUMERIC 1 , (+), (-) , ($)."

, IsNumeric.

0

():

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

/ .

0

IsNumeric child - SQL 2012 TRY_CAST TRY_CONVERT

, , ( NULL, ). XML, , ;)

-- 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

:

select dbo.ToDecimal(Reserve) from MyReserves
0

More articles:


All Articles