Conditional Functions

CASE value

WHEN value1_1 [, value1_2 ]* THEN result1

[ WHEN value2_1 [, value2_2 ]* THEN result2 ]*

[ ELSE resultZ ]

END

Returns resultX when the value is contained in (valueX_1, valueX_2, …).

Only the first matched value is returned.

When no value matches, returns resultZ if it is provided and returns NULL otherwise.

CASE

WHEN condition1 THEN result1

[ WHEN condition2 THEN result2 ]*

[ ELSE resultZ ]

END

Returns resultX when the first conditionX is met.

Only the first matched value is returned.

When no condition is met, returns resultZ if it is provided and returns NULL otherwise.

NULLIF(value1, value2)

Returns NULL if value1 is equal to value2; returns value1 otherwise.

For example, NullIF (5, 5) returns NULL.

NULLIF(5, 0) returns 5.

COALESCE(value1, value2 [, value3 ]* )

Returns the first value (from left to right) that is not NULL from value1, value2, ….

For example, COALESCE(NULL, 5) returns 5.

IF(condition, true_value, false_value)

Returns the true_value if condition is met, otherwise false_value.

For example, IF(5 > 3, 5, 3) returns 5.

IS_ALPHA(string)

Returns TRUE if all characters in the string are letters, otherwise FALSE.

IS_DECIMAL(string)

Returns TRUE if string can be parsed to a valid numeric, otherwise FALSE.

IS_DIGIT(string)

Returns TRUE if all characters in the string are digits, otherwise FALSE.