Communities
A collaborative platform to connect and grow with like-minded Informaticans across the globe
Product Communities
Connect and collaborate with Informatica experts and champions
Discussions
Have a question? Start a Discussion and get immediate answers you are looking for
User Groups
Customer-organized groups that meet online and in-person. Join today to network, share ideas, and get tips on how to get the most out of Informatica
Get Started
Community Guidelines
Knowledge Center
Troubleshooting documents, product guides, how to videos, best practices, and more
Knowledge Base
One-stop self-service portal for solutions, FAQs, Whitepapers, How Tos, Videos, and more
Support TV
Video channel for step-by-step instructions to use our products, best practices, troubleshooting tips, and much more
Documentation
Information library of the latest product documents
Velocity (Best Practices)
Best practices and use cases from the Implementation team
Learn
Rich resources to help you leverage full capabilities of our products
Trainings
Role-based training programs for the best ROI
Certifications
Get certified on Informatica products. Free, Foundation, or Professional
Product Learning Paths
Free and unlimited modules based on your expertise level and journey
Resources
Library of content to help you leverage the best of Informatica products
Tech Tuesdays Webinars
Most popular webinars on product architecture, best practices, and more
Product Availability Matrix
Product Availability Matrix statements of Informatica products
SupportFlash
Monthly support newsletter
Support Documents
Informatica Support Guide and Statements, Quick Start Guides, and Cloud Product Description Schedule
Product Lifecycle
End of Life statements of Informatica products
Ideas
Events
Change Request Tracking
Marketplace
English
  • PowerCenter 10.5.6
  • All Products

Table of Contents

Search

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

Transformation Language Reference

Transformation Language Reference

Back Next

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
0 COMMENTS
We’d like to hear from you!