Conditional Functions

Updated on 2024-04-19 GMT+08:00

View PDF

Description

**Table 1** Conditional functions
Conditional Function	Description
CASE value WHEN value1_1 [, value1_2 ]* THEN result1 [ WHEN value2_1 [, value2_2 ]* THEN result2 ]* [ ELSE resultZ ] END	Returns resultX when the first value in (valueX_1, valueX_2, ...) is included. If there is no matching value, returns result_z if provided, otherwise returns NULL.
CASE WHEN condition1 THEN result1 [ WHEN condition2 THEN result2 ]* [ ELSE resultZ ] END	Returns resultX when the first condition X is met. If no conditions are met, returns result_z if provided, otherwise returns NULL.
NULLIF(value1, value2)	Returns NULL if value1 equals value2; otherwise, returns value1. For example, NULLIF(5, 5) returns NULL; NULLIF(5, 0) returns 5.
COALESCE(value1, value2 [, value3 ]* )	Returns the first non-NULL value from value1, value2, .... For example, COALESCE(3, 5, 3) returns 3.
IF(condition, true_value, false_value)	Returns true_value if the condition is met, otherwise returns false_value. For example, IF(5 > 3, 5, 3) returns 5.
IFNULL(input, null_replacement)	Returns null_replacement if the input is NULL; otherwise, returns the input. This function returns a data type that is very clear about whether it is empty or not compared to COALESCE or CASE WHEN. The returned type is the common type of the two parameters, but can only be empty if null_replacement can be empty. This function allows nullable columns to be passed to functions or tables that use NOT NULL constraints. For example, IFNULL(nullable_column, 5) will never return NULL.
IS_ALPHA(string)	Returns true if all characters in the string are letters; otherwise, returns false.
IS_DECIMAL(string)	Returns true if the string can be parsed as a valid number; otherwise, returns false.
IS_DIGIT(string)	Returns true if all characters in the string are numbers; otherwise, returns false.
GREATEST(value1[, value2]*)	Returns the maximum value of all input parameters. If the input parameters contain NULL, returns NULL.
LEAST(value1[, value2]*)	Returns the minimum value of all input parameters. If the input parameters contain NULL, returns NULL.

Parent topic: Built-In Functions

Previous topic: Temporal Functions

Next topic: Type Conversion Functions

Feedback

Was this page helpful?

Helpful Not helpful

Provide feedback

Thank you very much for your feedback. We will continue working to improve the documentation.See the reply and handling status in My Cloud VOC.

The system is busy. Please try again later.

Which of the following issues have you encountered?

Content is inconsistent with the product UI

Unclear descriptions

Lack of examples or code

Incorrect steps

Can't find what I need

Lack of best practices

Feedback (optional)

0/500

Select at least one type of issue, and enter your comments or suggestions.

Enter a maximum of 500 characters.

Submit Cancel

For any further questions, feel free to contact us through the chatbot.

Chatbot