Function Reference > Functions > 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