Menu
Is there any analog of NVL in Presto DB?
I need to check if a field is NULL and return a default value.
I solve this somehow like this:
SELECT CASE WHEN my_field is null THEN 0 ELSE my_field END FROM my_table But I'm curious if there is something that could simplify this code.
756
Like most other functions in Presto, they return null if any argument is null. Note that in some other databases, such as PostgreSQL, they only return null if all arguments are null.
NVL replaces a null with a string. NVL returns the replacement string when the base expression is null, and the value of the base expression when it is not null. To replace an expression with one value if it is null and a different value if it is not, use NVL2 .
COALESCE. coalesce(value1, value2[, ...]) Returns the first non-null value in the argument list. Like a CASE expression, arguments are only evaluated if necessary.
The ISO SQL function for that is COALESCE
coalesce(my_field,0) https://prestodb.io/docs/current/functions/conditional.html
P.S. COALESCE can be used with multiple arguments. It will return the first (from the left) non-NULL argument, or NULL if not found.
e.g.
coalesce (my_field_1,my_field_2,my_field_3,my_field_4,my_field_5) If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
