T-SQL has powerful data-manipulation capabilities, but surprisingly it has no built-in function that checks whether a given string can be converted to an integer. I'll show you how you can use the T-SQL ISNUMERIC function and the CASE expression to determine whether a string is an integer. Then I'll show you how to write a CLR user-defined function (UDF) that makes it easier and faster to test a string to determine whether it's an integer.
Related: Splitting Separated Lists of Integers with HIERARCHYID
The task at hand is to query a sample table and produce a results column—called is_int—to indicate whether a string can be converted to an integer. The query will place a 1 in the is_int column if the string can be converted to an integer or a 0 if the string can't be converted to an integer.
Using T-SQL to Check for Numeric Data Types
The T-SQL ISNUMERIC function checks a data string to determine whether the string can be converted to a numeric data type. For example, the ISNUMERIC function considers strings that contain large numeric (e.g., 9223372036854775808), decimals (e.g., 12.4), currency figures (e.g., $123), and floating-point values (e.g., 1E2) to be strings that can be converted to a numeric data type.
Create and populate the table T1 with numeric and nonnumeric strings by running the code in Listing 1. Query the strings in table T1 and apply the ISNUMERIC function to the string column (is_numeric result column):
SELECT keycol, string, ISNUMERIC(string) AS is_numeric FROM dbo.T1;
Table 1 shows the result of this query and also adds another column called is_int, which specifies whether the string can in fact be converted to an integer. If the string is numeric, the result in column is_numeric is set to 1 and if nonnumeric, the value is 0. Similarly, if the string is convertible to an integer, the column is_int (which I added manually) specifies 1; if not convertible, the value is 0. If you look at the results in Table 1, you'll see that there are cases in which the ISNUMERIC function returns 1 even when the string isn't convertible to INT. In short, the string can be numeric but might not be convertible to an integer; the ISNUMERIC function is limited in the sense that it can tell you only whether the string can be converted to any numeric data type.
Table 1: Query Results for Convertibility to INT
|
Keycol |
String |
is_numeric |
is_int |
|---|---|---|---|
|
1 |
12345 |
1 |
1 |
|
2 |
abc |
0 |
0 |
|
3 |
NULL |
0 |
0 |
|
4 |
1E3 |
1 |
0 |
|
5 |
12.4 |
1 |
0 |
|
6 |
+123 |
1 |
1 |
|
7 |
-2345 |
1 |
1 |
|
8 |
|
0 |
0 |
|
9 |
$123 |
1 |
0 |
|
10 |
2147483647 |
1 |
1 |
|
11 |
2147483648 |
1 |
0 |
|
12 |
9223372036854775808 |
1 |
0 |
One possible solution is to use a CASE expression that applies a series of tests by using a “blacklist” approach; that is, this approach checks for a series of invalid conversion scenarios and returns a value of 0 if there’s a match. If the string doesn't match any of the criteria in the WHEN clauses, the ELSE CASE expression returns a value of 1.
The following code queries the data in the String column in Table 1 and produces the results you see in the is_numeric and is_int columns.
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 first WHEN clause—ISNUMERIC(string) = 0—tests the string to determine whether the string is nonnumeric and therefore also not an integer. If the result of the expression is that the string is numeric (function returns 1) and therefore possibly an integer, the CASE expression applies the second test (string LIKE '%\[^-+ 0-9\]%' ) to determine whether the string contains a character that isn't a plus or a minus sign, a space, or a digit.
If the string fails this second test—meaning there’s no invalid character—the third WHEN clause knows that the string is some form of an integer, and the string can safely be converted to NUMERIC(38, 0). If the last invalid scenario—CAST(string AS NUMERIC(38, 0)) NOT BETWEEN -2147483648. AND 2147483647—doesn’t yield true, the expression determines that the string can be converted to an integer and the ELSE clause returns a value of 1 in the is_int column.
You can check whether a string can be converted to other integer data types by changing the range in the third WHEN clause. For example, to check whether a string can be converted to a TINYINT, replace the third WHEN clause in the earlier query with the following clause:
WHEN CAST(string AS NUMERIC(38, 0)) NOT BETWEEN 0. AND 255. THEN 0
You should see results similar to those in Table 2.
Table 2: Query Results for Convertibility to TINYINT
|
Keycol |
string |
is_numeric |
is_tinyint |
|
1 |
12345 |
1 |
0 |
|
2 |
abc |
0 |
0 |
|
3 |
NULL |
0 |
0 |
|
4 |
1E3 |
1 |
0 |
|
5 |
12.4 |
1 |
0 |
|
6 |
+123 |
1 |
1 |
|
7 |
-2345 |
1 |
0 |
|
8 |
|
0 |
0 |
|
9 |
$123 |
1 |
0 |
|
10 |
2147483647 |
1 |
0 |
|
11 |
2147483648 |
1 |
0 |
|
12 |
9223372036854775808 |
1 |
0 |
To check whether a string can be converted to a SMALLINT, replace the third WHEN clause in the earlier query with the following clause:
WHEN CAST(string AS NUMERIC(38, 0)) NOT BETWEEN -32768. AND 32767. THEN 0
You should see results similar to those in Table 3.
Table 3: Query Results for Convertibility to SMALLINT
|
Keycol |
string |
is_numeric |
is_smallint |
|
1 |
12345 |
1 |
1 |
|
2 |
abc |
0 |
0 |
|
3 |
NULL |
0 |
0 |
|
4 |
1E3 |
1 |
0 |
|
5 |
12.4 |
1 |
0 |
|
6 |
+123 |
1 |
1 |
|
7 |
-2345 |
1 |
1 |
|
8 |
|
0 |
0 |
|
9 |
$123 |
1 |
0 |
|
10 |
2147483647 |
1 |
0 |
|
11 |
2147483648 |
1 |
0 |
|
12 |
9223372036854775808 |
1 |
0 |
To check whether a string can be converted to a BIGINT, replace the third WHEN clause in the earlier query with the following clause:
WHEN CAST(string AS NUMERIC(38, 0)) NOT BETWEEN -9223372036854775808. AND 9223372036854775807. THEN 0
You should see results similar to those in Table 4.
Table 4: Query Results for Convertibility to BIGINT
|
Keycol |
string |
is_numeric |
is_bigint |
|
1 |
12345 |
1 |
1 |
|
2 |
abc |
0 |
0 |
|
3 |
NULL |
0 |
0 |
|
4 |
1E3 |
1 |
0 |
|
5 |
12.4 |
1 |
0 |
|
6 |
+123 |
1 |
1 |
|
7 |
-2345 |
1 |
1 |
|
8 |
|
0 |
0 |
|
9 |
$123 |
1 |
0 |
|
10 |
2147483647 |
1 |
1 |
|
11 |
2147483648 |
1 |
1 |
|
12 |
9223372036854775808 |
1 |
0 |
Detecting NULL and Empty Strings as Valid Integers
Note that the ISNUMERIC function returns a value of 0 in two cases—NULL and empty string—but you might want to consider these values as valid integers. If you run the following code to check whether NULL and empty string are convertible to integers, you'll know that these values are convertible because you won’t get a conversion error.
SELECT CAST(NULL AS INT), CAST(' AS INT)
When converting NULL to an integer, you get a NULL back. When converting an empty string to an integer, you get 0 back. If you want to return 1 in these cases, you can add “whitelist” items, as the first line of code in the following query shows:
SELECT keycol, string, ISNUMERIC(string) AS is_numeric,
CASE
WHEN RTRIM(LTRIM(string)) = ' THEN 1
WHEN string IS NULL THEN 1
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;
You should see results similar to those in Table 5. It’s important to check for NULLs and empty strings before checking the ISNUMERIC scenario, otherwise the expression ISNUMERIC(string) = 0 will return true in those scenarios and the CASE expression will return 0.
Table 5: Query Results when Accepting NULL and Empty String as Valid Integers
|
Keycol |
string |
is_numeric |
is_int |
|
1 |
12345 |
1 |
1 |
|
2 |
abc |
0 |
0 |
|
3 |
NULL |
0 |
1 |
|
4 |
1E3 |
1 |
0 |
|
5 |
12.4 |
1 |
0 |
|
6 |
+123 |
1 |
1 |
|
7 |
-2345 |
1 |
1 |
|
8 |
|
0 |
1 |
|
9 |
$123 |
1 |
0 |
|
10 |
2147483647 |
1 |
1 |
|
11 |
2147483648 |
1 |
0 |
|
12 |
9223372036854775808 |
1 |
0 |
CLR Solution
A much simpler way to check whether a string is convertible to an integer is to write a CLR UDF. The fact of the matter is that Microsoft .NET provides better convertibility tests than T-SQL. Dejan Sarka, a SQL Server MVP, provided this solution for checking whether a string is convertible to an integer using .NET.
The code in Listing 2 has the definition of the function fn_IsInt in C#. Deploy the function in the tempdb database. If you aren't familiar with creating assemblies in SQL Server, read the SQL Server Magazine sidebar "5 Steps for Developing and Deploying CLR Code in SQL Server," April 2006.
The fn_IsInt function checks whether the input is NULL or not NULL. If the input is NULL, the query returns a "false" value (0 as with T-SQL). If the input is not NULL, the function invokes the Int32.TryParse method, which attempts to parse the input and returns a Boolean value that indicates whether the input is convertible (True or 1) or isn’t (False or 0).
After the function is registered in the tempdb database, you can use it in a query, as the following code shows:
SELECT keycol, string, ISNUMERIC(string) AS is_numeric, dbo.fn_IsInt(string) AS is_int FROM dbo.T1;
After you run this code, you should see results similar to those in Table 1.
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.
Learn more: T-SQL String Manipulation Tips and Techniques
