SQL Server, where field is int?

Question

how can I accomplish:

select * from table where column_value is int

I know I can probably inner join to the system tables and type tables but I'm wondering if there's a more elegant way.

Note that column_value is a varchar that "could" have an int, but not necessarily.

Maybe I can just cast it and trap the error? But again, that seems like a hack.

Answer 1

select * from table
where column_value not like '[^0-9]'

If negative ints are allowed, you need something like

where column_value like '[+-]%' 
and substring(column_value,patindex('[+-]',substring(column_value,1))+1,len(column_value))
not like '[^0-9]'

You need more code if column_value can be an integer that exceeds the limits of the "int" type, and you want to exclude such cases.

Answer 2

Here if you want to implement your custom function

CREATE Function dbo.IsInteger(@Value VARCHAR(18))
RETURNS BIT
AS 
BEGIN    
     RETURN ISNULL(     
         (SELECT    CASE WHEN CHARINDEX('.', @Value) > 0 THEN 
                            CASE WHEN CONVERT(int, PARSENAME(@Value, 1)) <> 0  THEN 0  ELSE 1 END  
                    ELSE 1 
                    END      
          WHERE     ISNUMERIC(@Value + 'e0') = 1), 0)

END

ISNUMERIC returns 1 when the input expression evaluates to a valid integer, floating point number, money or decimal type; otherwise it returns 0. A return value of 1 guarantees that expression can be converted to one of these numeric types.

Answer 3

I would do a UDF as Svetlozar Angelov suggests, but I would check for ISNUMERIC first (and return 0 if not), and then check for column_value % 1 = 0 to see if it's an integer.

Here's what the body might look like. You have to put the modulo logic in a separate branch because it will throw an exception if the value isn't numeric.

DECLARE @RV BIT
IF ISNUMERIC(@value) BEGIN
    IF CAST(@value AS NUMERIC) % 1 = 0 SET @RV = 1
    ELSE SET @RV = 0
END
ELSE SET @RV = 0
RETURN @RV