Utility Functions

General-purpose functions for data manipulation, control flow, and type handling.

Parenthetical

Used for explicit grouping of expressions, primarily to control the order of operations. Does not change the value, type, or purpose of the enclosed expression.

Inputs:

Any

Output:

Same as input

Purpose:

property

Example:

SELECT (price + tax) * quantity AS total;

Coalesce

Returns the first non-null value from its list of arguments. All arguments must be of compatible types. Returns NULL if all arguments are NULL.

Inputs:

Any (compatible types)...

Output:

Same as input types (common compatible type)

Purpose:

property

Example:

SELECT COALESCE(nickname, first_name, 'Valued Customer') AS display_name;

Cast

Explicitly converts an expression to a target data type specified as the second argument. Idiomatic shorthand is <expr>::<type>, though cast(a, b) work as well.

Inputs:

AnyDataType

Output:

Specified by second argument (DataType)

Purpose:

property

Example:

SELECT order_id::integer AS order_id_num; SELECT CAST(order_id_string,INTEGER) AS order_id_num;

IS_NULL

Returns true if the input argument is NULL, false otherwise.

Inputs:

Any

Output:

BOOL

Purpose:

property

Example:

SELECT order_id WHERE IS_NULL(shipping_address);

BOOL

Attempts to convert the input argument to a boolean value. Behavior depends on the input type (e.g., 0 -> false, non-zero -> true; empty string -> false, non-empty -> true).

Inputs:

Any

Output:

BOOL

Purpose:

property

Example:

SELECT product_id, BOOL(is_active_flag) AS is_active;

CASE

Evaluates a series of conditions and returns a result based on the first condition that evaluates to true. Syntax: `CASE WHEN condition1 THEN result1 [WHEN condition2 THEN result2 ...] [ELSE default_result] END`. All result expressions must have compatible types.

Inputs:

BOOLAny...

Output:

Common compatible type of result expressions

Purpose:

property

Example:

SELECT CASE WHEN score > 90 THEN 'A' WHEN score > 80 THEN 'B' ELSE 'C' END AS grade;

NULLIF

Returns NULL if the two arguments are equal, otherwise it returns the first argument.

Inputs:

AnyAny

Output:

Same as first input

Purpose:

property

Example:

SELECT NULLIF(description, '') AS description_or_null;

UNION

Combines the ranges of two or more concepts into a new concept.

Inputs:

Any...

Output:

Depends on inputs

Purpose:

key

Example:

auto all_users <- union(lapsed_users, active_users);

HASH

Computes a hash of the given string using a specified algorithm (e.g., 'md5', 'sha1', 'sha256').

Inputs:

STRINGSTRING

Output:

STRING

Purpose:

property

Example:

SELECT HASH(user_email, 'sha256') AS hashed_email;

GEO_POINT

Creates a geographical point from latitude and longitude values.

Inputs:

NUMERICNUMERIC

Output:

GEOGRAPHY

Purpose:

property

Example:

SELECT GEO_POINT(latitude, longitude) AS location;

GEO_DISTANCE

Calculates the distance between two geographical points, typically in meters.

Inputs:

GEOGRAPHYGEOGRAPHY

Output:

NUMERIC

Purpose:

property

Example:

SELECT GEO_DISTANCE(location1, location2) AS distance_in_meters;