Share this page

IS DECIMAL Predicate

Use IS DECIMAL to determine if a particular result can be assigned to a column of the given data type. It is especially useful for cleaning data or validating data input. When applied to row value expressions, all elements must test the same.

The IS DECIMAL predicate takes the following form:

IS [NOT] DECIMAL

For example:

x IS DECIMAL

is true if x is decimal in form. If x is a string, then this is true if the value is a number that can be stored as a decimal data type with no loss of accuracy. Any leading or trailing white space is ignored. If x is a FLOAT value then this predicate is true only if the number can be stored in a decimal column without loss of precision of overflow.

The following predicates are all true:

' +12345 ' IS DECIMAL

'1.000000' IS DECIMAL

1.0e10 IS DECIMAL

DECIMAL('1234567890123456789012345', 25,0) IS DECIMAL

'number' IS NOT DECIMAL

'123 456' IS NOT DECIMAL

'1.0e40' IS NOT DECIMAL

If all values in a column pass this predicate, then the whole column can be coerced into a decimal column.