v.19.8New Features

Add Todecimalorzero and Todecimalornull Functions

Add toDecimal*OrZero and toDecimal*OrNull functions. #5291 (Artem Zuikov)
Added new functions toDecimalOrZero and toDecimalOrNull that convert values to decimal types, returning zero or null instead of throwing an error on conversion failure.

Why it matters

These functions address the problem of conversion errors when casting invalid or incompatible values to decimal types. Instead of causing query failures, they provide safer alternatives that return zero or null, improving robustness and error handling in data processing.

How to use it

Use the functions toDecimal32OrZero, toDecimal64OrZero, toDecimal32OrNull, or toDecimal64OrNull in your queries to convert values to the respective decimal types. These functions behave like standard toDecimal* functions but return zero or null when conversion is not possible, preventing exceptions. For example:

SELECT toDecimal32OrZero('123.45', 2);
SELECT toDecimal64OrNull('invalid_value', 4);