Menu
What is the best way to determine whether or not a field's value is an integer in SQL Server (2000/2005/2008)?
IsNumeric returns true for a variety of formats that would not likely convert to an integer. Examples include '15,000' and '15.1'.
You can use a like statement but that only appears to work well for fields that have a pre-determined number of digits...
select * where zipcode like '[0-9][0-9][0-9][0-9][0-9]' I could write a user defined function that attempts to convert a varchar parameter to an int within a try/catch block but I'm checking with the community to see if someone has come across any succient methods to achieve this goal - preferably one that can be used within the where clause of a SQL statement without creating other objects.
511
SQL Server ISNUMERIC() Function The ISNUMERIC() function tests whether an expression is numeric. This function returns 1 if the expression is numeric, otherwise it returns 0.
To check if the given value is a string or not ,we use the cast() function. If the value is not numeric then it returns 0, otherwise it will return the numeric value. In this way, we can check whether the value is an integer or not.
LIKE is a string operator and has nothing to do with integers. You can kludge this sort of stuff by casting your integers as strings.
What is the SQL Server ISNUMERIC function? As defined in the official Microsoft SQL Server documentation, the ISNUMERIC function determines whether an expression is a valid numeric type. It is a scalar function that takes a string expression as a parameter and returns an integer.
Late entry that handles negative
ISNUMERIC(zipcode + '.0e0') --integer ISNUMERIC(zipcode + 'e0') --decimal For more see this
150
1 approach is
zipcode NOT LIKE '%[^0-9]%' Double negatives, got to love 'em!
26
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
