Table of Contents

Search

  1. Preface
  2. Function reference
  3. Constants
  4. Operators
  5. Dates
  6. Functions
  7. System variables
  8. Datatype reference

Function Reference

Function Reference

IS_NUMBER

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 target column 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
)
Argument
Required/
Optional
Description
value
Required
Must be a String datatype. Passes the rows you want to evaluate. You can enter any valid 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.

Example

The following expression checks the ITEM_PRICE column 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 whitespace
''
0 (False)  
Empty string
'+123abc'
0 (False)
'  123'
1 (True)  
Leading whitespace
'123  '
1 (True)  
Trailing whitespace
'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 column and converts each valid number to a double-precision floating point value. If the value is not a valid number,
Data Integration
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 whitespace
''
0.00   
Empty string
'+123abc'
0.00
''  123ABC'
0.00
'ABC'
0.00
'-ABC'
0.00
NULL
NULL

0 COMMENTS

We’d like to hear from you!