Stay organized with collections
Save and categorize content based on your preferences.
When you call a function, specific rules may apply. You can also add the
SAFE. 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 unless
explicitly indicated otherwise in the function description:
If an operand is NULL, the function result is NULL.
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 and
procedures. These arguments are called named arguments. An argument that isn't
named is called a positional argument.
Named arguments are optional, unless specified as required in the
function 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 added
before a named argument.
Examples
These examples reference a function called CountTokensInText, which counts
the number of tokens in a paragraph. The function signature looks like this:
CountTokensInText contains three arguments: paragraph, tokens, and
delimiters. paragraph represents a body of text to analyze,
tokens represents the tokens to search for in the paragraph,
and delimiters represents the characters that specify a boundary
between tokens in the paragraph.
This is a query that includes CountTokensInText
without 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. This
works:
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 positional
arguments 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 use tokens as a positional argument, any arguments that appear
before it in the function signature must also be positional arguments.
If you try to use a named argument for paragraph and a positional
argument for tokens, 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=>' .,!?()')
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 doesn't 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
doesn't return an error, SAFE. has no effect on the output.
Exclusions
Operators, such as + and =, don't support the
SAFE. prefix. To prevent errors from a
division operation, use SAFE_DIVIDE.
Some operators, such as ARRAY, and UNNEST, resemble functions but
don't support the SAFE. prefix.
The CAST and EXTRACT functions don't 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 doesn't 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.
[[["Easy to understand","easyToUnderstand","thumb-up"],["Solved my problem","solvedMyProblem","thumb-up"],["Other","otherUp","thumb-up"]],[["Hard to understand","hardToUnderstand","thumb-down"],["Incorrect information or sample code","incorrectInformationOrSampleCode","thumb-down"],["Missing the information/samples I need","missingTheInformationSamplesINeed","thumb-down"],["Other","otherDown","thumb-down"]],["Last updated 2025-08-25 UTC."],[[["\u003cp\u003eFunctions called without a specified time zone will default to using UTC, and if any operand within a function is \u003ccode\u003eNULL\u003c/code\u003e, the function's result will also be \u003ccode\u003eNULL\u003c/code\u003e.\u003c/p\u003e\n"],["\u003cp\u003eNamed arguments, which are specified using \u003ccode\u003eargument_name => value\u003c/code\u003e, are optional in function calls, allow for out-of-order specification, but cannot be mixed with positional arguments after they've been declared.\u003c/p\u003e\n"],["\u003cp\u003ePositional arguments must precede named arguments in a function call; once a named argument is used, all subsequent arguments must also be named, and failing to adhere will result in errors.\u003c/p\u003e\n"],["\u003cp\u003eThe \u003ccode\u003eSAFE.\u003c/code\u003e prefix, when applied to a function, prevents certain types of errors by returning \u003ccode\u003eNULL\u003c/code\u003e instead of generating an error, though it does not prevent errors in argument expressions or other error types.\u003c/p\u003e\n"],["\u003cp\u003eOperators like \u003ccode\u003e+\u003c/code\u003e and \u003ccode\u003e=\u003c/code\u003e, as well as functions like \u003ccode\u003eARRAY\u003c/code\u003e, \u003ccode\u003eCAST\u003c/code\u003e, and \u003ccode\u003eEXTRACT\u003c/code\u003e do not support the \u003ccode\u003eSAFE.\u003c/code\u003e prefix, requiring alternative error-handling methods like \u003ccode\u003eSAFE_DIVIDE\u003c/code\u003e or \u003ccode\u003eSAFE_CAST\u003c/code\u003e.\u003c/p\u003e\n"]]],[],null,["# Function calls\n\nWhen you call a function, specific rules may apply. You can also add the\n`SAFE.` prefix, which prevents functions from generating some types of errors.\nTo learn more, see the next sections.\n\nFunction call rules\n-------------------\n\nThe following rules apply to all built-in GoogleSQL functions unless\nexplicitly indicated otherwise in the function description:\n\n- If an operand is `NULL`, the function result is `NULL`.\n- For functions that are time zone sensitive, the default time zone, UTC, is used when a time zone isn't specified.\n\nNamed arguments\n---------------\n\n named_argument =\u003e value\n\nYou can provide parameter arguments by name when calling some functions and\nprocedures. These arguments are called *named arguments* . An argument that isn't\nnamed is called a *positional argument*.\n\n- Named arguments are optional, unless specified as required in the function signature.\n- Named arguments don't need to be in order.\n- You can specify positional arguments before named arguments.\n- You can't specify positional arguments after named arguments.\n- An optional positional argument that isn't used doesn't need to be added before a named argument.\n\n**Examples**\n\nThese examples reference a function called `CountTokensInText`, which counts\nthe number of tokens in a paragraph. The function signature looks like this: \n\n CountTokensInText(paragraph STRING, tokens ARRAY\u003cSTRING\u003e, delimiters STRING)\n\n`CountTokensInText` contains three arguments: `paragraph`, `tokens`, and\n`delimiters`. `paragraph` represents a body of text to analyze,\n`tokens` represents the tokens to search for in the paragraph,\nand `delimiters` represents the characters that specify a boundary\nbetween tokens in the paragraph.\n\nThis is a query that includes `CountTokensInText`\nwithout named arguments: \n\n SELECT token, count\n FROM CountTokensInText(\n 'Would you prefer softball, baseball, or tennis? There is also swimming.',\n ['baseball', 'football', 'tennis'],\n ' .,!?()')\n\nThis is the query with named arguments: \n\n SELECT token, count\n FROM CountTokensInText(\n paragraph =\u003e 'Would you prefer softball, baseball, or tennis? There is also swimming.',\n tokens =\u003e ['baseball', 'football', 'tennis'],\n delimiters =\u003e ' .,!?()')\n\nIf named arguments are used, the order of the arguments doesn't matter. This\nworks: \n\n SELECT token, count\n FROM CountTokensInText(\n tokens =\u003e ['baseball', 'football', 'tennis'],\n delimiters =\u003e ' .,!?()',\n paragraph =\u003e 'Would you prefer softball, baseball, or tennis? There is also swimming.')\n\nYou can mix positional arguments and named arguments, as long as the positional\narguments in the function signature come first: \n\n SELECT token, count\n FROM CountTokensInText(\n 'Would you prefer softball, baseball, or tennis? There is also swimming.',\n tokens =\u003e ['baseball', 'football', 'tennis'],\n delimiters =\u003e ' .,!?()')\n\nThis doesn't work because a positional argument appears after a named argument: \n\n SELECT token, count\n FROM CountTokensInText(\n paragraph =\u003e 'Would you prefer softball, baseball, or tennis? There is also swimming.',\n ['baseball', 'football', 'tennis'],\n delimiters =\u003e ' .,!?()')\n\nIf you want to use `tokens` as a positional argument, any arguments that appear\nbefore it in the function signature must also be positional arguments.\nIf you try to use a named argument for `paragraph` and a positional\nargument for `tokens`, this will not work. \n\n -- This doesn't work.\n SELECT token, count\n FROM CountTokensInText(\n ['baseball', 'football', 'tennis'],\n delimiters =\u003e ' .,!?()',\n paragraph =\u003e 'Would you prefer softball, baseball, or tennis? There is also swimming.')\n\n -- This works.\n SELECT token, count\n FROM CountTokensInText(\n 'Would you prefer softball, baseball, or tennis? There is also swimming.',\n ['baseball', 'football', 'tennis'],\n delimiters =\u003e ' .,!?()')\n\nSAFE. prefix\n------------\n\n**Syntax:** \n\n SAFE.function_name()\n\n**Description**\n\nIf you begin a function with\nthe `SAFE.` prefix, it will return `NULL` instead of an error.\nThe `SAFE.` prefix only prevents errors from the prefixed function\nitself: it doesn't prevent errors that occur while evaluating argument\nexpressions. The `SAFE.` prefix only prevents errors that occur because of the\nvalue of the function inputs, such as \"value out of range\" errors; other\nerrors, such as internal or system errors, may still occur. If the function\ndoesn't return an error, `SAFE.` has no effect on the output.\n\n**Exclusions**\n\n- [Operators](/bigtable/docs/reference/sql/operators), such as `+` and `=`, don't support the `SAFE.` prefix. To prevent errors from a division operation, use [SAFE_DIVIDE](/bigtable/docs/reference/sql/mathematical_functions#safe_divide).\n- Some operators, such as `ARRAY`, and `UNNEST`, resemble functions but don't support the `SAFE.` prefix.\n- The `CAST` and `EXTRACT` functions don't support the `SAFE.` prefix. To prevent errors from casting, use [SAFE_CAST](/bigtable/docs/reference/sql/conversion_functions#safe_casting).\n\n**Example**\n\nIn the following example, the first use of the `SUBSTR` function would normally\nreturn an error, because the function doesn't support length arguments with\nnegative values. However, the `SAFE.` prefix causes the function to return\n`NULL` instead. The second use of the `SUBSTR` function provides the expected\noutput: the `SAFE.` prefix has no effect. \n\n SELECT SAFE.SUBSTR('foo', 0, -2) AS safe_output UNION ALL\n SELECT SAFE.SUBSTR('bar', 0, 2) AS safe_output;\n\n /*-------------*\n | safe_output |\n +-------------+\n | NULL |\n | ba |\n *-------------*/"]]