IS_NUMBER
Returns whether a string is a valid number. A valid number consists of the following parts:
- •Optional space before the number
- •Optional sign (+/-)
- •One or more digits with an optional decimal point
- •Optional scientific notation, such as the letter ‘e’ or ‘E’ (and the letter ‘d’ or ‘D’ on Windows) followed by an optional sign (+/-), followed by one or more digits
- •Optional white space following the number
The following numbers are all valid:
' 100 '
' +100'
'-100'
'-3.45e+32'
'+3.45E-32'
'+3.45d+32' (Windows only)
'+3.45D-32' (Windows only)
'.6804'
The output port for an IS_NUMBER expression must be a String or Numeric datatype.
You might use IS_NUMBER to test or filter data in a flat file before writing it to a target.
Syntax
IS_NUMBER( value )
The following table describes the argument for this command:
Argument | Required/ Optional | Description |
|---|
value | Required | Must be a String datatype. Passes the rows you want to evaluate. You can enter any valid transformation expression. |
Return Value
TRUE (1) if the row is a valid number.
FALSE (0) if the row is not a valid number.
NULL if a value in the expression is NULL.
Examples
The following expression checks the ITEM_PRICE port for valid numbers:
IS_NUMBER( ITEM_PRICE )
ITEM_PRICE | RETURN VALUE |
|---|
'123.00' | 1 (True) |
'-3.45e+3' | 1 (True) |
'-3.45D-3' | 1 (True - Windows only) |
'-3.45d-3' | 0 (False - UNIX only) |
'3.45E-' | 0 (False) Incomplete number |
' ' | 0 (False) Consists entirely of blanks |
'' | 0 (False) Empty string |
'+123abc' | 0 (False) |
' 123' | 1 (True) Leading white blanks |
'123 ' | 1 (True) Trailing white blanks |
'ABC' | 0 (False) |
'-ABC' | 0 (False) |
NULL | NULL |
Use IS_NUMBER to test data before using one of the numeric conversion functions, such as TO_FLOAT. For example, the following expression checks the values in the ITEM_PRICE port and converts each valid number to a double-precision floating point value. If the value is not a valid number, the Data Integration Service returns 0.00:
IIF( IS_NUMBER ( ITEM_PRICE ), TO_FLOAT( ITEM_PRICE ), 0.00 )
ITEM_PRICE | RETURN VALUE |
|---|
'123.00' | 123 |
'-3.45e+3' | -3450 |
'3.45E-3' | 0.00345 |
' ' | 0.00 Consists entirely of blanks |
'' | 0.00 Empty string |
'+123abc' | 0.00 |
'' 123ABC' | 0.00 |
'ABC' | 0.00 |
'-ABC' | 0.00 |
NULL | NULL |