Cast to DECIMAL

From string

Strict mode

If the source type is nullable, returns nullable type;

If the source type is non-nullable, returns non-nullable type;

BNF definition

<decimal>     ::= <whitespace>* <value> <whitespace>*

<whitespace>  ::= " " | "\t" | "\n" | "\r" | "\f" | "\v"

<value>       ::= <sign>? <significand> <exponent>?

<sign>        ::= "+" | "-"

<significand> ::= <digits> "." <digits> | <digits> | <digits> "." | "." <digits>

<digits>      ::= <digit>+

<digit>       ::= "0" | "1" | "2" | "3" | "4" | "5" | "6" | "7" | "8" | "9"

<exponent>    ::= <e_marker> <sign>? <digits>

<e_marker>    ::= "e" | "E"

Rule description

• Only supports decimal digits;

• Supports scientific notation;

• Supports rounding;

• Strings allow arbitrary prefix and suffix whitespace characters, including: " ", "\t", "\n", "\r", "\f", "\v".

• Return error when integer part overflows;

• Return error for invalid format.

Examples

String	Decimal(18, 6)	Comment
"123.1234567"	123.123457	Rounding
"12345."	12345.000000
"12345"	12345.000000
".123456"	0.123456
" \t\r\n\f\v123.456 \t\r\n\f\v"	123.456000	With prefix and suffix whitespace
" \t\r\n\f\v+123.456 \t\r\n\f\v"	123.456000	With prefix and suffix whitespace, positive sign
" \t\r\n\f\v-123.456 \t\r\n\f\v"	-123.456000	With prefix and suffix whitespace, negative sign
" \t\r\n\f\v+1.234e5 \t\r\n\f\v"	123400.000000	Scientific notation
" \t\r\n\f\v+1.234e+5 \t\r\n\f\v"	123400.000000	Scientific notation with positive exponent
" \t\r\n\f\v+1.234e-1 \t\r\n\f\v"	0.123400	Scientific notation with negative exponent
"123.456a"	Error	Invalid format
"1234567890123.123456"	Error	Overflow

Non-strict mode

Always returns nullable type;

BNF definition

<decimal>     ::= <whitespace>* <value> <whitespace>*

<whitespace>  ::= " " | "\t" | "\n" | "\r" | "\f" | "\v"

<value>       ::= <sign>? <significand> <exponent>

<sign>        ::= "+" | "-"

<significand> ::= <digits> | <digits> "." <digits> | <digits> "." | "." <digits>

<digits>      ::= <digit>+

<digit>       ::= "0" | "1" | "2" | "3" | "4" | "5" | "6" | "7" | "8" | "9"

<exponent>    ::= <e_marker> <sign>? <digits>

<e_marker>    ::= "e" | "E"

Rule description

• Supports all valid formats from strict mode;

• Converts to NULL when overflow occurs;

• Converts to NULL for invalid format.

Examples

String	Decimal(18, 6)	Comment
"123.1234567"	123.123457	Rounding
"12345."	12345.000000
"12345"	12345.000000
".123456"	0.123456
" \t\r\n\f\v123.456 \t\r\n\f\v"	123.456000	With prefix and suffix whitespace
" \t\r\n\f\v+123.456 \t\r\n\f\v"	123.456000	With prefix and suffix whitespace, positive sign
" \t\r\n\f\v-123.456 \t\r\n\f\v"	-123.456000	With prefix and suffix whitespace, negative sign
" \t\r\n\f\v+1.234e5 \t\r\n\f\v"	123400.000000	Scientific notation
" \t\r\n\f\v+1.234e+5 \t\r\n\f\v"	123400.000000	Scientific notation with positive exponent
" \t\r\n\f\v+1.234e-1 \t\r\n\f\v"	0.123400	Scientific notation with negative exponent
"123.456a"	NULL	Invalid format
"1234567890123.123456"	NULL	Overflow

From bool

true converts to 1, false converts to 0.

Strict mode

Error when overflow occurs (e.g., cast bool as decimal(1, 1)).

If the source type is nullable, returns nullable type.

If the source type is non-nullable, returns non-nullable type.

Non-strict mode

Converts to NULL when overflow occurs.

If the source type is nullable, returns nullable type.

If the source type is non-nullable:

• If overflow is possible (e.g., cast bool as decimal(1, 1)), returns nullable type;

• Otherwise returns non-nullable type.

From integer

Strict mode

Error when overflow occurs.

If the source type is nullable, returns nullable type.

If the source type is non-nullable, returns non-nullable type.

Examples

int	Decimal(18, 9)	Comment
123	123.00000000
2147483647	Error	Overflow

Non-strict mode

Converts to NULL when overflow occurs.

If the source type is nullable, returns nullable type.

If the source type is non-nullable:

• If overflow is possible (e.g., cast int as decimal(1, 0)), returns nullable type;

• Otherwise returns non-nullable type (e.g., cast int as decimal(18, 0)).

Examples

int	Decimal(18, 9)	Comment
123	123.00000000
2147483647	NULL	Overflow

From float/double

Supports rounding.

Strict mode

If the source type is nullable, returns nullable type.

If the source type is non-nullable, returns non-nullable type.

• Infinity and NaN cause errors.

• Error when overflow occurs.

Examples

float/double	Decimal(18, 3)	Comment
1.1239	1.124	Rounding
3.40282e+38	Error	Overflow
Infinity	Error
NaN	Error

Non-strict mode

Always returns nullable type.

• +/-Inf converts to NULL;

• NaN converts to NULL;

• Converts to NULL when overflow occurs.

Examples

float/double	Decimal(18, 6)	Comment
1.123456	1.123456
3.40282e+38	NULL	Overflow
Infinity	NULL
NaN	NULL

Cast between decimals

Supports rounding.

Strict mode

Error when overflow occurs.

If the source type is nullable, returns nullable type.

If the source type is non-nullable, returns non-nullable type.

Examples

Decimal(18, 8)	Decimal(10, 6)	Comment
1234.12345678	1234.123457	Rounding
12345.12345678	Error	Integer part overflow

Non-strict mode

Converts to NULL when overflow occurs.

If the source type is nullable, returns nullable type.

If the source type is non-nullable:

• If overflow is possible (e.g., cast decimal(18, 0) as decimal(9, 0)), returns nullable type;

• Otherwise returns non-nullable type (e.g., cast decimal(9, 0) as decimal(18, 0)).

Examples

Decimal(18, 8)	Decimal(10, 6)	Comment
1234.12345678	1234.123457	Rounding
12345.12345678	NULL	Integer part overflow

From date

Not supported.

From datetime

Not supported.

From time

Not supported.

From other types

Not supported