Table of Contents

Search

  1. Preface
  2. The Transformation Language
  3. Constants
  4. Operators
  5. Dates
  6. Functions
  7. Creating Custom Functions
  8. Custom Function API Reference
  9. POWERCENTERHELP

Transformation Language Reference

Transformation Language 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 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
PowerCenter 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