x

Bug in MSSQL Function: IsNumeric

Has anyone had this problem before? If you use the function IsNumeric in T-SQL it returns the following:

SELECT ISNUMERIC('1d8') -- 1

SELECT ISNUMERIC('1D8') -- 1

SELECT ISNUMERIC('1d') -- 0

SELECT ISNUMERIC('1D') -- 0

I would expect that 1d8 and 1D8 wouldn't be numeric.

I'm using SQL Server 2005.

more ▼

asked Oct 27, 2009 at 06:49 AM in Default

avatar image

Henk
55 1 1 3

(comments are locked)
10|1200 characters needed characters left

8 answers: sort voted first

Melvyn is right, they are numeric but the odd thing is that you cant convert them to integers. You need to convert them to Float because D denotes to float double precision (inherited from Fortran?).

Try:

SELECT CONVERT(int,'1d8')

If you would like to know if the string is possible to convert to an integer you can use a function I found somewhere a while ago. I can't remember where it came from (maybe from www.aspfaq.com ), and I don't want to take credit for it.

CREATE FUNCTION dbo.isReallyInteger ( @num VARCHAR(64) ) RETURNS BIT BEGIN IF LEFT(@num, 1) = '-' SET @num = SUBSTRING(@num, 2, LEN(@num))

 RETURN CASE  
 WHEN PATINDEX('%[^0-9-]%', @num) = 0  
     --AND CHARINDEX('-', @num) <= 1  
     AND @num NOT IN ('.', '-', '+', '^') 
     AND LEN(@num)>0  
     AND @num NOT LIKE '%-%' 
 THEN  
     1  
 ELSE  
     0  
 END  

END
GO

Try the function with something like this:

DECLARE @num AS VARCHAR(8)='1d8'
SELECT
    DBO.isReallyInteger(@num) 
    ,ISNUMERIC(@num), 
    CASE 
        WHEN DBO.isReallyInteger(@num) =1 THEN CONVERT(int,@num) 
    END
more ▼

answered Oct 27, 2009 at 07:29 AM

avatar image

Håkan Winther
16.5k 37 45 57

(comments are locked)
10|1200 characters needed characters left

ISNUMERIC returns true if the value can be converted to a numeric data type. This includes real, float, money. For that reason non-number characters like '+', '-', 'd', 'e', '$','£' are allowed.

more ▼

answered Oct 27, 2009 at 07:37 AM

avatar image

Kev Riley ♦♦
64.2k 48 62 81

(comments are locked)
10|1200 characters needed characters left

I know an answer has already been accepted, but I just came across this and thought I'd suggest a different solution using CLR. One of the great things about CLR is that it allows you to do things in functions (like error-handling) that standard UDFs don't allow. You can add more data type validation tests (like custom date format tests) by adding other similar public static methods to the class.

C# class:

using System; using System.Collections.Generic; using System.Text;

namespace SqlFunctions { public class DataTypeTests { public static int IsInteger(string TestValue) { int IntTest;

         if (System.Int32.TryParse(TestValue, out IntTest))
             return 1;
         else
             return 0;
     }
 }

}

SQL Server create assembly:

CREATE ASSEMBLY SqlFunctions 
FROM 'C:\SqlFunctions\bin\SqlFunctions.dll'
WITH PERMISSION_SET = SAFE;
GO

SQL Server create function:

CREATE FUNCTION dbo.IsInteger (@TestValue nvarchar(50))
RETURNS int
AS EXTERNAL NAME SqlFunctions.[SqlFunctions.DataTypeTests].IsInteger
GO

SQL Server Usage:

SELECT dbo.IsInteger('3434d') -- returns 0

performance results (using SET STATISTICS TIME ON):

I just ran a test on 100,000 rows of varchar(10) fields from our database that usually store numbers (old data was not always numeric). 26 of the 100,000 tested were not numbers. I compared the execution time for my CLR solution vs. IsReallyInt (accepted answer) vs. IsNum (another answer) vs. the built-in function ISNUMERIC. They all returned the same 26 rows, but they did it in approximately 1, 3, 4 and .5 seconds, respectively.

(26 row(s) affected)

(1 row(s) affected)

SQL Server Execution Times: CPU time = 1078 ms, elapsed time = 1133 ms.

(26 row(s) affected)

(1 row(s) affected)

SQL Server Execution Times: CPU time = 3062 ms, elapsed time = 3106 ms.

(26 row(s) affected)

(1 row(s) affected)

SQL Server Execution Times: CPU time = 4032 ms, elapsed time = 4292 ms.

(26 row(s) affected)

(1 row(s) affected)

SQL Server Execution Times: CPU time = 516 ms, elapsed time = 526 ms.

more ▼

answered Nov 05, 2009 at 04:45 PM

avatar image

Tom Staab ♦
14.5k 7 14 19

(comments are locked)
10|1200 characters needed characters left

Technically, they are numeric, and it's working correctly:

SELECT CONVERT(FLOAT, '1d8') -- 100000000 (1 and 8 zeros)
SELECT CONVERT(FLOAT, '1D8') -- 100000000
more ▼

answered Oct 27, 2009 at 06:57 AM

avatar image

Melvyn Harbour 1 ♦♦
1.4k 19 40 26

(comments are locked)
10|1200 characters needed characters left

Why are you checking for both

AND CHARINDEX('-', @num) <= 1
AND @num NOT LIKE '%-%'

You have already truncated the data if first character is "-".
This is the danger of copying the code without reading it.

Try with this data too... declare @num varchar(10) = '²³'

Use this function for proper results

CREATE FUNCTION dbo.IsNum
(
    @Num VARCHAR(64)
)
RETURNS BIT
AS
BEGIN
    RETURN    CASE
            WHEN @Num COLLATE Latin1_General_BIN LIKE REPLICATE('[0-9]', DATALENGTH(@Num)) THEN 1
            WHEN @Num COLLATE Latin1_General_BIN LIKE '-' + REPLICATE('[0-9]', DATALENGTH(@Num) - 1) THEN 1
            ELSE 0
        END
END
more ▼

answered Oct 27, 2009 at 10:15 AM

avatar image

Peso
1.6k 5 6 9

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x2017
x68

asked: Oct 27, 2009 at 06:49 AM

Seen: 6813 times

Last Updated: Oct 27, 2009 at 06:53 AM

Copyright 2016 Redgate Software. Privacy Policy