REGEXP_MATCH(str, reg_exp)

str が正規表現に合致する場合にtrue を返します。正規表現ではない文字列マッチングの場合にはREGEXP_MATCH の替わりにCONTAINS を使います。
Note：この関数はUseLegacySQL=True の場合のみ有効です。

• str: 正規表現の合致する文字列です。
• reg_exp: 合致する正規表現。

CAST(expression AS type)

Cast is used in a query to indicate that the result type of an expression should be converted to some other type.

• expression: The expression to cast.
• type: The type to cast the expression to.

SAFE_CAST(expression, type)

Cast is used in a query to indicate that the result type of an expression should be converted to some other type.SAFE_CAST is identical to CAST, except it returns NULL instead of raising an error.

• expression: The expression to cast.
• type: The type to cast the expression to.

CURRENT_DATE()

Returns a human-readable string of the current date in the format %Y-%m-%d.

DATE(timestamp [, timezone])

Converts a timestamp_expression to a DATE data type.
Note: this function is only available when using Standard SQL (UseLegacySQL=False).

• timestamp: The timestamp from which to return the date.
• timezone: The timezone to use when converting the timestamp. If not specified, the default timezone, UTC, is used.

DATEDIFF(timestamp1, timestamp2)

Returns the number of days between two TIMESTAMP data types. The result is positive if the first TIMESTAMP data type comes after the second TIMESTAMP data type, and otherwise the result is negative.
Note: this function is only available when UseLegacySQL=True.

• timestamp1: The first timestamp.
• timestamp2: The second timestamp.

DATE_DIFF(date1, date2, date_part)

Computes the number of specified date_part differences between two date expressions. This can be thought of as the number of date_part boundaries crossed between the two dates. If the first date occurs before the second date, then the result is negative.
Note: this function is only available when using Standard SQL (UseLegacySQL=False).

• date1: The first date.
• date2: The second date.
• date_part: The date part. Supported values are: DAY, MONTH, QUARTER, YEAR.

DATE_TRUNC(date, date_part)

Truncates the date to the specified granularity.
Note: this function is only available when using Standard SQL (UseLegacySQL=False).

• date: The date to truncate.
• date_part: The date part. Supported values are: DAY, WEEK, ISOWEEK, MONTH, QUARTER, YEAR, ISOYEAR.

FORMAT_DATE(format_string, date_expr)

Formats the date_expr according to the specified format_string.
Note: this function is only available when using Standard SQL (UseLegacySQL=False).

• format_string: The format string used to format the date_expr.
• date_expr: The date to format.

PARSE_DATE(format_string, date_string)

Uses a format_string and a string representation of a date to return a DATE object.
Note: this function is only available when using Standard SQL (UseLegacySQL=False).

• format_string: The format string used to parse the date_string.
• date_string: The date string to parse.

CURRENT_DATETIME([timezone])

Returns the current time as a DATETIME object.

• timezone: The timezone to use when retrieving the current datetime object.

DATETIME(timestamp [, timezone])

Constructs a DATETIME object using a TIMESTAMP object.
Note: this function is only available when using Standard SQL (UseLegacySQL=False).

• timestamp: The timestamp from which to return the datetime.
• timezone: The timezone to use when converting the timestamp. If not specified, the default timezone, UTC, is used.

DATETIME_DIFF(datetime1, datetime2, date_part)

Computes the number of specified date_part differences between two date expressions. This can be thought of as the number of date_part boundaries crossed between the two dates. If the first date occurs before the second date, then the result is negative.
Note: this function is only available when using Standard SQL (UseLegacySQL=False).

• datetime1: The first datetime.
• datetime2: The second datetime.
• date_part: The date part. Possible values include: MICROSECOND, MILLISECOND, SECOND, MINUTE, HOUR, DAY, WEEK, MONTH, QUARTER, and YEAR.

DATETIME_TRUNC(datetime, part)

Truncates the datetime to the specified granularity.
Note: this function is only available when using Standard SQL (UseLegacySQL=False).

• date: The datetime to truncate.
• part: The date part. Possible values include: MICROSECOND, MILLISECOND, SECOND, MINUTE, HOUR, DAY, WEEK, ISOWEEK, MONTH, QUARTER, YEAR, and ISOYEAR.

FORMAT_DATETIME(format_string, datetime_expr)

Formats the date_expr according to the specified format_string.
Note: this function is only available when using Standard SQL (UseLegacySQL=False).

• format_string: The format string used to format the date_expr.
• datetime_expr: The datetime to format.

PARSE_DATETIME(format_string, datetime_string)

Uses a format_string and a string representation of a date to return a DATETIME object.
Note: this function is only available when using Standard SQL (UseLegacySQL=False).

• format_string: The format string used to parse the date_string.
• datetime_string: The datetime string to parse.

CURRENT_TIME()

Returns a human-readable string of the server's current time in the format %H:%M:%S.

TIME(timestamp [, timezone])

Constructs a DATETIME object using a TIMESTAMP object.
Note: this function is only available when using Standard SQL (UseLegacySQL=False).

• timestamp: The timestamp from which to return the datetime.
• timezone: The timezone to use when converting the timestamp. If not specified, the default timezone, UTC, is used.

TIME_DIFF(time1, time2, time_part)

Computes the number of specified time_part differences between two time expressions. This can be thought of as the number of time_part boundaries crossed between the two times. If the first time occurs before the second time, then the result is negative.
Note: this function is only available when using Standard SQL (UseLegacySQL=False).

• time1: The first time.
• time2: The second time.
• time_part: The time part. Possible values include: MICROSECOND, MILLISECOND, SECOND, MINUTE, HOUR.

TIME_TRUNC(time, part)

Truncates the time to the specified granularity.
Note: this function is only available when using Standard SQL (UseLegacySQL=False).

• time: The time to truncate.
• part: The time part. Possible values include: MICROSECOND, MILLISECOND, SECOND, MINUTE, HOUR.

FORMAT_TIME(format_string, time_expr)

Formats the date_expr according to the specified format_string.
Note: this function is only available when using Standard SQL (UseLegacySQL=False).

• format_string: The format string used to format the date_expr.
• time_expr: The time to format.

PARSE_TIME(format_string, time_string)

Uses a format_string and a string representation of a time to return a TIME object.
Note: this function is only available when using Standard SQL (UseLegacySQL=False).

• format_string: The format string used to parse the time_string.
• time_string: The time string to parse.

CURRENT_TIMESTAMP()

Returns a TIMESTAMP data type of the server's current time in the format %Y-%m-%d %H:%M:%S.

TIMESTAMP_DIFF(timestamp1, timestamp2, time_part)

Computes the number of specified time_part differences between two timestamp expressions. This can be thought of as the number of time_part boundaries crossed between the two timestamp. If the first timestamp occurs before the second timestamp, then the result is negative.
Note: this function is only available when using Standard SQL (UseLegacySQL=False).

• timestamp1: The first timestamp.
• timestamp2: The second timestamp.
• time_part: The timestamp part. Possible values include: MICROSECOND, MILLISECOND, SECOND, MINUTE, HOUR.

FORMAT_TIMESTAMP(format_string, timestamp_expr)

Formats the date_expr according to the specified format_string.
Note: this function is only available when using Standard SQL (UseLegacySQL=False).

• format_string: The format string used to format the date_expr.
• timestamp_expr: The timestamp to format.

PARSE_TIMESTAMP(format_string, timestamp_string)

Uses a format_string and a string representation of a timestamp to return a TIMESTAMP object.
Note: this function is only available when using Standard SQL (UseLegacySQL=False).

• format_string: The format string used to parse the timestamp_string.
• timestamp_string: The timestamp string to parse.

DAY(timestamp)

Returns the day of the month of a TIMESTAMP data type as an integer between 1 and 31, inclusively.
Note: this function is only available when UseLegacySQL=True.

• timestamp: The timestamp from which to return the day of the month.

DAYOFWEEK(timestamp)

Returns the day of the week of a TIMESTAMP data type as an integer between 1 (Sunday) and 7 (Saturday), inclusively.
Note: this function is only available when UseLegacySQL=True.

• timestamp: The timestamp from which to return the day of the week.

DAYOFYEAR(timestamp)

Returns the day of the year of a TIMESTAMP data type as an integer between 1 and 366, inclusively. The integer 1 refers to January 1.
Note: this function is only available when UseLegacySQL=True.

• timestamp: The timestamp from which to return the day of the year.

FORMAT_UTC_USEC(unix_timestamp)

Returns a human-readable string representation of a UNIX timestamp in the format YYYY-MM-DD HH:MM:SS.uuuuuu.
Note: this function is only available when UseLegacySQL=True.

• timestamp: The unix timestamp to format.

HOUR(timestamp)

Returns the hour of a TIMESTAMP data type as an integer between 0 and 23, inclusively.
Note: this function is only available when UseLegacySQL=True.

• timestamp: The timestamp from which to return the hour as an integer.

MINUTE(timestamp)

Returns the minutes of a TIMESTAMP data type as an integer between 0 and 59, inclusively.
Note: this function is only available when UseLegacySQL=True.

• timestamp: The timestamp from which to return the minutes as an integer.

MONTH(timestamp)

Returns the month of a TIMESTAMP data type as an integer between 1 and 12, inclusively.
Note: this function is only available when UseLegacySQL=True.

• timestamp: The timestamp from which to return the month as an integer.

MSEC_TO_TIMESTAMP(unix_timestamp)

Converts a UNIX timestamp in milliseconds to a TIMESTAMP data type.
Note: this function is only available when UseLegacySQL=True.

• timestamp: The unix timestamp to convert.

PARSE_UTC_USEC(date_string)

Converts a date string to a UNIX timestamp in microseconds. date_string must have the format YYYY-MM-DD HH:MM:SS[.uuuuuu]. The fractional part of the second can be up to 6 digits long or can be omitted.
Note: this function is only available when UseLegacySQL=True.

• date_string: The date string to convert.

QUARTER(timestamp)

Returns the quarter of the year of a TIMESTAMP data type as an integer between 1 and 4, inclusively.
Note: this function is only available when UseLegacySQL=True.

• timestamp: The timestamp from which to return the quarter as an integer.

SEC_TO_TIMESTAMP(unix_timestamp)

Converts a UNIX timestamp in seconds to a TIMESTAMP data type.
Note: this function is only available when UseLegacySQL=True.

• unix_timestamp: The unix timestamp to convert.

SECOND(timestamp)

Returns the seconds of a TIMESTAMP data type as an integer between 0 and 59, inclusively. During a leap second, the integer range is between 0 and 60, inclusively.
Note: this function is only available when UseLegacySQL=True.

• timestamp: The timestamp from which to return the second as an integer.

STRFTIME_UTC_USEC(unix_timestamp, date_format_str)

Returns a human-readable date string in the format date_format_str.date_format_str can include date-related punctuation characters (such as / and -) and special characters accepted by the strftime function in C++ (such as %d for day of month).
Note: this function is only available when UseLegacySQL=True.

• unix_timestamp: The unix timestamp to convert.
• date_format_str: The date format string.

TIMESTAMP_SECONDS(unix_timestamp)

Interprets INT64_expression as the number of seconds since 1970-01-01 00:00:00 UTC.
Note: this function is only available when using Standard SQL (UseLegacySQL=False).

• timestamp: The unix timestamp to convert.

TIMESTAMP_MILLIS(unix_timestamp)

Interprets INT64_expression as the number of milliseconds since 1970-01-01 00:00:00 UTC.
Note: this function is only available when using Standard SQL (UseLegacySQL=False).

• timestamp: The unix timestamp to convert.

TIMESTAMP_MICROS(unix_timestamp)

Interprets INT64_expression as the number of microseconds since 1970-01-01 00:00:00 UTC.
Note: this function is only available when using Standard SQL (UseLegacySQL=False).

• timestamp: The unix timestamp to convert.

TIMESTAMP_TO_MSEC(timestamp)

Converts a TIMESTAMP data type to a UNIX timestamp in milliseconds.
Note: this function is only available when UseLegacySQL=True.

• timestamp: The timestamp to convert.

TIMESTAMP_TO_SEC(timestamp)

Converts a TIMESTAMP data type to a UNIX timestamp in seconds.
Note: this function is only available when UseLegacySQL=True.

• timestamp: The timestamp to convert.

TIMESTAMP_TO_USEC(timestamp)

Converts a TIMESTAMP data type to a UNIX timestamp in microseconds.
Note: this function is only available when UseLegacySQL=True.

• timestamp: The timestamp to convert.

UNIX_DATE(date_string)

Returns the number of days since 1970-01-01.
Note: this function is only available when using Standard SQL (UseLegacySQL=False).

• date_string: The date string to convert.

UNIX_SECONDS(timestamp)

Returns the number of seconds since 1970-01-01 00:00:00 UTC. Truncates higher levels of precision.
Note: this function is only available when using Standard SQL (UseLegacySQL=False).

• timestamp: The timestamp to convert.

UNIX_MILLIS(timestamp)

Returns the number of milliseconds since 1970-01-01 00:00:00 UTC. Truncates higher levels of precision.
Note: this function is only available when using Standard SQL (UseLegacySQL=False).

• timestamp: The timestamp to convert.

UNIX_MICROS(timestamp)

Returns the number of microseconds since 1970-01-01 00:00:00 UTC. Truncates higher levels of precision.
Note: this function is only available when using Standard SQL (UseLegacySQL=False).

• timestamp: The timestamp to convert.

USEC_TO_TIMESTAMP(unix_timestamp)

Converts a UNIX timestamp in microseconds to a TIMESTAMP data type.
Note: this function is only available when UseLegacySQL=True.

• unix_timestamp: The unix timestamp to convert.

UTC_USEC_TO_DAY(unix_timestamp)

Shifts a UNIX timestamp in microseconds to the beginning of the day it occurs in.
Note: this function is only available when UseLegacySQL=True.

• unix_timestamp: The unix timestamp to shift.

UTC_USEC_TO_HOUR(unix_timestamp)

Shifts a UNIX timestamp in microseconds to the beginning of the hour it occurs in.
Note: this function is only available when UseLegacySQL=True.

• unix_timestamp: The unix timestamp to shift.

UTC_USEC_TO_MONTH(unix_timestamp)

Shifts a UNIX timestamp in microseconds to the beginning of the month it occurs in.
Note: this function is only available when UseLegacySQL=True.

• unix_timestamp: The unix timestamp to shift.

UTC_USEC_TO_WEEK(unix_timestamp, day_of_week)

Returns a UNIX timestamp in microseconds that represents a day in the week of the unix_timestamp argument.
Note: this function is only available when UseLegacySQL=True.

• unix_timestamp: The unix timestamp to shift.
• day_of_week: A day of the week from 0 (Sunday) to 6 (Saturday).

UTC_USEC_TO_YEAR(unix_timestamp)

Returns a UNIX timestamp in microseconds that represents the year of the unix_timestamp argument.
Note: this function is only available when UseLegacySQL=True.

• unix_timestamp: The unix timestamp to convert.

WEEK(timestamp)

Returns the week of a TIMESTAMP data type as an integer between 1 and 53, inclusively. Weeks begin on Sunday, so if January 1 is on a day other than Sunday, week 1 has fewer than 7 days and the first Sunday of the year is the first day of week 2.
Note: this function is only available when UseLegacySQL=True.

• timestamp: The timestamp from which to return the week as an integer.

YEAR(timestamp)

Returns the year of a TIMESTAMP data type.
Note: this function is only available when UseLegacySQL=True.

• timestamp: The timestamp from which to return the year as an integer.

ABS(expression)

Returns the absolute value of the argument.
Note: this function is only available when UseLegacySQL=True.

• expression: Any column or literal expression.

ACOS(expression)

Returns the arc cosine of the argument.

• expression: Any column or literal expression.

ACOSH(expression)

Returns the arc hyperbolic cosine of the argument.

• expression: Any column or literal expression.

ASIN(expression)

Returns arcsine in radians.

• expression: Any column or literal expression.

ASINH(expression)

Returns the arc hyperbolic sine of the argument.

• expression: Any column or literal expression.

ATAN(expression)

Returns arc tangent of the argument.

• expression: Any column or literal expression.

ATANH(expression)

Returns the arc hyperbolic tangent of the argument.

• expression: Any column or literal expression.

ATAN2(expression1, expression2)

Returns the arc tangent of the two arguments.

• expression1: Any column or literal expression.
• expression2: Any column or literal expression.

CEIL(expression)

Rounds the argument up to the nearest whole number and returns the rounded value.

• expression: Any column or literal expression.

CEILING(expression)

Synonym for CEIL function.

• expression: Any column or literal expression.

COS(expression)

Returns the cosine of the argument.

• expression: Any column or literal expression.

COSH(expression)

Returns the hyperbolic cosine of the argument.

• expression: Any column or literal expression.

DEGREES(expression)

Returns expression, converted from radians to degrees.
Note: this function is only available when UseLegacySQL=True.

• expression: Any column or literal expression.

EXP(expression)

Returns the result of raising the constant "e" - the base of the natural logarithm - to the power of expression.
Note: this function is only available when UseLegacySQL=True.

• expression: Any column or literal expression.

FLOOR(expression)

Rounds the argument down to the nearest whole number and returns the rounded value.

• expression: Any column or literal expression.

LN(expression)

Returns the natural logarithm of the argument.
Note: this function is only available when UseLegacySQL=True.

• expression: Any column or literal expression.

LOG(expression)

Returns the natural logarithm of the argument.
Note: this function is only available when UseLegacySQL=True.

• expression: Any column or literal expression.

LOG2(expression)

Returns the Base-2 logarithm of the argument.
Note: this function is only available when UseLegacySQL=True.

• expression: Any column or literal expression.

LOG10(expression)

Returns the Base-10 logarithm of the argument.
Note: this function is only available when UseLegacySQL=True.

• expression: Any column or literal expression.

PI()

Returns PI.
Note: this function is only available when UseLegacySQL=True.

POW(expression1, expression2)

Returns the result of raising expression1 to the power of expression2.

• expression1: Any column or literal expression.
• expression2: Any column or literal expression.

POWER(expression1, expression2)

Synonym of POW function.

• expression1: Any column or literal expression.
• expression2: Any column or literal expression.

RADIANS(expression)

Returns expression, converted from degrees to radians.
Note: this function is only available when UseLegacySQL=True.

• expression: Any column or literal expression.

RAND([expression])

Returns a random float value in the range 0.0 >= value < 1.0. Each int32_seed value always generates the same sequence of random numbers within a given query, as long as you don't use a LIMIT clause. If int32_seed is not specified, BigQuery uses the current timestamp as the seed value.
Note: this function is only available when UseLegacySQL=True.

• expression: Any column or literal expression.

ROUND(expression [, integer_digits])

Rounds the argument either up or down to the nearest whole number (or if specified, to the specified number of digits) and returns the rounded value.

• expression: Any column or literal expression.
• integer_digits: The number of digits to round to.

GREATEST(value1[, value2 [, ...]])

Returns NULL if any of the inputs is NULL. Otherwise, returns NaN if any of the inputs is NaN. Otherwise, returns the largest value among X1,...,XN according to the < comparison.
Note: this function is only available when using Standard SQL (UseLegacySQL=False).

• value1: The first value to compare.
• value2: The second value to compare.

LEAST(value1[, value2 [, ...]])

Returns NULL if any of the inputs is NULL. Otherwise, returns NaN if any of the inputs is NaN. Otherwise, returns the smallest value among X1,...,XN according to the > comparison.
Note: this function is only available when using Standard SQL (UseLegacySQL=False).

• value1: The first value to compare.
• value2: The second value to compare.

SIN(expression)

Returns the sine of the argument.

• expression: Any column or literal expression.

SINH(expression)

Returns the hyperbolic sine of the argument.

• expression: Any column or literal expression.

SQRT(expression)

Returns the square root of the expression.
Note: this function is only available when UseLegacySQL=True.

• expression: Any column or literal expression.

TAN(expression)

Returns the tangent of the argument.

• expression: Any column or literal expression.

TANH(expression)

Returns the hyperbolic tangent of the argument.

• expression: Any column or literal expression.

TRUNC(expression [, integer_digits])

Rounds X to the nearest integer whose absolute value is not greater than Xs. When the integer_digits parameter is specified this function is similar to ROUND(X, N) but always rounds towards zero. Unlike ROUND(X, N) it never overflows.
Note: this function is only available when using Standard SQL (UseLegacySQL=False).

• expression: Any column or literal expression.
• integer_digits: The number of digits to round to.

BYTE_LENGTH(str)

Returns the length of the value in bytes, regardless of whether the type of the value is STRING or BYTES.
Note: this function is only available when using Standard SQL (UseLegacySQL=False).

• str: The string to calculate the length on.

CHAR_LENGTH(str)

Returns the length of the STRING in characters.
Note: this function is only available when using Standard SQL (UseLegacySQL=False).

• str: The string to calculate the length on.

CONCAT(str1, str2 [, str3] [, ...])

Returns the concatenation of two or more strings, or NULL if any of the values are NULL.

• str1: The first string to concatenate.
• str2: The second string to concatenate.
• str3: The third string to concatenate.

ENDS_WITH(str1, str2)

Takes two values. Returns TRUE if the second value is a suffix of the first.
Note: this function is only available when using Standard SQL (UseLegacySQL=False).

• str1: The string to search in.
• str2: The string to search for.

FROM_BASE64(string_expr)

Converts the base64-encoded input string_expr into BYTES format. To convert BYTES to a base64-encoded STRING, use TO_BASE64.
Note: this function is only available when using Standard SQL (UseLegacySQL=False).

• string_expr: The string to convert from base64 encoding.

FROM_HEX(string_expr)

Converts a hexadecimal-encoded STRING into BYTES format. Returns an error if the input STRING contains characters outside the range (0..9, A..F, a..f). The lettercase of the characters does not matter. To convert BYTES to a hexadecimal-encoded STRING, use TO_HEX.
Note: this function is only available when using Standard SQL (UseLegacySQL=False).

• string_expr: The string to convert from hexadecimal encoding.

INSTR(str1, str2)

Returns the one-based index of the first occurrence of str2 in str1, or returns 0 if str2 does not occur in str1.
Note: this function is only available when UseLegacySQL=True.

• str1: The string to search in.
• str2: The string to search for.

LEFT(str, numeric_expression)

Returns the leftmost numeric_expr characters of str. If the number is longer than str, the full string will be returned. Example: LEFT('seattle', 3) returns sea.
Note: this function is only available when UseLegacySQL=True.

• str: The string to perform the LEFT operation on.
• numeric_expression: The number of characters to return.

LENGTH(str)

Returns a numerical value for the length of the string. Example: if str is '123456', LENGTH returns 6.

• str: The string to calculate the length on.

LOWER(str)

Returns the original string with all characters in lower case.

• str: The string to lower.

LPAD(str1, numeric_expression[, str2])

Pads str1 on the left with str2, repeating str2 until the result string is exactly numeric_expr characters. Example: LPAD('1', 7, '?') returns ??????1.

• str1: The string to pad.
• numeric_expression: The number of str2 instances to pad.
• str2: The pad characters.

LTRIM(str1 [, str2])

Removes characters from the left side of str1. If str2 is omitted, LTRIM removes spaces from the left side of str1. Otherwise, LTRIM removes any characters in str2 from the left side of str1 (case-sensitive).

• str1: The string to trim.
• str2: The characters to trim from str1.

REPEAT(str, repetitions)

Returns a value that consists of original_value, repeated. The repetitions parameter specifies the number of times to repeat original_value. Returns NULL if either original_value or repetitions are NULL. This function return an error if the repetitions value is negative.
Note: this function is only available when using Standard SQL (UseLegacySQL=False).

• str: The string to repeat.
• str2: The number of repititions.

REPLACE(original_value, from_value, to_value)

Replaces all instances of from_value within original_value with to_value.

• original_value: The string to search in.
• from_value: The string to search for.
• to_value: The string to replace instances of from_value.

REVERSE(str)

Returns the reverse of the input STRING or BYTES.
Note: this function is only available when using Standard SQL (UseLegacySQL=False).

• str: The string to reverse.

RIGHT(str, numeric_expression)

Returns the rightmost numeric_expr characters of str. If the number is longer than the string, it will return the whole string. Example: RIGHT('kirkland', 4) returns land.
Note: this function is only available when UseLegacySQL=True.

• str: The string to perform the RIGHT operation on.
• numeric_expression: The number of characters to return.

RPAD(str1, numeric_expression, str2)

Pads str1 on the right with str2, repeating str2 until the result string is exactly numeric_expr characters. Example: RPAD('1', 7, '?') returns 1??????.

• str1: The string to pad.
• numeric_expression: The number of str2 instances to pad.
• str2: The pad characters.

RTRIM(str1 [, str2])

Removes trailing characters from the right side of str1. If str2 is omitted, RTRIM removes trailing spaces from str1. Otherwise, RTRIM removes any characters in str2 from the right side of str1 (case-sensitive).

• str1: The string to trim.
• str2: The characters to trim from str1.

SPLIT(str [, delimiter])

Splits a string into repeated substrings. If delimiter is specified, the SPLIT function breaks str into substrings, using delimiter as the delimiter.

• str: The string to split.
• delimiter: The delimiter to split the string on. Default delimiter is a comma (,).

STARTS_WITH(str1, str2)

Takes two values. Returns TRUE if the second value is a prefix of the first.
Note: this function is only available when using Standard SQL (UseLegacySQL=False).

• str1: The string to search in.
• str2: The string to search for.

STRPOS(str1, str2)

Returns the 1-based index of the first occurrence of value2 inside value1. Returns 0 if value2 is not found.
Note: this function is only available when using Standard SQL (UseLegacySQL=False).

• str1: The string to search in.
• str2: The string to search for.

SUBSTR(str, index [, max_len])

Returns a substring of str, starting at index. If the optional max_len parameter is used, the returned string is a maximum of max_len characters long. Counting starts at 1, so the first character in the string is in position 1 (not zero). If index is 5, the substring begins with the 5th character from the left in str. If index is -4, the substring begins with the 4th character from the right in str. Example: SUBSTR('awesome', -4, 4) returns the substring some.

• str: The original string.
• index: The starting index.
• max_len: The maximum length of the return string.

TO_BASE64(string_expr)

Converts a sequence of BYTES into a base64-encoded STRING. To convert a base64-encoded STRING into BYTES, use FROM_BASE64.
Note: this function is only available when using Standard SQL (UseLegacySQL=False).

• string_expr: The string to convert to base64 encoding.

TO_HEX(string_expr)

Converts a sequence of BYTES into a hexadecimal STRING. Converts each byte in the STRING as two hexadecimal characters in the range (0..9, a..f). To convert a hexadecimal-encoded STRING to BYTES, use FROM_HEX.
Note: this function is only available when using Standard SQL (UseLegacySQL=False).

• string_expr: The string to convert to hexadecimal encoding.

TRIM(str1 [, str2])

Removes all leading and trailing characters that match value2. If value2 is not specified, all leading and trailing whitespace characters (as defined by the Unicode standard) are removed. If the first argument is of type BYTES, the second argument is required. If value2 contains more than one character or byte, the function removes all leading or trailing characters or bytes contained in value2.
Note: this function is only available when using Standard SQL (UseLegacySQL=False).

• str1: The string to trim.
• str2: The optional string characters to trim from str1.

UPPER(str)

Returns the original string with all characters in upper case.

• str: The string to upper.

JSON_EXTRACT(json, json_path)

Selects a value in json according to the JSONPath expression json_path. json_path must be a string constant. Returns the value in JSON string format.

• json: The JSON to select a value from.
• json_path: The JSON path of the value contained in json.

JSON_EXTRACT_SCALAR(json, json_path)

Selects a value in json according to the JSONPath expression json_path. json_path must be a string constant, and bracket notation is not supported. Returns a scalar JSON value.

• json: The JSON to select a value from.
• json_path: The JSON path of the value contained in json.

REGEXP_CONTAINS(str, reg_exp)

Returns TRUE if value is a partial match for the regular expression, regex. You can search for a full match by using ^ (beginning of text) and $ (end of text). If the regex argument is invalid, the function returns an error.
Note: this function is only available when UseLegacySQL=True.

• str: The string to match in the regular expression.
• reg_exp: The regular expression to match.

REGEXP_EXTRACT(str, reg_exp)

Returns the portion of str that matches the capturing group within the regular expression.

• str: The string to match in the regular expression.
• reg_exp: The regular expression to match.

REGEXP_EXTRACT_ALL(str, reg_exp)

Returns an array of all substrings of value that match the regular expression, regex. The REGEXP_EXTRACT_ALL function only returns non-overlapping matches. For example, using this function to extract ana from banana returns only one substring, not two.
Note: this function is only available when using Standard SQL (UseLegacySQL=False).

• str: The string to match in the regular expression.
• reg_exp: The regular expression to match.

REGEXP_REPLACE(orig_str, reg_exp, replace_str)

Returns a string where any substring of orig_str that matches reg_exp is replaced with replace_str. For example, REGEXP_REPLACE ('Hello', 'lo', 'p') returns Help.

• orig_str: The original string to match in the regular expression.
• reg_exp: The regular expression to match.
• replace_str: The replacement for the matched orig_str in the regular expression.

FORMAT_IP(integer_value)

Converts 32 least significant bits of integer_value to human-readable IPv4 address string.
Note: this function is only available when UseLegacySQL=True.

• integer_value: The integer value to convert to an IPv4 address.

PARSE_IP(readable_ip)

Converts a string representing IPv4 address to unsigned integer value. For example, PARSE_IP('0.0.0.1') will return 1. If string is not a valid IPv4 address, PARSE_IP will return NULL.
Note: this function is only available when UseLegacySQL=True.

• readable_ip: The IPv4 address to convert to an integer.

NET.IPV4_FROM_INT64(integer_value)

Converts an IPv4 address from integer format to binary (BYTES) format in network byte order. In the integer input, the least significant bit of the IP address is stored in the least significant bit of the integer, regardless of host or client architecture. For example, 1 means 0.0.0.1, and 0x1FF means 0.0.1.255.
Note: this function is only available when using Standard SQL (UseLegacySQL=False).

• integer_value: The integer value to convert to an IPv4 address.

NET.IPV4_TO_INT64(readable_ip)

Converts an IPv4 address from binary (BYTES) format in network byte order to integer format. In the integer output, the least significant bit of the IP address is stored in the least significant bit of the integer, regardless of host or client architecture. For example, 1 means 0.0.0.1, and 0x1FF means 0.0.1.255. The output is in the range [0, 0xFFFFFFFF]. If the input length is not 4, this function throws an error. This function does not support IPv6.
Note: this function is only available when using Standard SQL (UseLegacySQL=False).

• readable_ip: The IPv4 address to convert to an integer.

FARM_FINGERPRINT(expression)

Computes the fingerprint of the STRING or BYTES input using the Fingerprint64 function from the open-source FarmHash library. The output of this function for a particular input will never change.
Note: this function is only available when using Standard SQL (UseLegacySQL=False).

• expression: The expression to use to compute the fingerprint.

MD5(expression)

Computes the hash of the input using the MD5 algorithm. The input can either be STRING or BYTES. The string version treats the input as an array of bytes. This function returns 16 bytes.
Note: this function is only available when using Standard SQL (UseLegacySQL=False).

• expression: The expression to use to compute the hash.

SHA1(expression)

Computes the hash of the input using the SHA-1 algorithm. The input can either be STRING or BYTES. The string version treats the input as an array of bytes. This function returns 20 bytes.
Note: this function is only available when using Standard SQL (UseLegacySQL=False).

• expression: The expression to use to compute the hash.

SHA256(expression)

Computes the hash of the input using the SHA-256 algorithm. The input can either be STRING or BYTES. The string version treats the input as an array of bytes. This function returns 32 bytes.
Note: this function is only available when using Standard SQL (UseLegacySQL=False).

• expression: The expression to use to compute the hash.

SHA512(expression)

Computes the hash of the input using the SHA-512 algorithm. The input can either be STRING or BYTES. The string version treats the input as an array of bytes. This function returns 64 bytes.
Note: this function is only available when using Standard SQL (UseLegacySQL=False).

• expression: The expression to use to compute the hash.

TIMESTAMP(datetime_expression[, timezone])

Convert a date, datetime, or string to a TIMESTAMP data type.
Note: this function does not support the timezone parameter and requires datetime_expression to be a string when using Legacy SQL (UseLegacySQL=True).

• datetime_expression: The expression to be converted to a timestamp
• timezone: The timezone to be used. If no timezone is specified, the default timezone, UTC, is used