T-sql - determine if value is integer

Question

I want to determine if a value is integer (like TryParse in .NET). Unfortunatelly ISNUMERIC does not fit me because I want to parse only integers and not every kind of number. Is there such thing as ISINT or something?

Here is some code to make things clear. If MY_FIELD is not int, this code would fail:

SELECT @MY_VAR = CAST(MY_FIELD AS INT)
FROM MY_TABLE
WHERE MY_OTHER_FIELD = 'MY_FILTER'

Thank you

Answer 1

WHERE IsNumeric(MY_FIELD) = 1 AND CAST(MY_FIELD as VARCHAR(5)) NOT LIKE '%.%'

That is probably the simplest solution. Unless your MY_FIELD contains .00 or something of that sort. In which case, cast it to a float to remove any trailing .00s

Answer 2

See whether the below query will help

SELECT *
FROM MY_TABLE
WHERE CHARINDEX('.',MY_FIELD) = 0 AND CHARINDEX(',',MY_FIELD) = 0       
AND ISNUMERIC(MY_FIELD) = 1 AND CONVERT(FLOAT,MY_FIELD) / 2147483647 <= 1

Answer 3

I am not a Pro in SQL but what about checking if it is devideable by 1 ? For me it does the job.

SELECT *
FROM table    
WHERE fieldname % 1 = 0

Answer 4

This work around with IsNumeric function will work:

select * from A where ISNUMERIC(x) =1 and X not like '%.%'

or Use

select * from A where x not like '%[^0-9]%'

Answer 5

I think that there is something wrong with your database design. I think it is a really bad idea to mix varchar and numbers in one column? What is the reason for that?

Of course you can check if there are any chars other than [0-9], but imagine you have a 1m rows in table and your are checking every row. I think it won't work well.

Anyway if you really want to do it I suggest doing it on the client side.

Answer 6

I have a feeling doing it this way is the work of satan, but as an alternative:

How about a TRY - CATCH?

DECLARE @Converted as INT
DECLARE @IsNumeric BIT

BEGIN TRY
    SET @Converted = cast(@ValueToCheck as int)
    SET @IsNumeric=1
END TRY
BEGIN CATCH
    SET @IsNumeric=0
END CATCH

select IIF(@IsNumeric=1,'Integer','Not integer') as IsInteger

This works, though only in SQL Server 2008 and up.