Determines if a string argument is a valid number.
ISNUMERIC( string )
string The string to be analyzed to determine if the string represents a valid number.
INT
ISNUMERIC returns 1 when the input string evaluates to a valid integer or floating point number; otherwise it returns 0. The function also returns 0 if the string contains only blanks or is NULL.
Following are values that also cause the ISNUMERIC function to return 0:
Values that use the letter d or D as the exponent separator. For example, 1d2.
Special values such as NAN, 0x12, INF, and INFINITY.
NULL (for example, SELECT ISNUMERIC( NULL );
)
SQL/2003 Vendor extension.
The following example imports data from an external file, exports rows that contain invalid values, and copies the remaining rows to a permanent table. In this example, the ISNUMERIC statement validates that the values in height_in_cms values are numeric.
CREATE GLOBAL TEMPORARY TABLE MyData( person VARCHAR(100), birth_date VARCHAR(30), height_in_cms VARCHAR(10) ) ON COMMIT PRESERVE ROWS; LOAD TABLE MyData FROM 'exported.dat'; UNLOAD SELECT * FROM MyData WHERE ISDATE( birth_date ) = 0 OR ISNUMERIC( height_in_cms ) = 0 TO 'badrows.dat'; INSERT INTO PermData SELECT person, birth_date, height_in_cms FROM MyData WHERE ISDATE( birth_date ) = 1 AND ISNUMERIC( height_in_cms ) = 1; COMMIT; DROP TABLE MyData; |
Discuss this page in DocCommentXchange. Send feedback about this page using email. |
Copyright © 2009, iAnywhere Solutions, Inc. - SQL Anywhere 11.0.1 |