Bug in MSSQL Function: IsNumeric

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

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.

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.

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

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

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