ISNUMERIC
Synopsis
ISNUMERIC(check-expression)
Arguments
Argument | Description |
---|---|
check-expression | The expression to be evaluated. |
ISNUMERIC returns the SMALLINT data type.
Description
ISNUMERIC evaluates check-expression and returns one of the following values:
Returns 1 if check-expression is a valid number. A valid number can either be a numeric expression or a string that represents a valid number.
A numeric expression is first converted to canonical form, resolving multiple leading signs; therefore, a numeric expression such as +-+++34 is a valid number.
A numeric string is not converted before evaluation. A numeric string must have at most one leading sign to evaluate as a valid number. A numeric string with a trailing decimal point evaluates as a valid number.
Returns 0 if check-expression is not a valid number. Any string that contains a non-numeric character is not a valid number. A numeric string with more than one leading sign, such as '+-+++34', is not evaluated as a valid number. An InterSystems IRIS encoded list always returns 0, even if its element(s) are valid numbers. An empty string ISNUMERIC('') returns 0.
Returns NULL if check-expression is NULL. ISNUMERIC(NULL) returns null.
ISNUMERIC generates an SQLCODE -7, exponent out of range error if a scientific notation exponent is greater than 308 (308 – (number of integers - 1)). For example, ISNUMERIC(1E309) and ISNUMERIC(111E307) both generate this error code. If an exponent numeric string less than or equal to '1E145' returns 1; an exponent numeric string greater than '1E145' returns 0.
The ISNUMERIC function is very similar to the ObjectScript $ISVALIDNUM function. However, these two functions return different values when the input value is NULL.
Examples
In the following example, all of the ISNUMERIC functions return 1:
SELECT ISNUMERIC(99) AS MyInt, ISNUMERIC('-99') AS MyNegInt, ISNUMERIC('-0.99') AS MyNegFrac, ISNUMERIC('-0.00') AS MyNegZero, ISNUMERIC('-0.09'+7) AS MyAdd, ISNUMERIC('5E2') AS MyExponent
The following example returns NULL if FavoriteColors is NULL; otherwise, it returns 0, because FavoriteColors is not a numeric field:
SELECT Name, ISNUMERIC(FavoriteColors) AS ColorPref FROM Sample.Person
See Also
IFNULL function
ISNULL function
NULLIF function
ObjectScript function: $ISVALIDNUM