How to get if a string is a number in T-SQL

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

Than开发者_运维技巧k you for your help.

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

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 '%-%'

You could use the LIKE operator:

WHERE str NOT LIKE '%[^0-9]%'

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

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.

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

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