title | description | author | ms.author | ms.date | ms.service | ms.subservice | ms.topic | f1_keywords | helpviewer_keywords | dev_langs | monikerRange | ||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
ISNUMERIC (Transact-SQL) |
ISNUMERIC (Transact-SQL) |
markingmyname |
maghan |
03/13/2017 |
sql |
t-sql |
reference |
|
|
TSQL |
>= aps-pdw-2016 || = azuresqldb-current || = azure-sqldw-latest || >= sql-server-2016 || >= sql-server-linux-2017 || = azuresqldb-mi-current||=fabric |
ISNUMERIC (Transact-SQL)
[!INCLUDE sql-asdb-asdbmi-asa-pdw-fabricse-fabricdw]
Determines whether an expression is a valid numeric type.
:::image type=»icon» source=»../../includes/media/topic-link-icon.svg» border=»false»::: Transact-SQL syntax conventions
Syntax
[!INCLUDEsql-server-tsql-previous-offline-documentation]
Arguments
expression
Is the expression to be evaluated.
Return Types
int
Remarks
ISNUMERIC returns 1 when the input expression evaluates to a valid numeric data type; otherwise it returns 0. Valid numeric data types include the following:
Area | Numeric data types |
---|---|
Exact Numerics | bigint, int, smallint, tinyint, bit |
Fixed Precision | decimal, numeric |
Approximate | float, real |
Monetary Values | money, smallmoney |
[!NOTE]
ISNUMERIC returns 1 for some characters that are not numbers, such as plus (+), minus (-), and valid currency symbols such as the dollar sign ($). For a complete list of currency symbols, see money and smallmoney (Transact-SQL).
Examples
The following example uses ISNUMERIC
to return all the postal codes that are not numeric values.
USE AdventureWorks2012; GO SELECT City, PostalCode FROM Person.Address WHERE ISNUMERIC(PostalCode) <> 1; GO
Examples: [!INCLUDEssazuresynapse-md] and [!INCLUDEssPDW]
The following example uses ISNUMERIC
to return all the postal codes that are not numeric values.
USE master; GO SELECT name, ISNUMERIC(name) AS IsNameANumber, database_id, ISNUMERIC(database_id) AS IsIdANumber FROM sys.databases; GO
See also
- Expressions (Transact-SQL)
- System Functions (Transact-SQL)
- Data Types (Transact-SQL)
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.
This SQL Server tutorial explains how to use the ISNUMERIC function in SQL Server (Transact-SQL) with syntax and examples.
Description
In SQL Server (Transact-SQL), the ISNUMERIC function returns 1 if the expression is a valid number. Otherwise, it returns 0.
Syntax
The syntax for the ISNUMERIC function in SQL Server (Transact-SQL) is:
ISNUMERIC( expression )
Parameters or Arguments
- expression
- The value to test whether it is a numeric value.
Note
- The ISNUMERIC function returns 1, if the expression is a valid number.
- The ISNUMERIC function returns 0, if the expression is NOT a valid number.
Applies To
The ISNUMERIC function can be used in the following versions of SQL Server (Transact-SQL):
- SQL Server 2017, SQL Server 2016, SQL Server 2014, SQL Server 2012, SQL Server 2008 R2, SQL Server 2008, SQL Server 2005
Example
Let’s look at some SQL Server ISNUMERIC function examples and explore how to use the ISNUMERIC function in SQL Server (Transact-SQL).
For example:
SELECT ISNUMERIC(1234); Result: 1 SELECT ISNUMERIC('1234'); Result: 1 SELECT ISNUMERIC(10 * 5); Result: 1 SELECT ISNUMERIC('techonthenet.com'); Result: 0 SELECT ISNUMERIC('2014-05-01'); Result: 0
In SQL Server, you can use the ISNUMERIC()
function to find out whether an expression is numeric or not.
The function returns 1 if the expression is numeric, and 0 if it’s not.
To use this function, simply pass the value/expression to the function while calling it.
Example 1 – Numeric Expression
Here’s an example to demonstrate what happens when you pass a numeric expression to this function.
SELECT ISNUMERIC(250) AS Result;
Result:
+----------+ | Result | |----------| | 1 | +----------+
In this case, the value is numeric and the result is 1.
We get the same result even if the value is provided as a string (enclosed in single quotes).
SELECT ISNUMERIC('250') AS Result;
Result:
+----------+ | Result | |----------| | 1 | +----------+
Example 2 – Non-Numeric Expression
Here’s what happens when the value is not numeric.
SELECT ISNUMERIC('Hey!') AS Result;
Result:
+----------+ | Result | |----------| | 0 | +----------+
Example 3 – A Database Example
Here’s an example of using ISNUMERIC()
in a WHERE
clause when querying a database:
USE WideWorldImportersDW; SELECT COUNT(*) AS [Count] FROM Dimension.Customer WHERE ISNUMERIC([Postal Code]) = 1;
Result:
+---------+ | Count | |---------| | 402 | +---------+
This returns the count of all rows with a numeric postal code.
Unexpected Results? When Non-Numeric IS Numeric
Some characters are treated as being numeric, even when they’re not a number. This is something you need to be aware of when using this function, otherwise you could get results that you don’t expect.
See Non-Number Characters that Return Positive when using ISNUMERIC()
for an explanation and examples.
If your data is ‘test’, ‘test0’, ‘test1111’, ‘111test’, ‘111’
To select all records where the data is a simple int:
SELECT *
FROM myTable
WHERE col1 REGEXP '^[0-9]+$';
Result: ‘111’
(In regex, ^ means begin, and $ means end)
To select all records where an integer or decimal number exists:
SELECT *
FROM myTable
WHERE col1 REGEXP '^[0-9]+\.?[0-9]*$'; - for 123.12
Result: ‘111’ (same as last example)
Finally, to select all records where number exists, use this:
SELECT *
FROM myTable
WHERE col1 REGEXP '[0-9]+';
Result: ‘test0’ and ‘test1111’ and ‘111test’ and ‘111’