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.
If you begin a function with the
SAFE. prefix, it will return
of an error. The
SAFE. prefix only prevents errors from the prefixed function
itself: it does not prevent errors that occur while evaluating argument
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.
Operators, such as
=, do not support the
prefix. To prevent errors from a division
operation, use SAFE_DIVIDE. Some operators,
UNNEST, resemble functions, but do not support the
SAFE. prefix. The
EXTRACT functions also do not support the
SAFE. prefix. To prevent errors from casting, use
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
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 | +-------------+
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.
To call a UDF in a project other than the project that you are using to run
project_name is required.
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