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

User · Answer

Why not just do something like   CASE WHEN ROUND MY FIELD 0  MY FIELD THEN CAST MY FIELD AS INT  ELSE MY FIELD END as MY FIELD2

User · Answer

This works fine in SQL Server  SELECT  SELECT ISNUMERIC 2  WHERE ISNUMERIC 2  1 AND  2 NOT LIKE

User · Answer

In his article Can I convert this string to an integer   Itzik Ben-Gan provides a solution in pure T-SQL and another that uses the CLR   Which solution should you choose      Is the T-SQL or CLR Solution Better   The advantage of using the T-SQL   solution is that you don   t need to go outside the domain of T-SQL   programming  However  the CLR solution has two important advantages    It s simpler and faster  When I tested both solutions against a table   that had 1 000 000 rows  the CLR solution took two seconds  rather   than seven seconds  for the T-SQL solution   to run on my laptop  So   the next time you need to check whether a given string can be   converted to an integer  you can include the T-SQL or CLR solution   that I provided in this article    If you only want to maintain T-SQL  then use the pure T-SQL solution  If performance is more important than convenience  then use the CLR solution   The pure T-SQL Solution is tricky  It combines the built-in ISNUMERIC function with pattern-matching and casting to check if the string represents an int   SELECT keycol  string  ISNUMERIC string  AS is numeric    CASE     WHEN ISNUMERIC string    0     THEN 0     WHEN string LIKE     -  0-9    THEN 0     WHEN CAST string AS NUMERIC 38  0         NOT BETWEEN -2147483648  AND 2147483647  THEN 0     ELSE 1   END AS is int FROM dbo T1    The T-SQL part of the CLR solution is simpler  You call the fn IsInt function just like you would call ISNUMERIC   SELECT keycol  string  ISNUMERIC string  AS is numeric    dbo fn IsInt string  AS is int FROM dbo T1    The C  part is simply a wrapper for the  NET s parsing function Int32 TryParse  This works because the SQL Server int and the  NET Int32 are both 32-bit signed integers   using System  using System Data SqlTypes   public partial class UserDefinedFunctions        Microsoft SqlServer Server SqlFunction      public static SqlBoolean fn IsInt SqlString s                if  s IsNull              return SqlBoolean False          else                       Int32 i   0              return Int32 TryParse s Value  out i                        Please read Itzik s article for a full explanation of these code samples

User · Answer

Necromancing  As of SQL-Server 2012   you can use TRY CAST  which returns NULL if the cast wasn t successful   Example   DECLARE  foo varchar 200  SET  foo    0123   -- SET  foo    -0123   -- SET  foo     0123   -- SET  foo     -0123   -- SET  foo     -0123   -- SET  foo     123   -- SET  foo    1 23   -- SET  foo        -- SET  foo         -- SET  foo    0123e10    SELECT CASE WHEN TRY CAST  foo AS integer  IS NULL AND  foo IS NOT NULL THEN 0 ELSE 1 END AS isInteger    This is the only really reliable way    Should you need support for SQL-Server 2008  then fall back to Sam DeHaan s answer    SELECT CASE WHEN ISNUMERIC  foo     e0     1 THEN 1 ELSE 0 END AS isInteger    SQL-Server  lt  2012  aka 2008R2  will reach end of  extended  support by 2019-07-09  At this time  which is very soon  support for  lt  2012 can be dropped   I wouldn t use any of the other hacks at this point in time anymore  Just tell your frugal customers to update - it s been over 10 years since 2008

User · Answer

The following is correct for a WHERE clause  to make a function wrap it in CASE WHEN    ISNUMERIC table field   gt  0 AND PATINDEX     0123456789     table field    0

User · Answer

Here s a blog post describing the creation of an IsInteger UDF   Basically  it recommends adding   e0  to the value and using IsNumeric  In this way  anything that already had a decimal point now has two decimal points  causing IsNumeric to be false  and anything already expressed in scientific notation is invalidated by the e0

User · Answer

Sometimes you don t get to design the database  you just have to work with what you are given  In my case it s a database located on a computer that I only have read access to which has been around since 2008    I need to select from a column in a poorly designed database which is a varchar with numbers 1-100 but sometimes a random string  I used the following to get around it  although I wish I could have re designed the entire database    SELECT A from TABLE where isnumeric A  1

User · Answer

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

User · Answer

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  lt   1

User · Answer

Use PATINDEX  DECLARE  input VARCHAR 10   102030 40  SELECT PATINDEX     0-9    RTRIM LTRIM  input    AS IsNumber   reference  http   www intellectsql com post-how-to-check-if-the-input-is-numeric

User · Answer

Had the same question  I finally used where ATTRIBUTE    round ATTRIBUTE   and it worked for me

User · Answer

I tried this script and got the answer ISNUMERIC Replace Replace  enter your number       A    -   A       0e0    for example for up question this is answer  SELECT  MY VAR   CAST MY FIELD AS INT  FROM MY TABLE WHERE MY OTHER FIELD    MY FILTER  and ISNUMERIC Replace Replace MY FIELD      A    -   A       0e0     1

User · Answer

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

User · Answer

Case    When  LNSEQNBR   16384  1   0 then 1    else 0 end

User · Answer

WHERE IsNumeric value    e0     1 AND CONVERT FLOAT  value  BETWEEN -2147483648 AND 2147483647

User · Answer

declare  i numeric 28 5    12 0001   if   i cast  i as int    1  begin     select  this is not int  end else begin     select  this is int  end

User · Answer

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

User · Answer

Use TRY CONVERT which is an SQL alternative to TryParse in  NET  IsNumeric   isn   t aware that empty strings are counted as  integer zero  and that some perfectly valid money symbols  by themselves  are not converted to  money zero  reference  SELECT  MY VAR   CASE WHEN TRY CONVERT INT MY FIELD  IS NOT NULL THEN MY FIELD                  ELSE 0                  END FROM MY TABLE WHERE MY OTHER FIELD    MY FILTER

User · Answer

As of SQL Server 2012  the TRY CONVERT and TRY CAST functions were implemented  Thise are vast improvements over the ISNUMERIC solution  which can  and does  give false positives  or negatives   For example if you run the below   SELECT CONVERT int V S  FROM  VALUES  1                  900                  hello                  12b                  1 1                      V S  WHERE ISNUMERIC V S    1    Using TRY CONVERT  or TRY CAST  avoids that   SELECT TRY CONVERT int V S          V S         ISNUMERIC V S  FROM  VALUES  1                  900                  hello                  12b                  1 1                      V S  --WHERE TRY CONVERT int V S  IS NOT NULL  --To filter to only convertable values   Notice that  1 1  returned NULL  which cause the error before  as a string represtation of a decimal cannot be converted to an int  but also that    returned 0  even though ISNUMERIC states the value  can t be converted

User · Answer

With sqlserver 2005 and later you can use regex-like character classes with LIKE operator  See here   To check if a string is a non-negative integer  it is a sequence of decimal digits  you can test that it doesn t contain other characters   SELECT numstr   FROM table  WHERE numstr NOT LIKE     0-9      Note1  This will return empty strings too   Note2  Using LIKE    0-9    will return any string that contains at least a digit   See fiddle

User · Answer

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

User · Answer

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

[sql-server] T-sql - determine if value is integer

Examples related to sql-server

Examples related to tsql

Examples related to casting

Examples related to integer

Examples related to isnumeric