When you call a function, specific rules may apply. You can also add theSAFE. prefix, which prevents functions from generating some types of errors.To learn more, see the next sections.

Function call rules

The following rules apply to all built-in GoogleSQL functions unlessexplicitly indicated otherwise in the function description:

• If an operand isNULL, the function result isNULL.
• For functions that are time zone sensitive, the default time zone,UTC, is used when a time zone isn't specified.

Named arguments

named_argument=>value

You can provide parameter arguments by name when calling some functions andprocedures. These arguments are callednamed arguments. An argument that isn'tnamed is called apositional argument.

• Named arguments are optional, unless specified as required in thefunction signature.
• Named arguments don't need to be in order.
• You can specify positional arguments before named arguments.
• You can't specify positional arguments after named arguments.
• An optional positional argument that isn't used doesn't need to be addedbefore a named argument.

Examples

These examples reference a function calledCountTokensInText, which countsthe number of tokens in a paragraph. The function signature looks like this:

CountTokensInText(paragraphSTRING,tokensARRAY<STRING>,delimitersSTRING)

CountTokensInText contains three arguments:paragraph,tokens, anddelimiters.paragraph represents a body of text to analyze,tokens represents the tokens to search for in the paragraph,anddelimiters represents the characters that specify a boundarybetween tokens in the paragraph.

This is a query that includesCountTokensInTextwithout named arguments:

SELECTtoken,countFROMCountTokensInText('Would you prefer softball, baseball, or tennis? There is also swimming.',['baseball','football','tennis'],' .,!?()')

This is the query with named arguments:

SELECTtoken,countFROMCountTokensInText(paragraph=>'Would you prefer softball, baseball, or tennis? There is also swimming.',tokens=>['baseball','football','tennis'],delimiters=>' .,!?()')

If named arguments are used, the order of the arguments doesn't matter. Thisworks:

SELECTtoken,countFROMCountTokensInText(tokens=>['baseball','football','tennis'],delimiters=>' .,!?()',paragraph=>'Would you prefer softball, baseball, or tennis? There is also swimming.')

You can mix positional arguments and named arguments, as long as the positionalarguments in the function signature come first:

SELECTtoken,countFROMCountTokensInText('Would you prefer softball, baseball, or tennis? There is also swimming.',tokens=>['baseball','football','tennis'],delimiters=>' .,!?()')

This doesn't work because a positional argument appears after a named argument:

SELECTtoken,countFROMCountTokensInText(paragraph=>'Would you prefer softball, baseball, or tennis? There is also swimming.',['baseball','football','tennis'],delimiters=>' .,!?()')

If you want to usetokens as a positional argument, any arguments that appearbefore it in the function signature must also be positional arguments.If you try to use a named argument forparagraph and a positionalargument fortokens, this will not work.

-- This doesn't work.SELECTtoken,countFROMCountTokensInText(['baseball','football','tennis'],delimiters=>' .,!?()',paragraph=>'Would you prefer softball, baseball, or tennis? There is also swimming.')-- This works.SELECTtoken,countFROMCountTokensInText('Would you prefer softball, baseball, or tennis? There is also swimming.',['baseball','football','tennis'],delimiters=>' .,!?()')

Chained function calls

Writing nested expressions in GoogleSQL is common, particularly whenyou're cleaning or transforming data. Deeply nested expressions can be hard toread and maintain.

Here's an example of an expression with deep nesting. The nesting makes itdifficult to read:

SELECTREPLACE(REPLACE(REPLACE(REPLACE(REPLACE('one two three four five','one','1'),'two','2'),'three','3'),'four','4'),'five','5');

Here is the same example rewritten using chained function syntax:

SELECT('one two three four five').REPLACE('one','1').REPLACE('two','2').REPLACE('three','3').REPLACE('four','4').REPLACE('five','5');

Chained function calls provide a syntax for simplifying nestedfunction calls. Chained function calls have the following properties:

• Chained function calls consist of functions connected together witha. character.
• Each function in the chain must meet certainrequirements.
• Each function in the chain uses the output from the previous functionas its first argument.
• If the chain starts from a column name (or other identifier), that initialargument must be surrounded by() characters, for example:(x).UPPER().Parentheses aren't required on the input for other cases.
• For functions with multi-part names, likeSAFE.SQRT, the function name mustbe parenthesized. For example,(x).(SAFE.SQRT)().

Chained function calls are generally easier to read, understand, and maintainthan deeply nested function calls because they're applied in the order in whichthey're written.

Chained function requirements

You can write function calls in chained call syntax if the functions meet theserequirements:

• The function must use standard function call syntax, using comma-separatedarguments. Function-like syntaxes that include special-case keywords, suchasCAST(value AS type), aren't included.
• The function must have at least one argument. The first argument becomesthe chained input, and must meet the following requirements:
  • It must be an expression. It can't be atable, connection, model,descriptor, or other non-expression argument type.
  • It must be a positional argument. It can't be anamed argument (name => value).
  • It can't have anAS alias (as invalue AS alias).

There are a few additional special cases. Chained function calls are allowedfor these functions:

• Aggregate functions with standard modifiers likeDISTINCT,ORDER BY, etc.You write the modifiers inside the parentheses with their usual syntax. Forexample, you writeCOUNT(DISTINCT x) as(x).COUNT(DISTINCT).
• FLATTEN (and other functions that do implicit flattening)

Chained function calls aren't allowed for these functions:

• GROUPING

Example chained function calls

The following examples show the chained function call equivalentof some standard syntax calls:

UPPER(x)(x).UPPER()# Chained function call equivalent; the x must be within ()SUBSTR(x,1,4)(x).SUBSTR(1,4)# Chained function call equivalentSTRPOS(x,'pattern string')(x).STRPOS('pattern string')# Chained function call equivalentFUNC(x,y,named_argument=>z)# Some function that meets the chained function call requirements(x).FUNC(y,named_argument=>z)# Chained function call equivalentARRAY_CONCAT(array1,array2)(array1).ARRAY_CONCAT(array2)# Chained function call equivalentSELECTSAFE.LEFT(x,count)ASresult;# Multi-part function nameSELECT(x).(SAFE.LEFT)(count)ASresult;# Chained function call equivalent

Here are chained function call examples with multiple function calls:

SELECT"Two birds and one mouse".REPLACE("bird","dog").REPLACE("mouse","cat")ASresult;/*----------------------+ |      result          | +----------------------+ | Two dogs and one cat | +----------------------*/

The following examples result in errors because the function being calleddoesn't meet the necessary requirements.

FUNC(named_argument=>x).# Some function(x).FUNC()# Error: The first argument can't be a named argument.CAST(xASINT64)(x).CAST(ASINT64)# Error: CAST syntax isn't supported in chained function calls.GROUPING(x)(x).GROUPING()# Error: The argument isn't an expression.

SAFE. prefix

Syntax:

SAFE.function_name()

Description

If you begin a function withtheSAFE. prefix, it will returnNULL instead of an error.TheSAFE. prefix only prevents errors from the prefixed functionitself: it doesn't prevent errors that occur while evaluating argumentexpressions. TheSAFE. prefix only prevents errors that occur because of thevalue of the function inputs, such as "value out of range" errors; othererrors, such as internal or system errors, may still occur. If the functiondoesn't return an error,SAFE. has no effect on the output.

Exclusions

• Operators, such as+ and=, don't support theSAFE. prefix. To prevent errors from adivision operation, useSAFE_DIVIDE.
• Some operators, such asIN,ARRAY, andUNNEST, resemble functions butdon't support theSAFE. prefix.
• TheCAST andEXTRACT functions don't support theSAFE.prefix. To prevent errors from casting, useSAFE_CAST.

Example

In the following example, the first use of theSUBSTR function would normallyreturn an error, because the function doesn't support length arguments withnegative values. However, theSAFE. prefix causes the function to returnNULL instead. The second use of theSUBSTR function provides the expectedoutput: theSAFE. prefix has no effect.

SELECTSAFE.SUBSTR('foo',0,-2)ASsafe_outputUNIONALLSELECTSAFE.SUBSTR('bar',0,2)ASsafe_output;/*-------------+ | safe_output | +-------------+ | NULL        | | ba          | +-------------*/

Supported functions

BigQuery supports the use of theSAFE. prefix with mostscalar functions that can raise errors, includingSTRING functions,math functions,DATE functions,DATETIME functions,TIMESTAMP functions, andJSON functions.BigQuery does not support the use of theSAFE. prefix withaggregate,window, oruser-defined functions.

Calling persistent user-defined functions (UDFs)

Aftercreating a persistent UDF,you can call it as you would any other function, prepended with the name ofthe 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 runthe query,project_name is required.

Examples

The following example creates a UDF namedmultiply_by_three and calls itfrom the same project.

CREATEFUNCTIONmy_dataset.multiply_by_three(xINT64)AS(x*3);SELECTmy_dataset.multiply_by_three(5)ASresult;-- returns 15

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

CREATE`other_project`.other_dataset.other_function(xINT64,yINT64)AS(x*y*2);SELECT`other_project`.other_dataset.other_function(3,4);--returns24