16

Is there an easy way to get if string is an integer number (consists only of digits) in MS SQL 2005?

Thank you for your help.

StuffHappens
  • 6,457
  • 13
  • 70
  • 95

7 Answers7

20

The function ISNUMERIC returns whether a string is numeric, but will return true for non-integers.

So you could use:

WHERE ISNUMERIC(str) AND str NOT LIKE '%.%' AND str NOT LIKE '%e%' AND str NOT LIKE '%-%'
Paul
  • 16,285
  • 13
  • 41
  • 52
  • 4
    And what is with strings like `'£'` or `'0d0'`? `ISNUMERIC` will still return `1`. – Tim Schmelter Mar 13 '14 at 13:49
  • I think excluding character by character doesn't help as we can have multiple combinations. ex: special characters, alphabets etc. – Dev Apr 10 '17 at 10:47
  • And '$' in the US - also returns 1 (SQL 2022), even though cast('$' as decimal) gives error. – Chr. Aug 29 '23 at 20:04
19

Even though the original poster was referring to SQL 2005, I found in 2008 r2 a straight where isnumeric(string) resulted in an error 4145 non-boolean type. To resolve this use:where isnumeric(string) = 1

Craig
  • 191
  • 1
  • 2
9

You could use the LIKE operator:

WHERE str NOT LIKE '%[^0-9]%'
Paul
  • 16,285
  • 13
  • 41
  • 52
2

See this:

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
Anton Gogolev
  • 113,561
  • 39
  • 200
  • 288
1

It is a little tricky to guarantee that a value will conform to a 4 byte integer.

Since you are using 2005 - One way is to try to convert the value within a try/catch block. That would be the best way to insure that it is actually an int. Of course you need to handle the cases when it does not in the catch block according to your requirements.

Another way to just test for only "digits" is this:

where strVal not like '%[^0-9]%'

That will miss -25. as well as allow '99999999999999999999' So you may need to include additional criteria with this method.

TG.
  • 358
  • 1
  • 3
  • 6
0

Using (CAST(PATINDEX('%[^0-9]%', value) as BIT)) handles all the characters

Dale K
  • 25,246
  • 15
  • 42
  • 71
Dev
  • 1,451
  • 20
  • 30
-1

Standard T-SQL function ISNUMERIC ( expression ) Determines whether an expression is a valid numeric type.

Dale K
  • 25,246
  • 15
  • 42
  • 71
Dewfy
  • 23,277
  • 13
  • 73
  • 121
  • Valid numeric type indeed, though it doesn't care about whether integer, decimal, money or float. And the OP explicitly asked about integer. – Joey Jan 21 '10 at 13:49