DB2 Scalar functions - DECFLOAT_FORMAT

DECFLOAT_FORMAT

The DECFLOAT_FORMAT function returns a DECFLOAT(34) value that is based on the interpretation of the input string using the specified format.

The schema is SYSIBM.

string-expression

An expression that returns a value that is a CHAR and VARCHAR data type.
In a Unicode database, if a supplied argument is a GRAPHIC or VARGRAPHIC data type, it is first converted to VARCHAR before evaluating the function.
Leading and trailing blanks are removed from the string.
If format-string is not specified, the resulting substring must conform to the rules for forming an SQL integer, decimal, floating-point, or decimal floating-point constant (SQLSTATE 22018) and not be greater than 42 bytes (SQLSTATE 42820); otherwise, the resulting substring must contain the components of a number that correspond to the format specified by format-string (SQLSTATE 22018).

format-string

An expression that returns a value that is a built-in character string data type (except CLOB).
In a Unicode database, if a supplied argument is a graphic string (except DBCLOB), it is first converted to a character string before evaluating the function.
The actual length must not be greater than 255 bytes (SQLSTATE 22018).
The value is a template for how string-expression is to be interpreted for conversion to a DECFLOAT value.
Format elements specified as a prefix can be used only at the beginning of the template.
Format elements specified as a suffix can be used only at the end of the template.
The format elements are case sensitive. The template must not contain more than one of the MI, S, or PR format elements (SQLSTATE 22018).

Table 1. Format elements for the DECFLOAT_FORMAT function

Format element	Description
0 or 9	Each 0 or 9 represents a digit.
MI	Suffix: If string-expression is to represent a negative number, a trailing minus sign (-) is expected. If string-expression is to represent a positive number, a trailing blank can be specified.
S	Prefix: If string-expression is to represent a negative number, a leading minus sign (-) is expected. If string-expression is to represent a positive number, a leading plus sign (+) or leading blank can be specified.
PR	Suffix: If string-expression is to represent a negative number, a leading less than character (<) and a trailing greater than character (>) are expected. If string-expression is to represent a positive number, a leading space and a trailing space can be specified.
$	Prefix: A leading dollar sign ($) must be specified.
,	Specifies the expected location of a comma. This comma is used as a group separator.
.	Specifies the expected location of the period. This period is used as a decimal point.

If format-string is not specified, string-expression must conform to the rules for forming an SQL integer, decimal, floating-point, or decimal floating-point constant (SQLSTATE 22018) and have a length not greater than 42 bytes (SQLSTATE 42820).

The result is a DECFLOAT(34).
If the first or second argument can be null, the result can be null; if the first or second argument is null, the result is the null value.

Notes

Syntax alternatives: TO_NUMBER is a synonym for DECFLOAT_FORMAT.

Examples

The following example returns 123.45
```
DECFLOAT_FORMAT( '123.45' )
```
The following example returns -123456.78
```
DECFLOAT_FORMAT( '-123456.78' )
```
The following example returns 123456.78
```
DECFLOAT_FORMAT( '+123456.78' )
```
The following example returns 12300
```
DECFLOAT_FORMAT( '1.23E4' )
```
The following example returns 123.40
```
DECFLOAT_FORMAT( '123.4', '9999.99' )
```
The following example returns 1234
```
DECFLOAT_FORMAT( '001,234', '000,000' )
```
The following example returns 1234
```
DECFLOAT_FORMAT( '1234 ', '9999MI' )
```
The following example returns -1234
```
DECFLOAT_FORMAT( '1234-', '9999MI' )
```
The following example returns 1234
```
DECFLOAT_FORMAT( '+1234', 'S9999' )
```
The following example returns -1234
```
DECFLOAT_FORMAT( '-1234', 'S9999' )
```
The following example returns 1234
```
DECFLOAT_FORMAT( ' 1234 ', '9999PR' )
```
The following example returns -1234
```
DECFLOAT_FORMAT( '<1234>', '9999PR' )
```

The following example returns 123456.78

DECFLOAT_FORMAT( '$123,456.78', '$999,999.99' )