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 '09 at 06:49 AM in Default

Henk gravatar image

Henk
55 1 1 1

(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 '09 at 07:29 AM

Håkan Winther gravatar image

Håkan Winther
15.5k 33 37 48

Watch out for the scalar-function problem though.
Oct 27 '09 at 08:28 AM Rob Farley
I voted this down because of problems with the implementation. See my reply below.
Oct 27 '09 at 10:17 AM Peso
(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 '09 at 07:37 AM

Kev Riley gravatar image

Kev Riley ♦♦
50.7k 43 49 76

(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 '09 at 04:45 PM

Tom Staab gravatar image

Tom Staab
5.8k 6 8 10

What's the performance like on that?
Nov 06 '09 at 08:23 AM Melvyn Harbour 1 ♦♦
edited answer to include performance results
Nov 06 '09 at 09:14 AM Tom Staab
Nice work! +1...
Nov 06 '09 at 09:57 AM Matt Whitfield ♦♦
Top notch stuff. Interesting stuff to read, particularly when it nicely exposes the fact that using CLR functions isn't always slower!
Nov 06 '09 at 10:40 AM Melvyn Harbour 1 ♦♦
(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 '09 at 06:57 AM

Melvyn Harbour 1 gravatar image

Melvyn Harbour 1 ♦♦
1.4k 18 20 22

(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 '09 at 10:15 AM

Peso gravatar image

Peso
1.6k 5 6 8

I agree, there is a danger with copying the code without reading and testing. Actually I posted the original code and haven't used it myself. :)
Oct 27 '09 at 10:40 AM Håkan Winther
dbo.IsNum('1.234') Fails
Dec 23 '09 at 02:37 PM dvroman
1.234 isn't an integer, so the function returns false (0) and it does.
Dec 27 '09 at 01:24 PM Peso
Is it 1 point 234 or 1 thousand 234?
Dec 27 '09 at 01:25 PM Peso
(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.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

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:

x1933
x55

asked: Oct 27 '09 at 06:49 AM

Seen: 5565 times

Last Updated: Oct 27 '09 at 06:53 AM