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.

To be honest, the hack here is that you have data in a text column that you want to treat as numbers. You really should not do that, but I guess you've already discovered why. — Lasse V. Karlsen, Sep 04 '09 at 07:21

score 2 · Answer 1 · answered Sep 04 '09 at 05:10

2

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.

answered Sep 04 '09 at 05:10

Steve Kass

7,144
20
26

Not sure if this is right or not, but won't that only test the first character for 'not being a digit'? Shouldn't you use: WHERE column_value not like '%[^0-9]%'? – VoidKing Dec 04 '12 at 16:39
OR column_value like '%[0-9]%'? – VoidKing Dec 04 '12 at 16:46

Svetlozar Angelov · Answer 2 · 2009-09-04T07:19:55.293

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.

It detects '123.' as Integer but we cannot convert it :( – Faiz Apr 21 '11 at 11:45 — Faiz, Apr 21 '11 at 11:45

MikeTeeVee · Answer 3 · 2012-05-18T04:07:21.297

This should handle all cases without throwing any exceptions:

--This handles dollar-signs, commas, decimal-points, and values too big or small,
--  all while safely returning an int.
DECLARE @IntString as VarChar(50) = '$1,000.'
SELECT CAST((CASE WHEN --This IsNumeric check here does most of the heavy lifting.  The rest is Integer-Specific
                       ISNUMERIC(@IntString) = 1
                       --Only allow Int-related characters.  This will exclude things like 'e' and other foreign currency characters.
                   AND @IntString NOT LIKE '%[^ $,.\-+0-9]%' ESCAPE '\'--'
                       --Checks that the value is not out of bounds for an Integer.
                   AND CAST(REPLACE(REPLACE(@IntString,'$',''),',','') as Decimal(38)) BETWEEN -2147483648 AND 2147483647
                       --This allows values with decimal-points for count as an Int, so long as there it is not a fractional value.
                   AND CAST(REPLACE(REPLACE(@IntString,'$',''),',','') as Decimal(38)) = CAST(REPLACE(REPLACE(@IntString,'$',''),',','') as Decimal(38,2))
                       --This will safely convert values with decimal points to casting later as an Int.
                  THEN CAST(REPLACE(REPLACE(@IntString,'$',''),',','') as Decimal(10))
             END) as Int)[Integer]

Throw this into a Scalar UDF and call it ReturnInt().
If the value comes back as NULL, then it's not an int (so there's your IsInteger() requirement)

If you don't like typing "WHERE ReturnInt(SomeValue) IS NOT NULL", you could throw it into another scalar UDF called IsInt() to call this function and simply return "ReturnInt(SomeValue) IS NOT NULL".

The cool thing is, the UDF can serve double duty by returning the "safely" converted int value.
Just because something can be an int doesn't mean casting it as an int won't throw a huge exception. This takes care of that for you.

Also, I'd avoid the other solutions because this universal approach will handle commas, decimals, dollar signs, and checks the acceptable Int value's range while the other solutions do not - or they require multiple SET operations that prevent you from using the logic in a Scalar-Function for maximum performance.

See the examples below and test them against my code and others:

--Proves that appending "e0" or ".0e0" is NOT a good idea.
select ISNUMERIC('$1' + 'e0')--Returns: 0.
select ISNUMERIC('1,000' + 'e0')--Returns: 0.
select ISNUMERIC('1.0' + '.0e0')--Returns: 0.

--While these are numeric, they WILL break your code
--   if you try to cast them directly as int.
select ISNUMERIC('1,000')--Returns: 1.
select CAST('1,000' as Int)--Will throw exception.
select ISNUMERIC('$1')--Returns: 1.
select CAST('$1' as Int)--Will throw exception.
select ISNUMERIC('10.0')--Returns: 1.
select CAST('10.0' as Int)--Will throw exception.
select ISNUMERIC('9999999999223372036854775807')--Returns: 1.  This is why I use Decimal(38) as Decimal defaults to Decimal(18).
select CAST('9999999999223372036854775807' as Int)--Will throw exception.

Update:
I read a comment here that you want to be able to parse a value like '123.' into an Integer. I have updated my code to handle this as well.

Note: This converts "1.0", but returns null on "1.9".
If you want to allow for rounding, then tweak the logic in the "THEN" clause to add Round() like so:
ROUND(CAST(REPLACE(REPLACE(@IntString,'$',''),',','') as Decimal(10)), 0)
You must also remove the "AND" that checks for "decimal-points" to allow for Rounding or Truncation.

score 0 · Answer 4 · answered Jun 04 '12 at 23:38

Why not use the following and test for 1?

DECLARE @TestValue nvarchar(MAX)
SET @TestValue = '1.04343234e5'

SELECT CASE WHEN ISNUMERIC(@TestValue) = 1
        THEN CASE WHEN ROUND(@TestValue,0,1) = @TestValue
            THEN 1
            ELSE 0
            END
        ELSE null
        END AS Analysis

John M Gant · Answer 5 · 2009-09-04T13:50:19.147

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

score 0 · Answer 6 · answered Feb 17 '14 at 21:34

If you are purely looking to verify a string is all digits and not just CAST-able to INT you can do this terrible, terrible thing:

select LEN(
 REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE(
 '-1.223344556677889900e-1'
 ,'0','') ,'1','') ,'2','') ,'3','') ,'4','') ,'5','') ,'6','') ,'7','') ,'8','') ,'9','')
)

It returns 0 when the string was empty or pure digits.

To make it a useful check for "poor-man's" Integer you'd have to deal with empty string, and an initial negative sign. And manually make sure it isn't too long for your variety of INTEGER.

SQL Server, where field is int?

6 Answers6

Linked