T-sql - determine if value is integer
Sql ServerTsqlCastingIntegerIsnumericSql Server Problem Overview
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
Sql Server Solutions
Solution 1 - Sql Server
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
.
Solution 2 - Sql Server
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.
Solution 3 - Sql Server
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
Solution 4 - Sql Server
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
Solution 5 - Sql Server
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 < 2012 (aka 2008R2) will reach end of (extended) support by 2019-07-09.
At this time, which is very soon, support for < 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.
Solution 6 - Sql Server
> 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 <= 1
Solution 7 - Sql Server
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]%'
Solution 8 - Sql Server
The following is correct for a WHERE
clause; to make a function wrap it in CASE WHEN
.
ISNUMERIC(table.field) > 0 AND PATINDEX('%[^0123456789]%', table.field) = 0
Solution 9 - Sql Server
declare @i numeric(28,5) = 12.0001if (@i/cast(@i as int) > 1) begin select 'this is not int' end else begin select 'this is int' end
Solution 10 - Sql Server
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".
Solution 11 - Sql Server
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.
Solution 12 - Sql Server
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.
Solution 13 - Sql Server
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
Solution 14 - Sql Server
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
Solution 15 - Sql Server
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
Solution 16 - Sql Server
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
Solution 17 - Sql Server
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/
Solution 18 - Sql Server
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'
Solution 19 - Sql Server
Had the same question. I finally used
where ATTRIBUTE != round(ATTRIBUTE)
and it worked for me
Solution 20 - Sql Server
WHERE IsNumeric(value + 'e0') = 1 AND CONVERT(FLOAT, value) BETWEEN -2147483648 AND 2147483647
Solution 21 - Sql Server
Seeing as this is quite old, but my solution isn't here, i thought to add another possible way to do this:
--This query only returns values with decimals
SELECT ActualCost
FROM TransactionHistory
where cast(ActualCost as int) != ActualCost
--This query only returns values without decimals
SELECT ActualCost
FROM TransactionHistory
where cast(ActualCost as int) = ActualCost
The easy part here is checking if the selected value is the same when cast as an integer.
Solution 22 - Sql Server
we can check if its a non integer by
> SELECT number2
> FROM table
> WHERE number2 LIKE '%[^0-9]%' and (( right(number2 ,len(number2)-1) LIKE '%[^0-9]%' and lefT(number2 ,1) <> '-') or ( right(number2 ,len(number2)-1) LIKE '%[^0-9]%' and lefT(number2 ,1) in ( '-','+') ) )
Solution 23 - Sql Server
This works fine in SQL Server
SELECT (SELECT ISNUMERIC(2) WHERE ISNUMERIC(2)=1 AND 2 NOT LIKE '%.%')
Solution 24 - Sql Server
Case When (LNSEQNBR / 16384)%1 = 0 then 1 else 0 end