Function call rules in Standard SQL

The following rules apply to all functions unless explicitly indicated otherwise in the function description:

  • For functions that accept numeric types, if one operand is a floating point operand and the other operand is another numeric type, both operands are converted to FLOAT64 before the function is evaluated.
  • If an operand is NULL, the result is NULL, with the exception of the IS operator.
  • For functions that are time zone sensitive (as indicated in the function description), the default time zone, UTC, is used if a time zone is not specified.

SAFE. prefix

Syntax:

SAFE.function_name()

Description

If you begin a function with the SAFE. prefix, it will return NULL instead of an error. The SAFE. prefix only prevents errors from the prefixed function itself: it does not prevent errors that occur while evaluating argument expressions. The SAFE. prefix only prevents errors that occur because of the value of the function inputs, such as "value out of range" errors; other errors, such as internal or system errors, may still occur. If the function does not return an error, SAFE. has no effect on the output. If the function never returns an error, like RAND, then SAFE. has no effect.

Operators, such as + and =, do not support the SAFE. prefix. To prevent errors from a division operation, use SAFE_DIVIDE. Some operators, such as IN, ARRAY, and UNNEST, resemble functions, but do not support the SAFE. prefix. The CAST and EXTRACT functions also do not support the SAFE. prefix. To prevent errors from casting, use SAFE_CAST.

Example

In the following example, the first use of the SUBSTR function would normally return an error, because the function does not support length arguments with negative values. However, the SAFE. prefix causes the function to return NULL instead. The second use of the SUBSTR function provides the expected output: the SAFE. prefix has no effect.

SELECT SAFE.SUBSTR('foo', 0, -2) AS safe_output UNION ALL
SELECT SAFE.SUBSTR('bar', 0, 2) AS safe_output;

+-------------+
| safe_output |
+-------------+
| NULL        |
| ba          |
+-------------+

Supported functions

BigQuery supports the use of the SAFE. prefix with most scalar functions that can raise errors, including STRING functions, math functions, DATE functions, DATETIME functions, and TIMESTAMP functions. BigQuery does not support the use of the SAFE. prefix with aggregate, analytic, or user-defined functions.

Calling persistent user-defined functions (UDFs)

After creating a persistent UDF, you can call it as you would any other function, prepended with the name of the dataset in which it is defined as a prefix.

Syntax

[`project_name`].dataset_name.function_name([parameter_value[, ...]])

To call a UDF in a project other than the project that you are using to run the query, project_name is required.

Examples

The following example creates a UDF named multiply_by_three and calls it from the same project.

CREATE FUNCTION my_dataset.multiply_by_three(x INT64) AS (x * 3);

SELECT my_dataset.multiply_by_three(5) AS result; -- returns 15

The following example calls a persistent UDF from a different project.


CREATE `other_project`.other_dataset.other_function(x INT64, y INT64)
  AS (x * y * 2);

SELECT `other_project`.other_dataset.other_function(3, 4); --returns 24