Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
The Google BigQuery API offers additional SQL operators and functions. A complete list of the available syntax is located at: https://cloud.google.com/bigquery/query-reference
A SELECT statement can consist of the following basic clauses.
SELECT
INTO
FROM
JOIN
WHERE
GROUP BY
HAVING
UNION
ORDER BY
LIMIT
The following syntax diagram outlines the syntax supported by the SQL engine of the provider:
Return all columns:
Rename a column:
Cast a column's data as a different data type:
Search data:
The Google BigQuery APIs support the following operators in the WHERE clause: =, >, <, >=, <=, <>, !=, LIKE, NOT LIKE, IN, NOT IN, IS NULL, IS NOT NULL, AND, OR.
Return the number of items matching the query criteria:
Return the unique items matching the query criteria:
Summarize data:
See Aggregate Functions below for details.
Retrieve data from multiple tables.
See JOIN Queries below for details.
Sort a result set in ascending order:
Restrict a result set to the specified number of rows:
Parameterize a query to pass in inputs at execution time. This enables you to create prepared statements and mitigate SQL injection attacks.
The Google BigQuery API offers additional SQL operators and functions. A complete list of the available syntax is located at: https://cloud.google.com/bigquery/query-reference
Below are several examples of SQL aggregate functions. You can use these with a GROUP BY clause to aggregate rows based on the specified GROUP BY criterion. This can be a reporting tool.
Returns the number of rows matching the query criteria.
Returns the average of the column values.
Returns the minimum column value.
Returns the maximum column value.
Returns the total sum of the column values.
Returns the Pearson correlation coefficient of a set of number pairs.
Computes the population covariance of the values computed by a set of number pairs.
Computes the sample covariance of the values computed by a set of number pairs.
Returns the nth sequential value in the scope of the function, where n is a constant. The NTH function starts counting at 1, so there is no zeroth term. If the scope of the function has less than n values, the function returns NULL.
Returns the standard deviation of the computed values. Rows with a NULL value are not included in the calculation.
The provider supports the complete join syntax in Google BigQuery. Google BigQuery supports inner joins, outer joins, and cross joins. The default is inner. Multiple join operations are supported.
Note that the default join is an inner join. The following limitations exist on joins in Google BigQuery:
Cross joins must not contain an ON clause.
Normal joins require that the right-side table must contain less than 8 MB of compressed data. If you are working with tables larger than 8 MB, use the EACH modifier. Note that EACH cannot be used in cross joins.
Returns any value from the input or NULL if there are zero input rows. The value returned is non-deterministic, which means you might receive a different result each time you use this function. Note: this function is only available when using Standard SQL (UseLegacySQL=False).
expression: The expression to retrieve a value from.
Returns the approximate result for COUNT(DISTINCT expression). The value returned is a statistical estimate-not necessarily the actual value. This function is less accurate than COUNT(DISTINCT expression), but performs better on huge input. Note: this function is only available when using Standard SQL (UseLegacySQL=False).
expression: The expression to perform the approximate count distinct on.
Returns the approximate boundaries for a group of expression values, where number represents the number of quantiles to create. This function returns an array of number + 1 elements, where the first element is the approximate minimum and the last element is the approximate maximum. Note: this function is only available when using Standard SQL (UseLegacySQL=False).
expression: The expression to perform the approximate quantiles on.
number: The number of quantiles to create.
Returns the approximate top elements of expression. The number parameter specifies the number of elements returned. Note: this function is only available when using Standard SQL (UseLegacySQL=False).
expression: The expression to perform the approximate top count on.
number: The number of elements to be returned.
Returns the approximate top elements of expression, based on the sum of an assigned weight. The number parameter specifies the number of elements returned. If the weight input is negative or NaN, this function returns an error. Note: this function is only available when using Standard SQL (UseLegacySQL=False).
expression: The expression to perform the approximate top sum on.
weight: The assigned weight.
number: The number of elements to be returned.
The ARRAY function returns an ARRAY with one element for each row in a subquery. Note: this function is only available when using Standard SQL (UseLegacySQL=False).
subquery: The subquery to execute.
Concatenates one or more arrays with the same element type into a single array. Note: this function is only available when using Standard SQL (UseLegacySQL=False).
array_expr1: The first array to concatenate.
array_expr2: The second array to concatenate.
Returns the size of the array. Returns 0 for an empty array. Returns NULL if the array_expression is NULL. Note: this function is only available when using Standard SQL (UseLegacySQL=False).
array_expr: The array expression to get the size of.
Returns a concatenation of the elements in array_expression as a STRING. The value for array_expression can either be an array of STRING or BYTES data types. Note: this function is only available when using Standard SQL (UseLegacySQL=False).
array_expr: The array expression to convert to string.
delimiter: The delimiter string used to delimit the array values.
null_text: If the null_text parameter is used, the function replaces any NULL values in the array with the value of null_text. If the null_text parameter is not used, the function omits the NULL value and its preceding delimiter.
Returns an array of values. The start_expression and end_expression parameters determine the inclusive start and end of the array. Note: this function is only available when using Standard SQL (UseLegacySQL=False).
start_expr: The starting value.
end_expr: The ending value.
step_expr: The increment used to generate array values.
Returns an array of dates. The start_date and end_date parameters determine the inclusive start and end of the array. Note: this function is only available when using Standard SQL (UseLegacySQL=False).
start_date: The starting date.
end_date: The ending date.
int_expr: The increment used to generate dates.
date_part: The date part used to increment the generated dates. Valid values are: DAY, WEEK, MONTH, QUARTER, and YEAR.
Returns the input ARRAY with elements in reverse order. Note: this function is only available when using Standard SQL (UseLegacySQL=False).
array_expr: The array to reverse.
Returns an ARRAY of expression values. Note: this function is only available when using Standard SQL (UseLegacySQL=False).
expression: The expression values to generate an array from.
Concatenates elements from expression of type ARRAY, returning a single ARRAY as a result. This function ignores NULL input arrays, but respects the NULL elements in non-NULL input arrays (an error is raised, however, if an array in the final query result contains a NULL element). Note: this function is only available when using Standard SQL (UseLegacySQL=False).
expression1: The first expression to concatenate.
expression2: The first expression to concatenate.
Returns the average on non-null values. Each distinct value of expression is aggregated only once into the result.
expression: The expression to use to compute the average.
Returns the result of a bitwise AND operation between each instance of numeric_expr across all rows. NULL values are ignored. This function returns NULL if all instances of numeric_expr evaluate to NULL.
numeric_expression: The numeric expression to perform the bitwise operation.
The input, expression, must be an integer or BYTES. Returns the number of bits that are set in the input expression. For integers, this is the number of bits in two's complement form. Note: this function is only available when using Standard SQL (UseLegacySQL=False).
expression: The expression to perform the bit count operation on.
Returns the result of a bitwise OR operation between each instance of numeric_expr across all rows. NULL values are ignored. This function returns NULL if all instances of numeric_expr evaluate to NULL.
numeric_expression: The numeric expression to perform the bitwise operation.
Returns the result of a bitwise XOR operation between each instance of numeric_expr across all rows. NULL values are ignored. This function returns NULL if all instances of numeric_expr evaluate to NULL.
numeric_expression: The numeric expression to perform the bitwise operation.
Returns the Pearson correlation coefficient of a set of number pairs.
numeric_expression1: The first series.
numeric_expression2: The second series.
Returns the count of TRUE values for expression. Returns 0 if there are zero input rows or expression evaluates to FALSE for all rows. Note: this function is only available when using Standard SQL (UseLegacySQL=False).
expression: The expression to evaluate.
Computes the population covariance of the values computed by numeric_expression1 and numeric_expression2.
numeric_expression: The first series.
numeric_expression: The second series.
Computes the sample covariance of the values computed by numeric_expression1 and numeric_expression2.
numeric_expression: The first series.
numeric_expression: The second series.
Returns the first sequential value in the scope of the function. Note: this function is only available when UseLegacySQL=True.
column: Any column expression.
Returns the value of the value_expression for the first row in the current window frame. Note: this function only supports [IGNORE NULLS] when using Standard SQL (UseLegacySQL=False).
value_expression: Any value expression
Concatenates multiple strings into a single string, where each value is separated by the optional separator parameter. If separator is omitted, returns a comma-separated string. Note: this function is only available when UseLegacySQL=True.
string_expression: The string expression to concat.
separator: The separator.
Concatenates multiple strings into a single string, where each value is separated by the optional separator parameter. If separator is omitted, BigQuery returns a comma-separated string. Unlike GROUP_CONCAT, this function will not add double quotes to returned values that include a double quote character. For example, the string a"b would return as a"b. Note: this function is only available when UseLegacySQL=True.
string_expression: The string expression to concat.
separator: The separator.
Returns the last sequential value in the scope of the function. Note: this function is only available when UseLegacySQL=True.
column: Any column expression
Returns the value of the value_expression for the last row in the current window frame. Note: this function only supports [IGNORE NULLS] when using Standard SQL (UseLegacySQL=False).
value_expression: Any value expression
Returns the logical AND of all non-NULL expressions. Returns NULL if there are zero input rows or expression evaluates to NULL for all rows. Note: this function is only available when using Standard SQL (UseLegacySQL=False).
expression: The expression to perform the logical AND on.
Returns the logical OR of all non-NULL expressions. Returns NULL if there are zero input rows or expression evaluates to NULL for all rows. Note: this function is only available when using Standard SQL (UseLegacySQL=False).
expression: The expression to perform the logical OR on.
Aggregates all values in the current aggregation scope into a repeated field. For example, the query SELECT x, NEST(y) FROM ... GROUP BY x returns one output record for each distinct x value, and contains a repeated field for all y values paired with x in the query input. The NEST function requires a GROUP BY clause. Note: this function is only available when UseLegacySQL=True.
expression: Any expression.
Returns the current UNIX timestamp in microseconds. Note: this function is only available when UseLegacySQL=True.
Returns the nth sequential value in the scope of the function, where n is a constant. The NTH function starts counting at 1, so there is no zeroth term. If the scope of the function has less than n values, the function returns NULL. Note: this function is only available when UseLegacySQL=True.
n: The nth sequential value.
field: The column name.
Returns the value of value_expression at the Nth row of the current window frame, where Nth is defined by constant_integer_expression. Returns NULL if there is no such row. Note: this function is only available when using Standard SQL (UseLegacySQL=False).
value_expression: Any value expression.
constant_integer_expression: The nth sequential value.
Computes approximate minimum, maximum, and quantiles for the input expression. NULL input values are ignored. Empty or exclusively-NULL input results in NULL output. The number of quantiles computed is controlled with the optional buckets parameter, which includes the minimum and maximum in the count. Note: this function is only available when UseLegacySQL=True.
expression: The numeric expression to compute quantiles on.
buckets: The number of buckets.
Returns the standard deviation of the values computed by numeric_expr. Rows with a NULL value are not included in the calculation.
numeric_expression: The series to calculate STDDEV on.
Computes the population standard deviation of the value computed by numeric_expr.
numeric_expression: The series to calculate STDDEV on.
Computes the sample standard deviation of the value computed by numeric_expr.
numeric_expression: The series to calculate STDDEV on.
Returns a value (either STRING or BYTES) obtained by concatenating non-null values. If a delimiter is specified, concatenated values are separated by that delimiter; otherwise, a comma is used as a delimiter. Note: this function is only available when using Standard SQL (UseLegacySQL=False).
expression: The string expression to concatenate.
delimiter: The delimiter to separate concatenated values.
Returns the sum on non-null values. Each distinct value of expression is aggregated only once into the result.
expression: The expression to use to compute the sum.
TOP is a function that is an alternative to the GROUP BY clause. It is used as simplified syntax for GROUP BY ... ORDER BY ... LIMIT .... Generally, the TOP function performs faster than the full ... GROUP BY ... ORDER BY ... LIMIT ... query, but may only return approximate results. Note: this function is only available when UseLegacySQL=True.
numeric_expression: The series to calculate STDDEV on.
max_values: The maximum number of results to return. Default is 20.
multiplier: A positive integer that increases the value(s) returned by COUNT(*) by the multiple specified.
Returns the set of unique, non-NULL values in the scope of the function in an undefined order. Note: this function is only available when UseLegacySQL=True.
expression: Any expression.
Computes the variance of the values computed by numeric_expr. Rows with a NULL value are not included in the calculation.
numeric_expression: The series to calculate VARIANCE on.
Computes the population variance of the values computed by numeric_expr.
numeric_expression: The series to calculate VARIANCE on.
Computes the sample variance of the values computed by numeric_expr.
numeric_expression: The series to calculate VARIANCE on.
Returns the ordinal (1-based) rank of each row within the ordered partition. All peer rows receive the same rank value. The next row or set of peer rows receives a rank value which increments by the number of peers with the previous rank value, instead of a rank value which always increments by 1. Note: this function is only available when using Standard SQL (UseLegacySQL=False).
Return the percentile rank of a row defined as (RK-1)/(NR-1), where RK is the RANK of the row and NR is the number of rows in the partition. Returns 0 if NR=1. Note: this function is only available when using Standard SQL (UseLegacySQL=False).
This function divides the rows into constant_integer_expression buckets based on row ordering and returns the 1-based bucket number that is assigned to each row. The number of rows in the buckets can differ by at most 1. The remainder values (the remainder of number of rows divided by buckets) are distributed one for each bucket, starting with bucket 1. If constant_integer_expression evaluates to NULL, 0 or negative, an error is provided. Note: this function is only available when using Standard SQL (UseLegacySQL=False).
constant_integer_expression: The number of buckets to divide the rows into.
Returns the value of the value_expression on a subsequent row. Changing the offset value changes which subsequent row is returned; the default value is 1, indicating the next row in the window frame. An error occurs if offset is NULL or a negative value. Note: this function is only available when using Standard SQL (UseLegacySQL=False).
value_expression: The value expression.
offset: The offset to use. Must be a non-negative integer.
default_expression: The default expression. Must be compatible with the value_expression type.
Returns the value of the value_expression on a subsequent row. Changing the offset value changes which subsequent row is returned; the default value is 1, indicating the next row in the window frame. An error occurs if offset is NULL or a negative value. Note: this function is only available when using Standard SQL (UseLegacySQL=False).
value_expression: The value expression.
offset: The offset to use. Must be a non-negative integer.
default_expression: The default expression. Must be compatible with the value_expression type.
Computes the specified percentile value for the value_expression, with linear interpolation. Note: this function is only available when using Standard SQL (UseLegacySQL=False).
value_expression: A numeric expression.
percentile: A literal in the range [0, 1].
Computes the specified percentile value for a discrete value_expression. The returned value is the first sorted value of value_expression with cumulative distribution greater than or equal to the given percentile value. Note: this function is only available when using Standard SQL (UseLegacySQL=False).
value_expression: Any orderable type.
percentile: A literal in the range [0, 1].
Returns the value of the first non-null expression. The remaining expressions are not evaluated. All input expressions must be implicitly coercible to a common supertype. Note: this function currently accepts up to 9 expressions.
expr1: Any expression
expr2: Any expression
Returns NULL if expression = expression_to_match is true, otherwise returns expression. expression and expression_to_match must be implicitly coercible to a common supertype; equality comparison is done on coerced values. Note: this function is only available when using Standard SQL (UseLegacySQL=False).
expression: Any expression
expression_to_match: Any expression to be matched
Return the relative rank of a row defined as NP/NR. NP is defined to be the number of rows that either precede or are peers with the current row. NR is the number of rows in the partition. Note: this function returns a double when using Legacy SQL (UseLegacySQL=True).
Returns the ordinal (1-based) rank of each row within the window partition. All peer rows receive the same rank value, and the subsequent rank value is incremented by one.
Does not require the ORDER BY clause. Returns the sequential row ordinal (1-based) of each row for each ordered partition. If the ORDER BY clause is unspecified then the result is non-deterministic.
If expr is NULL, return null_result. Otherwise, return expr. If expr is not NULL, null_result is not evaluated. expr and null_result must be implicitly coercible to a common supertype. Synonym for COALESCE(expr, null_result)
expr: Any expression
null_result: The result to return if expr is null
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.
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.
Returns a human-readable string of the current date in the format %Y-%m-%d.
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.
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.
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.
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.
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.
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.
Returns the current time as a DATETIME object.
timezone: The timezone to use when retrieving the current datetime object.
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.
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.
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.
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.
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.
Returns a human-readable string of the server's current time in the format %H:%M:%S.
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.
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.
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.
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.
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.
Returns a TIMESTAMP data type of the server's current time in the format %Y-%m-%d %H:%M:%S.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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).
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.
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.
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.
Returns the absolute value of the argument. Note: this function is only available when UseLegacySQL=True.
expression: Any column or literal expression.
Returns the arc cosine of the argument.
expression: Any column or literal expression.
Returns the arc hyperbolic cosine of the argument.
expression: Any column or literal expression.
Returns arcsine in radians.
expression: Any column or literal expression.
Returns the arc hyperbolic sine of the argument.
expression: Any column or literal expression.
Returns arc tangent of the argument.
expression: Any column or literal expression.
Returns the arc hyperbolic tangent of the argument.
expression: Any column or literal expression.
Returns the arc tangent of the two arguments.
expression1: Any column or literal expression.
expression2: Any column or literal expression.
Rounds the argument up to the nearest whole number and returns the rounded value.
expression: Any column or literal expression.
Synonym for CEIL function.
expression: Any column or literal expression.
Returns the cosine of the argument.
expression: Any column or literal expression.
Returns the hyperbolic cosine of the argument.
expression: Any column or literal expression.
Returns expression, converted from radians to degrees. Note: this function is only available when UseLegacySQL=True.
expression: Any column or literal 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.
Rounds the argument down to the nearest whole number and returns the rounded value.
expression: Any column or literal expression.
Returns the natural logarithm of the argument. Note: this function is only available when UseLegacySQL=True.
expression: Any column or literal expression.
Returns the natural logarithm of the argument. Note: this function is only available when UseLegacySQL=True.
expression: Any column or literal expression.
Returns the Base-2 logarithm of the argument. Note: this function is only available when UseLegacySQL=True.
expression: Any column or literal expression.
Returns the Base-10 logarithm of the argument. Note: this function is only available when UseLegacySQL=True.
expression: Any column or literal expression.
Returns PI. Note: this function is only available when UseLegacySQL=True.
Returns the result of raising expression1 to the power of expression2.
expression1: Any column or literal expression.
expression2: Any column or literal expression.
Synonym of POW function.
expression1: Any column or literal expression.
expression2: Any column or literal expression.
Returns expression, converted from degrees to radians. Note: this function is only available when UseLegacySQL=True.
expression: Any column or literal 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.
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.
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.
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.
Returns the sine of the argument.
expression: Any column or literal expression.
Returns the hyperbolic sine of the argument.
expression: Any column or literal expression.
Returns the square root of the expression. Note: this function is only available when UseLegacySQL=True.
expression: Any column or literal expression.
Returns the tangent of the argument.
expression: Any column or literal expression.
Returns the hyperbolic tangent of the argument.
expression: Any column or literal expression.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
Returns the original string with all characters in lower case.
str: The string to lower.
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.
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.
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.
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.
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.
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.
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.
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.
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 (,).
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.
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.
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.
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.
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.
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.
Returns the original string with all characters in upper case.
str: The string to upper.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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
Returns true if str matches the regular expression. For string matching without regular expressions, use CONTAINS instead of REGEXP_MATCH. 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.
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.
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.
Returns a human-readable string of the current date in the format %Y-%m-%d.
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.
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.
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.
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.
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.
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.
Returns the current time as a DATETIME object.
timezone: The timezone to use when retrieving the current datetime object.
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.
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.
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.
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.
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.
Returns a human-readable string of the server's current time in the format %H:%M:%S.
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.
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.
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.
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.
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.
Returns a TIMESTAMP data type of the server's current time in the format %Y-%m-%d %H:%M:%S.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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).
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.
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.
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.
Returns the absolute value of the argument. Note: this function is only available when UseLegacySQL=True.
expression: Any column or literal expression.
Returns the arc cosine of the argument.
expression: Any column or literal expression.
Returns the arc hyperbolic cosine of the argument.
expression: Any column or literal expression.
Returns arcsine in radians.
expression: Any column or literal expression.
Returns the arc hyperbolic sine of the argument.
expression: Any column or literal expression.
Returns arc tangent of the argument.
expression: Any column or literal expression.
Returns the arc hyperbolic tangent of the argument.
expression: Any column or literal expression.
Returns the arc tangent of the two arguments.
expression1: Any column or literal expression.
expression2: Any column or literal expression.
Rounds the argument up to the nearest whole number and returns the rounded value.
expression: Any column or literal expression.
Synonym for CEIL function.
expression: Any column or literal expression.
Returns the cosine of the argument.
expression: Any column or literal expression.
Returns the hyperbolic cosine of the argument.
expression: Any column or literal expression.
Returns expression, converted from radians to degrees. Note: this function is only available when UseLegacySQL=True.
expression: Any column or literal 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.
Rounds the argument down to the nearest whole number and returns the rounded value.
expression: Any column or literal expression.
Returns the natural logarithm of the argument. Note: this function is only available when UseLegacySQL=True.
expression: Any column or literal expression.
Returns the natural logarithm of the argument. Note: this function is only available when UseLegacySQL=True.
expression: Any column or literal expression.
Returns the Base-2 logarithm of the argument. Note: this function is only available when UseLegacySQL=True.
expression: Any column or literal expression.
Returns the Base-10 logarithm of the argument. Note: this function is only available when UseLegacySQL=True.
expression: Any column or literal expression.
Returns PI. Note: this function is only available when UseLegacySQL=True.
Returns the result of raising expression1 to the power of expression2.
expression1: Any column or literal expression.
expression2: Any column or literal expression.
Synonym of POW function.
expression1: Any column or literal expression.
expression2: Any column or literal expression.
Returns expression, converted from degrees to radians. Note: this function is only available when UseLegacySQL=True.
expression: Any column or literal 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.
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.
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.
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.
Returns the sine of the argument.
expression: Any column or literal expression.
Returns the hyperbolic sine of the argument.
expression: Any column or literal expression.
Returns the square root of the expression. Note: this function is only available when UseLegacySQL=True.
expression: Any column or literal expression.
Returns the tangent of the argument.
expression: Any column or literal expression.
Returns the hyperbolic tangent of the argument.
expression: Any column or literal expression.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
Returns the original string with all characters in lower case.
str: The string to lower.
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.
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.
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.
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.
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.
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.
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.
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.
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 (,).
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.
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.
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.
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.
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.
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.
Returns the original string with all characters in upper case.
str: The string to upper.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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
SELECT {
[ TOP <numeric_literal> | DISTINCT ]
{
*
| {
<expression> [ [ AS ] <column_reference> ]
| { <table_name> | <correlation_name> } .*
} [ , ... ]
}
[ INTO csv:// [ filename= ] <file_path> [ ;delimiter=tab ] ]
{
FROM <table_reference> [ [ AS ] <identifier> ]
} [ , ... ]
[ [
INNER | { { LEFT | RIGHT | FULL } [ OUTER ] }
] JOIN <table_reference> [ ON <search_condition> ] [ [ AS ] <identifier> ]
] [ ... ]
[ WHERE <search_condition> ]
[ GROUP BY <column_reference> [ , ... ]
[ HAVING <search_condition> ]
[ UNION [ ALL ] <select_statement> ]
[
ORDER BY
<column_reference> [ ASC | DESC ] [ NULLS FIRST | NULLS LAST ]
]
[
LIMIT <expression>
[
{ OFFSET | , }
<expression>
]
]
} | SCOPE_IDENTITY()
<expression> ::=
| <column_reference>
| @ <parameter>
| ?
| COUNT( * | { [ DISTINCT ] <expression> } )
| { AVG | MAX | MIN | SUM | COUNT } ( <expression> )
| NULLIF ( <expression> , <expression> )
| COALESCE ( <expression> , ... )
| CASE <expression>
WHEN { <expression> | <search_condition> } THEN { <expression> | NULL } [ ... ]
[ ELSE { <expression> | NULL } ]
END
| <literal>
| <sql_function>
<search_condition> ::=
{
<expression> { = | > | < | >= | <= | <> | != | LIKE | NOT LIKE | IN | NOT IN | IS NULL | IS NOT NULL | AND | OR | CONTAINS | BETWEEN } [ <expression> ]
} [ { AND | OR } ... ]
SELECT * FROM publicdata.samples.github_nested
SELECT [repository.name] AS MY_repository.name FROM publicdata.samples.github_nested
SELECT CAST(repository.watchers AS VARCHAR) AS Str_repository.watchers FROM publicdata.samples.github_nested
SELECT * FROM publicdata.samples.github_nested WHERE repository.name = 'EntityFramework';
SELECT * FROM publicdata.samples.github_nested WHERE repository.name = 'EntityFramework';
SELECT COUNT(*) AS MyCount FROM publicdata.samples.github_nested
SELECT DISTINCT repository.name FROM publicdata.samples.github_nested
SELECT repository.name, MAX(repository.watchers) FROM publicdata.samples.github_nested GROUP BY repository.name
SELECT * FROM CRMAccounts INNER JOIN ERPCustomers ON CRMAccounts.BillingState = ERPCustomers.BillingState
SELECT actor.attributes.email, repository.name FROM publicdata.samples.github_nested ORDER BY repository.name ASC
SELECT actor.attributes.email, repository.name FROM publicdata.samples.github_nested LIMIT 10
SELECT * FROM publicdata.samples.github_nested WHERE repository.name = @param
SELECT COUNT(*) FROM publicdata.samples.github_nested WHERE repository.name = 'EntityFramework'
SELECT repository.name, AVG(repository.watchers) FROM publicdata.samples.github_nested WHERE repository.name = 'EntityFramework'
GROUP BY repository.name
SELECT MIN(repository.watchers), repository.name FROM publicdata.samples.github_nested WHERE repository.name = 'EntityFramework'
GROUP BY repository.name
SELECT repository.name, MAX(repository.watchers) FROM publicdata.samples.github_nested WHERE repository.name = 'EntityFramework'
GROUP BY repository.name
SELECT SUM(repository.watchers) FROM publicdata.samples.github_nested WHERE repository.name = 'EntityFramework'
SELECT repository.name, CORR(repository.watchers, repository.size) FROM publicdata.samples.github_nested
SELECT repository.name, COVAR_POP(repository.watchers, repository.size) FROM publicdata.samples.github_nested
SELECT repository.name, COVAR_SAMP(repository.watchers, repository.size) FROM publicdata.samples.github_nested
SELECT repository.name, NTH(n, actor.attributes.email) FROM publicdata.samples.github_nested
SELECT repository.name, STDDEV(repository.watchers) FROM publicdata.samples.github_nested
SELECT field_1 [..., field_n] FROM table_1 [[AS] alias_1] [[INNER|[FULL|RIGHT|LEFT] OUTER|CROSS] JOIN [EACH] table_2 [[AS] alias_2] [ON join_condition_1 [... AND join_condition_n]] ]+
Coming soon
The provider enables standards-based access to Amazon Redshift based on PostgreSQL 8.0.2 with Amazon Redshift SQL.
A SELECT statement can consist of the following basic clauses.
SELECT
INTO
FROM
JOIN
WHERE
GROUP BY
HAVING
UNION
ORDER BY
LIMIT
The following syntax diagram outlines the syntax supported by the SQL engine of the provider:
Return all columns:
Rename a column:
Cast a column's data as a different data type:
Search data:
Return the number of items matching the query criteria:
Return the number of unique items matching the query criteria:
Return the unique items matching the query criteria:
Summarize data:
See Aggregate Functions below for details.
Retrieve data from multiple tables.
See JOIN Queries below for details.
Sort a result set in ascending order:
Restrict a result set to the specified number of rows:
Parameterize a query to pass in inputs at execution time. This enables you to create prepared statements and mitigate SQL injection attacks.
Returns the number of rows matching the query criteria.
Returns the number of distinct, non-null field values matching the query criteria.
Returns the average of the column values.
Returns the minimum column value.
Returns the maximum column value.
Returns the total sum of the column values.
The Provider for Azure Data Lake Storage supports standard SQL joins like the following examples.
An inner join selects only rows from both tables that match the join condition:
A left join selects all rows in the FROM table and only matching rows in the JOIN table:
The following date literal functions can be used to filter date fields using relative intervals. Note that while the <, >, and = operators are supported for these functions, <= and >= are not.
The current day.
The previous day.
The following day.
Every day in the preceding week.
Every day in the current week.
Every day in the following week.
Also available:
L_LAST/L_THIS/L_NEXT MONTH
L_LAST/L_THIS/L_NEXT QUARTER
L_LAST/L_THIS/L_NEXT YEAR
The previous n days, excluding the current day.
The following n days, including the current day.
Also available:
L_LAST/L_NEXT_90_DAYS
Every day in every week, starting n weeks before current week, and ending in the previous week.
Every day in every week, starting the following week, and ending n weeks in the future.
Also available:
L_LAST/L_NEXT_N_MONTHS(n)
L_LAST/L_NEXT_N_QUARTERS(n)
L_LAST/L_NEXT_N_YEARS(n)
A SELECT statement can consist of the following basic clauses.
SELECT
INTO
FROM
JOIN
WHERE
GROUP BY
HAVING
UNION
ORDER BY
LIMIT
The following syntax diagram outlines the syntax supported by the SQL engine of the provider:
Return all columns:
Rename a column:
Cast a column's data as a different data type:
Search data:
Return the number of items matching the query criteria:
Return the number of unique items matching the query criteria:
Return the unique items matching the query criteria:
Summarize data:
See Aggregate Functions below for details.
Retrieve data from multiple tables.
See JOIN Queries below for details.
Sort a result set in ascending order:
Restrict a result set to the specified number of rows:
Parameterize a query to pass in inputs at execution time. This enables you to create prepared statements and mitigate SQL injection attacks.
Returns the number of rows matching the query criteria.
Returns the number of distinct, non-null field values matching the query criteria.
Returns the average of the column values.
Returns the minimum column value.
Returns the maximum column value.
Returns the total sum of the column values.
The Provider for Databricks supports standard SQL joins like the following examples.
An inner join selects only rows from both tables that match the join condition:
A left join selects all rows in the FROM table and only matching rows in the JOIN table:
The following date literal functions can be used to filter date fields using relative intervals. Note that while the <, >, and = operators are supported for these functions, <= and >= are not.
The current day.
The previous day.
The following day.
Every day in the preceding week.
Every day in the current week.
Every day in the following week.
Also available:
L_LAST/L_THIS/L_NEXT MONTH
L_LAST/L_THIS/L_NEXT QUARTER
L_LAST/L_THIS/L_NEXT YEAR
The previous n days, excluding the current day.
The following n days, including the current day.
Also available:
L_LAST/L_NEXT_90_DAYS
Every day in every week, starting n weeks before current week, and ending in the previous week.
Every day in every week, starting the following week, and ending n weeks in the future.
Also available:
L_LAST/L_NEXT_N_MONTHS(n)
L_LAST/L_NEXT_N_QUARTERS(n)
L_LAST/L_NEXT_N_YEARS(n)
Returns expr rounded to d decimal places using HALF_UP rounding mode.
expr: Any numeric expression.
d: The number of decimal places.
Returns expr rounded to d decimal places using HALF_EVEN rounding mode.
expr: Any numeric expression.
d: The number of decimal places.
Returns the largest integer not greater than expr.
expr: Any numeric expression.
Returns the smallest integer not smaller than expr.
expr: Any numeric expression.
Returns a random value with independent and identically distributed (i.i.d.) uniformly distributed values in [0, 1).
seed: The seed to use to generate the random value.
Returns e to the power of expr.
expr: Any numeric expression.
Returns the natural logarithm (base e) of expr.
expr: Any numeric expression.
Returns the logarithm of expr with base 10.
expr: Any numeric expression.
Returns the logarithm of expr with base 2.
expr: Any numeric expression.
Returns the logarithm of expr with base.
base: A numeric expression to use as the base.
expr: Any numeric expression.
Raises expr1 to the power of expr2.
expr1: Any numeric expression.
expr2: Any numeric expression.
Returns the square root of expr.
expr: Any numeric expression.
Returns the string representation of the long value expr represented in binary.
expr: A long expression.
Converts expr to hexadecimal.
expr: The expression to convert to hex.
Converts hexadecimal expr to binary.
expr: The hexadecimal value to convert to binary.
Convert num from from_base to to_base.
num: The number to convert.
from_base: The original base of num.
to_base: The base to convert num to.
Returns the absolute value of the numeric value.
expr: Any valid numeric expression.
Returns the positive value of expr1 mod expr2.
expr1: Any valid numeric expression.
expr2: Any valid numeric expression.
Returns the sine of expr, as if computed by java.lang.Math.sin.
expr: Any valid numeric expression.
Returns the inverse sine (a.k.a. arc sine) the arc sin of expr, as if computed by java.lang.Math.asin.
expr: Any valid numeric expression.
Returns the cosine of expr, as if computed by java.lang.Math.cos.
expr: Any valid numeric expression.
Returns the inverse cosine (a.k.a. arc cosine) of expr, as if computed by java.lang.Math.acos.
expr: Any valid numeric expression.
Returns the tangent of expr, as if computed by java.lang.Math.tan.
expr: Any valid numeric expression.
Returns the inverse tangent (a.k.a. arc tangent) of expr, as if computed by java.lang.Math.atan
expr: Any valid numeric expression.
Converts radians to degrees.
expr: Any valid numeric expression.
Converts degrees to radians.
expr: Any valid numeric expression.
Returns the postive value of expr.
expr: Any valid numeric expression.
Returns the negated value of expr.
expr: Any valid numeric expression.
Returns -1.0, 0.0 or 1.0 as expr is negative, 0 or positive.
expr: Any valid numeric expression.
Returns Euler's number, e.
Returns pi.
Returns the factorial of expr. expr is [0..20]. Otherwise, null.
expr: A numeric expression.
Returns the cube root of expr.
expr: Any valid numeric expression.
Bitwise left shift.
base: The base number to shift.
shift: The number of bits to shift.
Bitwise right shift.
base: The base number to shift.
shift: The number of bits to shift.
Bitwise unsigned right shift.
base: The base number to shift.
shift: The number of bits to shift.
Returns the greatest value of all parameters, skipping null values.
expr1: Any valid expression.
expr2: Any valid expression.
expr3: Any valid expression.
Returns the least value of all parameters, skipping null values.
expr1: Any valid expression.
expr2: Any valid expression.
expr3: Any valid expression.
Returns an integer between 0 and num_buckets+1 by mapping expr into the ith equally sized bucket. Buckets are made by dividing [min_value, max_value] into equally sized regions. If expr < min_value, return 1, if expr > max_value return num_buckets+1.
expr: A valid numeric expression.
min_value: The minimum value.
max_value: The maximum value.
num_buckets: The number of buckets.
Returns the size of an array or a map. Returns -1 if null.
expr: Any valid expression.
Returns an unordered array containing the keys of the map.
map: A valid map expression.
Returns an unordered array containing the values of the map.
map: A valid map expression.
Returns true if the array contains the value.
array: The array to search.
expr: The expression to search for.
Sorts the input array in ascending or descending order according to the natural ordering of the array elements.
array: The array to sort.
order: Identifies whether to sort in ascending order.
Casts the value expr to the target data type binary.
expr: The expression to cast.
Casts the value expr to the target data type type.
expr: Any valid expression.
type: The type to cast expr to.
Converts the number of seconds from unix epoch (1970-01-01 00:00:00 UTC) to a string representing the timestamp of that moment in the current system time zone in the format of "1970-01-01 00:00:00".
unixtime: Unix time.
format: The format to convert unixtime to.
Returns the UNIX timestamp of the given time.
expr: The time string to convert.
format: The format of expr.
Parses the date_str expression with the fmt expression to a date. Returns null with invalid input. By default, it follows casting rules to a date if the fmt is omitted.
date_str: The date string expression.
fmt: The format of date_str.
Returns the year component of the date/timestamp.
date: The date to extract the year from.
Returns the quarter of the year for date, in the range 1 to 4.
date: The date to extract the quarter from.
Returns the month component of the date/timestamp.
date: The date to extract the month from.
Returns the day of month of the date/timestamp.
date: The date to extract the day from.
Returns the hour component of the string/timestamp.
timestamp: The timestamp to extract the hours from.
Returns the minute component of the string/timestamp.
timestamp: The timestamp to extract the minutes from.
Returns the second component of the string/timestamp.
timestamp: The timestamp to extract the seconds from.
Returns the week of the year of the given date. A week is considered to start on a Monday and week 1 is the first week with >3 days.
date: The date to extract the week of the year from.
Returns the number of days from startDate to endDate.
endDate: The end date.
startDate: The start date.
Returns the date that is num_days after start_date.
start_date: The start date.
num_days: The number of days to add to start_date.
Returns the date that is num_days before start_date.
start_date: The start date.
num_days: The number of days to subtract from start_date.
Given a timestamp like '2017-07-14 02:40:00.0', interprets it as a time in UTC, and renders that time as a timestamp in the given time zone. For example, 'GMT+1' would yield '2017-07-14 03:40:00.0'.
timestamp: The UTC timestamp.
timezone: The timezone to convert to.
Given a timestamp like '2017-07-14 02:40:00.0', interprets it as a time in the given time zone, and renders that time as a timestamp in UTC. For example, 'GMT+1' would yield '2017-07-14 01:40:00.0'.
timestamp: The timestamp to convert to UTC.
timezone: The timezone of timestamp.
Returns the current date at the start of query evaluation.
Returns the current timestamp at the start of query evaluation.
Returns the date that is num_months after start_date.
start_date: The starting date.
num_months: The number of months to add.
fmt: The output format.
Returns the last day of the month which the date belongs to.
date: A valid date expression.
Returns the first date which is later than start_date and named as indicated.
start_date: The start date.
day_of_week: The day of week.
Returns date with the time portion of the day truncated to the unit specified by the format model fmt. fmt should be one of ["year", "yyyy", "yy", "mon", "month", "mm"]
date: A valid date expression.
time_unit: The time unit.
Returns number of months between timestamp1 and timestamp2.
timestamp1: A valid timestamp expression.
timestamp2: A valid timestamp expression.
Converts timestamp to a value of string in the format specified by the date format fmt.
timestamp: A valid timestamp expression.
fmt: A valid date format.
If expr1 evaluates to true, then returns expr2; otherwise returns expr3.
expr1: An expression that should evaluate to a boolean value.
expr2: A valid expression.
expr3: A valid expression.
Returns true if expr is null, or false otherwise.
expr: A valid expression.
Returns true if expr is not null, or false otherwise.
expr: A valid expression.
Returns expr1 if it's not NaN, or expr2 otherwise.
expr1: A valid expression.
expr2: A valid expression.
Returns the first non-null argument if exists. Otherwise, null.
expr1: A valid expression.
expr2: A valid expression.
expr3: A valid expression.
Returns null if expr1 equals to expr2, or expr1 otherwise.
expr1: A valid expression.
expr2: A valid expression.
Throws an exception if expr is not true.
expr: A valid expression that evaluates to a boolean.
Returns the numeric value of the first character of str.
str: A string expression.
Converts the argument from a binary bin to a base 64 string.
bin: A binary expression.
Returns the character length of string data or number of bytes of binary data. The length of string data includes the trailing spaces. The length of binary data includes binary zeros.
str: A string expression.
Returns the ASCII character having the binary equivalent to expr. If n is larger than 256 the result is equivalent to chr(n % 256)
expr: A integer expression.
Returns the string or bytes resulting from concatenating the strings or bytes passed in as parameters in order. For example, concat('foo', 'bar') results in 'foobar'. Note that this function can take any number of input strings.
str1: A valid string expression.
str2: A valid string expression.
str3: A valid string expression.
Returns the concatenation of the strings separated by sep.
set: A string separator.
exp1: A valid expression.
Decodes the first argument using the second argument character set.
bin: The binary expression to decode.
charset: The charset to use to decode bin.
Returns the n-th input, e.g., returns input2 when n is 2.
n: A valid integer index.
input1: A valid string expression.
input3: A valid string expression.
Encodes the first argument using the second argument character set.
str: A string expression to encode.
charset: The charset to use to encode str.
Returns the index of val in the val1,val2,val3,... list or 0 if not found. For example field('world','say','hello','world') returns 3. All primitive types are supported, arguments are compared using str.equals(x). If val is NULL, the return value is 0.
val1: A valid expression.
val2: A valid expression.
val3: A valid expression.
Returns the index (1-based) of the given string (str) in the comma-delimited list (str_array). Returns 0, if the string was not found or if the given string (str) contains a comma.
str: The string expression to search for.
str_array: A comma-delimited list of values.
Formats the number expr1 like '#,###,###.##', rounded to expr2 decimal places. If expr2 is 0, the result has no decimal point or fractional part. This is supposed to function like MySQL's FORMAT.
expr1: A numeric expression to format.
expr2: The number of deciml places.
Extracts a json object from path.
json_txt: JSON data.
path: The path to extract.
Returns true if the string str appears as an entire line in filename.
str: The string to search for.
filename: The name of the file to search.
Returns the (1-based) index of the first occurrence of substr in str.
str: A string expression.
substr: The string expression to search for.
Returns the character length of string data or number of bytes of binary data. The length of string data includes the trailing spaces. The length of binary data includes binary zeros.
expr: A string expression.
Returns the position of the first occurrence of substr in str after position pos. The given pos and return value are 1-based.
substr: The string expression to search for.
str: The string expression to search in.
pos: The starting index.
Returns str with all characters changed to lowercase.
expr: A string expression.
Returns str, left-padded with pad to a length of len. If str is longer than len, the return value is shortened to len characters.
str: A string expression.
len: The length to pad.
pad_str: The pad string.
Removes the leading space characters from str.
str: A string expression.
Returns the byte length of expr or number of bytes in binary data.
expr: Any string expression.
Returns the specified part from the URL. For example, parse_url('http://facebook.com/path1/p.php?k1=v1#Ref1', 'HOST') returns 'facebook.com'. Also a value of a particular key in QUERY can be extracted by providing the key as the third argument, for example, parse_url('http://facebook.com/path1/p.php?k1=v1#Ref1', 'QUERY', 'k1') returns 'v1'.
url: A valid URL expression.
partToExtract: The URL part to extract. Valid values for partToExtract include HOST, PATH, QUERY, REF, PROTOCOL, AUTHORITY, FILE, and USERINFO.
key: The key.
Returns a formatted string from printf-style format strings.
strfmt: The string format.
obj1: The object to include in the formatted string.
Extracts a group that matches regexp.
str: A string expression.
regexp: A regular expression to search for.
idx: The starting index.
Replaces all substring of str that match regexp with rep.
str: A string expression.
regexp: A regular expression to search for.
rep: The string to replace.
Returns the string which repeats the given string value n times.
str: The string expression to repeat.
n: The number of times to repeat str.
Replaces all occurrences of search with replace. If search is not found in str, str is returned unchanged. If replace is not specified or is an empty string, nothing replaces the string that is removed from str.
str: A string expression.
search: The search string.
replace: A string expression to replace search values.
Returns the reversed given string.
str: A string expression.
Returns str, right-padded with pad to a length of len. If str is longer than len, the return value is shortened to len characters.
str: A string expression.
len: The length to pad.
pad_str: The pad string.
Removes the trailing space characters from str.
str: A string expression.
Splits str into an array of array of words.
str: A string expression.
lang: The language of str.
country: The country of the specified language.
Returns a string consisting of n spaces.
n: The number of spaces.
Splits str around occurrences that match regex.
str: A string expression.
regex: The regular expression to match.
Creates a map after splitting the text into key/value pairs using delimiters. Default delimiters are ',' for pairDelim and ':' for keyValueDelim.
text: A string expression.
pairDelim: The pair delimiter.
keyValueDelim: The value delimiter.
Returns the substring of str that starts at pos and is of length len, or the slice of byte array that starts at pos and is of length len.
str: A string expression.
pos: The starting position.
len: The length of the string.
Returns the substring from str before count occurrences of the delimiter delim. If count is positive, everything to the left of the final delimiter (counting from the left) is returned. If count is negative, everything to the right of the final delimiter (counting from the right) is returned. The function substring_index performs a case-sensitive match when searching for delim.
str: A string expression.
delim: The delimiter.
count: Total number of occurrences.
Translates the input string by replacing the characters present in the from string with the corresponding characters in the to string.
input: A string expression.
from: A string expression.
to: A string expression.
Removes the leading and trailing space characters from str.
str: A string expression.
Converts the argument from a base 64 string str to a binary.
str: A string expression.
Returns str with all characters changed to uppercase.
str: A string expression.
Returns str with the first letter of each word in uppercase. All other letters are in lowercase. Words are delimited by white space.
str: A string expression.
Returns the Levenshtein distance between the two given strings.
str1: A string expression.
str2: A string expression.
Returns Soundex code of the string.
str: A string expression.
Returns a masked version of str. By default, upper case letters are converted to "X", lower case letters are converted to "x" and numbers are converted to "n". For example mask("abcd-EFGH-8765-4321") results in xxxx-XXXX-nnnn-nnnn. You can override the characters used in the mask by supplying additional arguments: the second argument controls the mask character for upper case letters, the third argument for lower case letters and the fourth argument for numbers. For example, mask("abcd-EFGH-8765-4321", "U", "l", "#") results in llll-UUUU-####-####.
str: The string to mask.
upper: The character to mask for uppercase letters.
lower: The character to mask for lowercase letters.
number: The character to mask for numbers.
Returns a masked version of str with the first n values masked. Upper case letters are converted to "X", lower case letters are converted to "x" and numbers are converted to "n". For example, mask_first_n("1234-5678-8765-4321", 4) results in nnnn-5678-8765-4321.
str: The string to mask.
n: The number of values to mask.
Returns a masked version of str with the last n values masked. Upper case letters are converted to "X", lower case letters are converted to "x" and numbers are converted to "n". For example, mask_last_n("1234-5678-8765-4321", 4) results in 1234-5678-8765-nnnn.
str: The string to mask.
n: The number of values to mask.
Returns a masked version of str, showing the first n characters unmasked. Upper case letters are converted to "X", lower case letters are converted to "x" and numbers are converted to "n". For example, mask_show_first_n("1234-5678-8765-4321", 4) results in 1234-nnnn-nnnn-nnnn.
str: The string to mask.
n: The number of values to mask.
Returns a masked version of str, showing the last n characters unmasked. Upper case letters are converted to "X", lower case letters are converted to "x" and numbers are converted to "n". For example, mask_show_last_n("1234-5678-8765-4321", 4) results in nnnn-nnnn-nnnn-4321.
str: The string to mask.
n: The number of values to mask.
Returns a hashed value based on str. The hash is consistent and can be used to join masked values together across tables. This function returns null for non-string types.
str: The string to mask.
Calls a method with reflection.
class: The class to call.
method: The method to call.
arg1: The argument to pass in.
Calls a method with reflection.
class: The class to call.
method: The method to call.
arg1: The argument to pass in.
Returns a hash value of the arguments.
expr1: A valid expression.
expr2: A valid expression.
Returns current user name from the configured authenticator manager. Could be the same as the user provided when connecting, but with some authentication managers (for example HadoopDefaultAuthenticator) it could be different.
Returns current user name from the session state. This is the username provided when connecting to Hive.
Returns current database name.
Returns a sha1 hash value as a hex string of the expr.
expr: A valid expression.
Returns a cyclic redundancy check value of the expr as a bigint.
expr: A valid expression.
Returns a checksum of SHA-2 family as a hex string of expr. SHA-224, SHA-256, SHA-384, and SHA-512 are supported. Bit length of 0 is equivalent to 256.
expr: A valid expression.
bitlength: The bit length.
Encrypt input using AES. Key lengths of 128, 192 or 256 bits can be used. 192 and 256 bits keys can be used if Java Cryptography Extension (JCE) Unlimited Strength Jurisdiction Policy Files are installed. If either argument is NULL or the key length is not one of the permitted values, the return value is NULL. Example: base64(aes_encrypt('ABC', '1234567890123456')) = 'y6Ss+zCYObpCbgfWfyNWTw=='.
input: The input value to encrypt.
key: The key to use when encrypting.
Returns the Hive version. The string contains 2 fields, the first being a build number and the second being a build hash. Example: "select version();" might return "2.1.0.2.5.0.0-1245 r027527b9c5ce1a3d7d0b6d2e6de2378fb0c39232". Actual results will depend on your build.
Returns the number of rows for which the supplied expression(s) are unique and non-null.
expr1: A valid expression.
expr2: A valid expression.
Returns the sum calculated from values of a group.
expr: A valid expression.
Returns the sum calculated from distinct values of a group.
expr: A valid expression.
Returns the mean calculated from values of a group.
expr: A valid expression.
Returns the mean calculated from distinct values of a group.
expr: A valid expression.
Returns the minimum value of expr.
expr: A valid expression.
Returns the maximum value of expr.
expr: A valid expression.
Returns the sample variance calculated from values of a group.
expr: A valid expression.
Returns the population standard deviation calculated from values of a group.
expr: A valid expression.
Returns the sample standard deviation calculated from values of a group.
expr: A valid expression.
Returns the population covariance of a set of number pairs.
expr1: A valid expression.
expr2: A valid expression.
Returns the sample covariance of a set of number pairs.
expr1: A valid expression.
expr2: A valid expression.
Returns Pearson coefficient of correlation between a set of number pairs.
expr1: A valid expression.
expr2: A valid expression.
Returns the exact percentile value of numeric column col at the given percentage. The value of percentage must be between 0.0 and 1.0. The value of frequency should be positive integral
col: A numeric expression.
percentage: The percentage.
accuracy: The accuracy to control approximation.
Returns the approximate percentile value of numeric column col at the given percentage. The value of percentage must be between 0.0 and 1.0. The accuracy parameter (default: 10000) is a positive numeric literal which controls approximation accuracy at the cost of memory. Higher value of accuracy yields better accuracy, 1.0/accuracy is the relative error of the approximation. When percentage is an array, each value of the percentage array must be between 0.0 and 1.0. In this case, returns the approximate percentile array of column col at the given percentage array.
col: A numeric expression.
percentage: The percentage.
accuracy: The accuracy to control approximation.
Collects and returns a set of unique elements.
expr: A valid expression.
Collects and returns a set of unique elements.
expr: A valid expression.
Divides the rows for each window partition into n buckets ranging from 1 to at most n.
n: The number of buckets.
Separates the elements of array expr into multiple rows, or the elements of map expr into multiple rows and columns.
expr: A valid expression.
Separates the elements of array expr into multiple rows with positions, or the elements of map expr into multiple rows and columns with positions.
expr: A valid expression.
Explodes an array of structs into a table.
expr: A valid expression.
Separates expr1, ..., exprk into n rows.
n: The number of rows.
expr1: A valid expression.
expr2: A valid expression.
Takes URL string and a set of n URL parts, and returns a tuple of n values. This is similar to the parse_url() UDF but can extract multiple parts at once out of a URL. Valid part names are: HOST, PATH, QUERY, REF, PROTOCOL, AUTHORITY, FILE, USERINFO, QUERY:[KEY].
urlStr: A valid URL string.
p1: A valid part name.
p2: A valid part name.
Returns expr rounded to d decimal places using HALF_UP rounding mode.
expr: Any numeric expression.
d: The number of decimal places.
Returns expr rounded to d decimal places using HALF_EVEN rounding mode.
expr: Any numeric expression.
d: The number of decimal places.
Returns the largest integer not greater than expr.
expr: Any numeric expression.
Returns the smallest integer not smaller than expr.
expr: Any numeric expression.
Returns a random value with independent and identically distributed (i.i.d.) uniformly distributed values in [0, 1).
seed: The seed to use to generate the random value.
Returns e to the power of expr.
expr: Any numeric expression.
Returns the natural logarithm (base e) of expr.
expr: Any numeric expression.
Returns the logarithm of expr with base 10.
expr: Any numeric expression.
Returns the logarithm of expr with base 2.
expr: Any numeric expression.
Returns the logarithm of expr with base.
base: A numeric expression to use as the base.
expr: Any numeric expression.
Raises expr1 to the power of expr2.
expr1: Any numeric expression.
expr2: Any numeric expression.
Returns the square root of expr.
expr: Any numeric expression.
Returns the string representation of the long value expr represented in binary.
expr: A long expression.
Converts expr to hexadecimal.
expr: The expression to convert to hex.
Converts hexadecimal expr to binary.
expr: The hexadecimal value to convert to binary.
Convert num from from_base to to_base.
num: The number to convert.
from_base: The original base of num.
to_base: The base to convert num to.
Returns the absolute value of the numeric value.
expr: Any valid numeric expression.
Returns the positive value of expr1 mod expr2.
expr1: Any valid numeric expression.
expr2: Any valid numeric expression.
Returns the sine of expr, as if computed by java.lang.Math.sin.
expr: Any valid numeric expression.
Returns the inverse sine (a.k.a. arc sine) the arc sin of expr, as if computed by java.lang.Math.asin.
expr: Any valid numeric expression.
Returns the cosine of expr, as if computed by java.lang.Math.cos.
expr: Any valid numeric expression.
Returns the inverse cosine (a.k.a. arc cosine) of expr, as if computed by java.lang.Math.acos.
expr: Any valid numeric expression.
Returns the tangent of expr, as if computed by java.lang.Math.tan.
expr: Any valid numeric expression.
Returns the inverse tangent (a.k.a. arc tangent) of expr, as if computed by java.lang.Math.atan
expr: Any valid numeric expression.
Converts radians to degrees.
expr: Any valid numeric expression.
Converts degrees to radians.
expr: Any valid numeric expression.
Returns the postive value of expr.
expr: Any valid numeric expression.
Returns the negated value of expr.
expr: Any valid numeric expression.
Returns -1.0, 0.0 or 1.0 as expr is negative, 0 or positive.
expr: Any valid numeric expression.
Returns Euler's number, e.
Returns pi.
Returns the factorial of expr. expr is [0..20]. Otherwise, null.
expr: A numeric expression.
Returns the cube root of expr.
expr: Any valid numeric expression.
Bitwise left shift.
base: The base number to shift.
shift: The number of bits to shift.
Bitwise right shift.
base: The base number to shift.
shift: The number of bits to shift.
Bitwise unsigned right shift.
base: The base number to shift.
shift: The number of bits to shift.
Returns the greatest value of all parameters, skipping null values.
expr1: Any valid expression.
expr2: Any valid expression.
expr3: Any valid expression.
Returns the least value of all parameters, skipping null values.
expr1: Any valid expression.
expr2: Any valid expression.
expr3: Any valid expression.
Returns an integer between 0 and num_buckets+1 by mapping expr into the ith equally sized bucket. Buckets are made by dividing [min_value, max_value] into equally sized regions. If expr < min_value, return 1, if expr > max_value return num_buckets+1.
expr: A valid numeric expression.
min_value: The minimum value.
max_value: The maximum value.
num_buckets: The number of buckets.
Returns the size of an array or a map. Returns -1 if null.
expr: Any valid expression.
Returns an unordered array containing the keys of the map.
map: A valid map expression.
Returns an unordered array containing the values of the map.
map: A valid map expression.
Returns true if the array contains the value.
array: The array to search.
expr: The expression to search for.
Sorts the input array in ascending or descending order according to the natural ordering of the array elements.
array: The array to sort.
order: Identifies whether to sort in ascending order.
Casts the value expr to the target data type binary.
expr: The expression to cast.
Casts the value expr to the target data type type.
expr: Any valid expression.
type: The type to cast expr to.
Converts the number of seconds from unix epoch (1970-01-01 00:00:00 UTC) to a string representing the timestamp of that moment in the current system time zone in the format of "1970-01-01 00:00:00".
unixtime: Unix time.
format: The format to convert unixtime to.
Returns the UNIX timestamp of the given time.
expr: The time string to convert.
format: The format of expr.
Parses the date_str expression with the fmt expression to a date. Returns null with invalid input. By default, it follows casting rules to a date if the fmt is omitted.
date_str: The date string expression.
fmt: The format of date_str.
Returns the year component of the date/timestamp.
date: The date to extract the year from.
Returns the quarter of the year for date, in the range 1 to 4.
date: The date to extract the quarter from.
Returns the month component of the date/timestamp.
date: The date to extract the month from.
Returns the day of month of the date/timestamp.
date: The date to extract the day from.
Returns the hour component of the string/timestamp.
timestamp: The timestamp to extract the hours from.
Returns the minute component of the string/timestamp.
timestamp: The timestamp to extract the minutes from.
Returns the second component of the string/timestamp.
timestamp: The timestamp to extract the seconds from.
Returns the week of the year of the given date. A week is considered to start on a Monday and week 1 is the first week with >3 days.
date: The date to extract the week of the year from.
Returns the number of days from startDate to endDate.
endDate: The end date.
startDate: The start date.
Returns the date that is num_days after start_date.
start_date: The start date.
num_days: The number of days to add to start_date.
Returns the date that is num_days before start_date.
start_date: The start date.
num_days: The number of days to subtract from start_date.
Given a timestamp like '2017-07-14 02:40:00.0', interprets it as a time in UTC, and renders that time as a timestamp in the given time zone. For example, 'GMT+1' would yield '2017-07-14 03:40:00.0'.
timestamp: The UTC timestamp.
timezone: The timezone to convert to.
Given a timestamp like '2017-07-14 02:40:00.0', interprets it as a time in the given time zone, and renders that time as a timestamp in UTC. For example, 'GMT+1' would yield '2017-07-14 01:40:00.0'.
timestamp: The timestamp to convert to UTC.
timezone: The timezone of timestamp.
Returns the current date at the start of query evaluation.
Returns the current timestamp at the start of query evaluation.
Returns the date that is num_months after start_date.
start_date: The starting date.
num_months: The number of months to add.
fmt: The output format.
Returns the last day of the month which the date belongs to.
date: A valid date expression.
Returns the first date which is later than start_date and named as indicated.
start_date: The start date.
day_of_week: The day of week.
Returns date with the time portion of the day truncated to the unit specified by the format model fmt. fmt should be one of ["year", "yyyy", "yy", "mon", "month", "mm"]
date: A valid date expression.
time_unit: The time unit.
Returns number of months between timestamp1 and timestamp2.
timestamp1: A valid timestamp expression.
timestamp2: A valid timestamp expression.
Converts timestamp to a value of string in the format specified by the date format fmt.
timestamp: A valid timestamp expression.
fmt: A valid date format.
If expr1 evaluates to true, then returns expr2; otherwise returns expr3.
expr1: An expression that should evaluate to a boolean value.
expr2: A valid expression.
expr3: A valid expression.
Returns true if expr is null, or false otherwise.
expr: A valid expression.
Returns true if expr is not null, or false otherwise.
expr: A valid expression.
Returns expr1 if it's not NaN, or expr2 otherwise.
expr1: A valid expression.
expr2: A valid expression.
Returns the first non-null argument if exists. Otherwise, null.
expr1: A valid expression.
expr2: A valid expression.
expr3: A valid expression.
Returns null if expr1 equals to expr2, or expr1 otherwise.
expr1: A valid expression.
expr2: A valid expression.
Throws an exception if expr is not true.
expr: A valid expression that evaluates to a boolean.
Returns the numeric value of the first character of str.
str: A string expression.
Converts the argument from a binary bin to a base 64 string.
bin: A binary expression.
Returns the character length of string data or number of bytes of binary data. The length of string data includes the trailing spaces. The length of binary data includes binary zeros.
str: A string expression.
Returns the ASCII character having the binary equivalent to expr. If n is larger than 256 the result is equivalent to chr(n % 256)
expr: A integer expression.
Returns the string or bytes resulting from concatenating the strings or bytes passed in as parameters in order. For example, concat('foo', 'bar') results in 'foobar'. Note that this function can take any number of input strings.
str1: A valid string expression.
str2: A valid string expression.
str3: A valid string expression.
Returns the concatenation of the strings separated by sep.
set: A string separator.
exp1: A valid expression.
Decodes the first argument using the second argument character set.
bin: The binary expression to decode.
charset: The charset to use to decode bin.
Returns the n-th input, e.g., returns input2 when n is 2.
n: A valid integer index.
input1: A valid string expression.
input3: A valid string expression.
Encodes the first argument using the second argument character set.
str: A string expression to encode.
charset: The charset to use to encode str.
Returns the index of val in the val1,val2,val3,... list or 0 if not found. For example field('world','say','hello','world') returns 3. All primitive types are supported, arguments are compared using str.equals(x). If val is NULL, the return value is 0.
val1: A valid expression.
val2: A valid expression.
val3: A valid expression.
Returns the index (1-based) of the given string (str) in the comma-delimited list (str_array). Returns 0, if the string was not found or if the given string (str) contains a comma.
str: The string expression to search for.
str_array: A comma-delimited list of values.
Formats the number expr1 like '#,###,###.##', rounded to expr2 decimal places. If expr2 is 0, the result has no decimal point or fractional part. This is supposed to function like MySQL's FORMAT.
expr1: A numeric expression to format.
expr2: The number of deciml places.
Extracts a json object from path.
json_txt: JSON data.
path: The path to extract.
Returns true if the string str appears as an entire line in filename.
str: The string to search for.
filename: The name of the file to search.
Returns the (1-based) index of the first occurrence of substr in str.
str: A string expression.
substr: The string expression to search for.
Returns the character length of string data or number of bytes of binary data. The length of string data includes the trailing spaces. The length of binary data includes binary zeros.
expr: A string expression.
Returns the position of the first occurrence of substr in str after position pos. The given pos and return value are 1-based.
substr: The string expression to search for.
str: The string expression to search in.
pos: The starting index.
Returns str with all characters changed to lowercase.
expr: A string expression.
Returns str, left-padded with pad to a length of len. If str is longer than len, the return value is shortened to len characters.
str: A string expression.
len: The length to pad.
pad_str: The pad string.
Removes the leading space characters from str.
str: A string expression.
Returns the byte length of expr or number of bytes in binary data.
expr: Any string expression.
Returns the specified part from the URL. For example, parse_url('http://facebook.com/path1/p.php?k1=v1#Ref1', 'HOST') returns 'facebook.com'. Also a value of a particular key in QUERY can be extracted by providing the key as the third argument, for example, parse_url('http://facebook.com/path1/p.php?k1=v1#Ref1', 'QUERY', 'k1') returns 'v1'.
url: A valid URL expression.
partToExtract: The URL part to extract. Valid values for partToExtract include HOST, PATH, QUERY, REF, PROTOCOL, AUTHORITY, FILE, and USERINFO.
key: The key.
Returns a formatted string from printf-style format strings.
strfmt: The string format.
obj1: The object to include in the formatted string.
Extracts a group that matches regexp.
str: A string expression.
regexp: A regular expression to search for.
idx: The starting index.
Replaces all substring of str that match regexp with rep.
str: A string expression.
regexp: A regular expression to search for.
rep: The string to replace.
Returns the string which repeats the given string value n times.
str: The string expression to repeat.
n: The number of times to repeat str.
Replaces all occurrences of search with replace. If search is not found in str, str is returned unchanged. If replace is not specified or is an empty string, nothing replaces the string that is removed from str.
str: A string expression.
search: The search string.
replace: A string expression to replace search values.
Returns the reversed given string.
str: A string expression.
Returns str, right-padded with pad to a length of len. If str is longer than len, the return value is shortened to len characters.
str: A string expression.
len: The length to pad.
pad_str: The pad string.
Removes the trailing space characters from str.
str: A string expression.
Splits str into an array of array of words.
str: A string expression.
lang: The language of str.
country: The country of the specified language.
Returns a string consisting of n spaces.
n: The number of spaces.
Splits str around occurrences that match regex.
str: A string expression.
regex: The regular expression to match.
Creates a map after splitting the text into key/value pairs using delimiters. Default delimiters are ',' for pairDelim and ':' for keyValueDelim.
text: A string expression.
pairDelim: The pair delimiter.
keyValueDelim: The value delimiter.
Returns the substring of str that starts at pos and is of length len, or the slice of byte array that starts at pos and is of length len.
str: A string expression.
pos: The starting position.
len: The length of the string.
Returns the substring from str before count occurrences of the delimiter delim. If count is positive, everything to the left of the final delimiter (counting from the left) is returned. If count is negative, everything to the right of the final delimiter (counting from the right) is returned. The function substring_index performs a case-sensitive match when searching for delim.
str: A string expression.
delim: The delimiter.
count: Total number of occurrences.
Translates the input string by replacing the characters present in the from string with the corresponding characters in the to string.
input: A string expression.
from: A string expression.
to: A string expression.
Removes the leading and trailing space characters from str.
str: A string expression.
Converts the argument from a base 64 string str to a binary.
str: A string expression.
Returns str with all characters changed to uppercase.
str: A string expression.
Returns str with the first letter of each word in uppercase. All other letters are in lowercase. Words are delimited by white space.
str: A string expression.
Returns the Levenshtein distance between the two given strings.
str1: A string expression.
str2: A string expression.
Returns Soundex code of the string.
str: A string expression.
Returns a masked version of str. By default, upper case letters are converted to "X", lower case letters are converted to "x" and numbers are converted to "n". For example mask("abcd-EFGH-8765-4321") results in xxxx-XXXX-nnnn-nnnn. You can override the characters used in the mask by supplying additional arguments: the second argument controls the mask character for upper case letters, the third argument for lower case letters and the fourth argument for numbers. For example, mask("abcd-EFGH-8765-4321", "U", "l", "#") results in llll-UUUU-####-####.
str: The string to mask.
upper: The character to mask for uppercase letters.
lower: The character to mask for lowercase letters.
number: The character to mask for numbers.
Returns a masked version of str with the first n values masked. Upper case letters are converted to "X", lower case letters are converted to "x" and numbers are converted to "n". For example, mask_first_n("1234-5678-8765-4321", 4) results in nnnn-5678-8765-4321.
str: The string to mask.
n: The number of values to mask.
Returns a masked version of str with the last n values masked. Upper case letters are converted to "X", lower case letters are converted to "x" and numbers are converted to "n". For example, mask_last_n("1234-5678-8765-4321", 4) results in 1234-5678-8765-nnnn.
str: The string to mask.
n: The number of values to mask.
Returns a masked version of str, showing the first n characters unmasked. Upper case letters are converted to "X", lower case letters are converted to "x" and numbers are converted to "n". For example, mask_show_first_n("1234-5678-8765-4321", 4) results in 1234-nnnn-nnnn-nnnn.
str: The string to mask.
n: The number of values to mask.
Returns a masked version of str, showing the last n characters unmasked. Upper case letters are converted to "X", lower case letters are converted to "x" and numbers are converted to "n". For example, mask_show_last_n("1234-5678-8765-4321", 4) results in nnnn-nnnn-nnnn-4321.
str: The string to mask.
n: The number of values to mask.
Returns a hashed value based on str. The hash is consistent and can be used to join masked values together across tables. This function returns null for non-string types.
str: The string to mask.
Calls a method with reflection.
class: The class to call.
method: The method to call.
arg1: The argument to pass in.
Calls a method with reflection.
class: The class to call.
method: The method to call.
arg1: The argument to pass in.
Returns a hash value of the arguments.
expr1: A valid expression.
expr2: A valid expression.
Returns current user name from the configured authenticator manager. Could be the same as the user provided when connecting, but with some authentication managers (for example HadoopDefaultAuthenticator) it could be different.
Returns current user name from the session state. This is the username provided when connecting to Hive.
Returns current database name.
Returns a sha1 hash value as a hex string of the expr.
expr: A valid expression.
Returns a cyclic redundancy check value of the expr as a bigint.
expr: A valid expression.
Returns a checksum of SHA-2 family as a hex string of expr. SHA-224, SHA-256, SHA-384, and SHA-512 are supported. Bit length of 0 is equivalent to 256.
expr: A valid expression.
bitlength: The bit length.
Encrypt input using AES. Key lengths of 128, 192 or 256 bits can be used. 192 and 256 bits keys can be used if Java Cryptography Extension (JCE) Unlimited Strength Jurisdiction Policy Files are installed. If either argument is NULL or the key length is not one of the permitted values, the return value is NULL. Example: base64(aes_encrypt('ABC', '1234567890123456')) = 'y6Ss+zCYObpCbgfWfyNWTw=='.
input: The input value to encrypt.
key: The key to use when encrypting.
Returns the Hive version. The string contains 2 fields, the first being a build number and the second being a build hash. Example: "select version();" might return "2.1.0.2.5.0.0-1245 r027527b9c5ce1a3d7d0b6d2e6de2378fb0c39232". Actual results will depend on your build.
Returns the number of rows for which the supplied expression(s) are unique and non-null.
expr1: A valid expression.
expr2: A valid expression.
Returns the sum calculated from values of a group.
expr: A valid expression.
Returns the sum calculated from distinct values of a group.
expr: A valid expression.
Returns the mean calculated from values of a group.
expr: A valid expression.
Returns the mean calculated from distinct values of a group.
expr: A valid expression.
Returns the minimum value of expr.
expr: A valid expression.
Returns the maximum value of expr.
expr: A valid expression.
Returns the sample variance calculated from values of a group.
expr: A valid expression.
Returns the population standard deviation calculated from values of a group.
expr: A valid expression.
Returns the sample standard deviation calculated from values of a group.
expr: A valid expression.
Returns the population covariance of a set of number pairs.
expr1: A valid expression.
expr2: A valid expression.
Returns the sample covariance of a set of number pairs.
expr1: A valid expression.
expr2: A valid expression.
Returns Pearson coefficient of correlation between a set of number pairs.
expr1: A valid expression.
expr2: A valid expression.
Returns the exact percentile value of numeric column col at the given percentage. The value of percentage must be between 0.0 and 1.0. The value of frequency should be positive integral
col: A numeric expression.
percentage: The percentage.
accuracy: The accuracy to control approximation.
Returns the approximate percentile value of numeric column col at the given percentage. The value of percentage must be between 0.0 and 1.0. The accuracy parameter (default: 10000) is a positive numeric literal which controls approximation accuracy at the cost of memory. Higher value of accuracy yields better accuracy, 1.0/accuracy is the relative error of the approximation. When percentage is an array, each value of the percentage array must be between 0.0 and 1.0. In this case, returns the approximate percentile array of column col at the given percentage array.
col: A numeric expression.
percentage: The percentage.
accuracy: The accuracy to control approximation.
Collects and returns a set of unique elements.
expr: A valid expression.
Collects and returns a set of unique elements.
expr: A valid expression.
Divides the rows for each window partition into n buckets ranging from 1 to at most n.
n: The number of buckets.
Separates the elements of array expr into multiple rows, or the elements of map expr into multiple rows and columns.
expr: A valid expression.
Separates the elements of array expr into multiple rows with positions, or the elements of map expr into multiple rows and columns with positions.
expr: A valid expression.
Explodes an array of structs into a table.
expr: A valid expression.
Separates expr1, ..., exprk into n rows.
n: The number of rows.
expr1: A valid expression.
expr2: A valid expression.
Takes URL string and a set of n URL parts, and returns a tuple of n values. This is similar to the parse_url() UDF but can extract multiple parts at once out of a URL. Valid part names are: HOST, PATH, QUERY, REF, PROTOCOL, AUTHORITY, FILE, USERINFO, QUERY:[KEY].
urlStr: A valid URL string.
p1: A valid part name.
p2: A valid part name.
SELECT {
[ TOP <numeric_literal> | DISTINCT ]
{
*
| {
<expression> [ [ AS ] <column_reference> ]
| { <table_name> | <correlation_name> } .*
} [ , ... ]
}
[ INTO csv:// [ filename= ] <file_path> [ ;delimiter=tab ] ]
{
FROM <table_reference> [ [ AS ] <identifier> ]
} [ , ... ]
[ [
INNER | { { LEFT | RIGHT | FULL } [ OUTER ] }
] JOIN <table_reference> [ ON <search_condition> ] [ [ AS ] <identifier> ]
] [ ... ]
[ WHERE <search_condition> ]
[ GROUP BY <column_reference> [ , ... ]
[ HAVING <search_condition> ]
[ UNION [ ALL ] <select_statement> ]
[
ORDER BY
<column_reference> [ ASC | DESC ] [ NULLS FIRST | NULLS LAST ]
]
[
LIMIT <expression>
[
{ OFFSET | , }
<expression>
]
]
}
<expression> ::=
| <column_reference>
| @ <parameter>
| ?
| COUNT( * | { [ DISTINCT ] <expression> } )
| { AVG | MAX | MIN | SUM | COUNT } ( <expression> )
| NULLIF ( <expression> , <expression> )
| COALESCE ( <expression> , ... )
| CASE <expression>
WHEN { <expression> | <search_condition> } THEN { <expression> | NULL } [ ... ]
[ ELSE { <expression> | NULL } ]
END
| <literal>
| <sql_function>
<search_condition> ::=
{
<expression> { = | > | < | >= | <= | <> | != | LIKE | NOT LIKE | IN | NOT IN | IS NULL | IS NOT NULL | AND | OR | CONTAINS | BETWEEN } [ <expression> ]
} [ { AND | OR } ... ]
SELECT * FROM Resources
SELECT [Permission] AS MY_Permission FROM Resources
SELECT CAST(AnnualRevenue AS VARCHAR) AS Str_AnnualRevenue FROM Resources
SELECT * FROM Resources WHERE Type = 'FILE'
SELECT COUNT(*) AS MyCount FROM Resources
SELECT COUNT(DISTINCT Permission) FROM Resources
SELECT DISTINCT Permission FROM Resources
SELECT Permission, MAX(AnnualRevenue) FROM Resources GROUP BY Permission
SELECT Customers.ContactName, Orders.OrderDate FROM Customers, Orders WHERE Customers.CustomerId=Orders.CustomerId
SELECT FullPath, Permission FROM Resources ORDER BY Permission ASC
SELECT FullPath, Permission FROM Resources LIMIT 10
SELECT * FROM Resources WHERE Type = @param
SELECT COUNT(*) FROM Resources WHERE Type = 'FILE'
SELECT COUNT(DISTINCT FullPath) AS DistinctValues FROM Resources WHERE Type = 'FILE'
SELECT Permission, AVG(AnnualRevenue) FROM Resources WHERE Type = 'FILE'
GROUP BY Permission
SELECT MIN(AnnualRevenue), Permission FROM Resources WHERE Type = 'FILE'
GROUP BY Permission
SELECT Permission, MAX(AnnualRevenue) FROM Resources WHERE Type = 'FILE'
GROUP BY Permission
SELECT SUM(AnnualRevenue) FROM Resources WHERE Type = 'FILE'
SELECT Customers.ContactName, Orders.OrderDate FROM Customers, Orders WHERE Customers.CustomerId=Orders.CustomerId
SELECT Customers.ContactName, Orders.OrderDate FROM Customers LEFT OUTER JOIN Orders ON Customers.CustomerId=Orders.CustomerId
SELECT * FROM MyTable WHERE MyDateField = L_TODAY()
SELECT * FROM MyTable WHERE MyDateField = L_YESTERDAY()
SELECT * FROM MyTable WHERE MyDateField = L_TOMORROW()
SELECT * FROM MyTable WHERE MyDateField = L_LAST_WEEK()
SELECT * FROM MyTable WHERE MyDateField = L_THIS_WEEK()
SELECT * FROM MyTable WHERE MyDateField = L_NEXT_WEEK()
SELECT * FROM MyTable WHERE MyDateField = L_LAST_N_DAYS(3)
SELECT * FROM MyTable WHERE MyDateField = L_NEXT_N_DAYS(3)
SELECT * FROM MyTable WHERE MyDateField = L_LAST_N_WEEKS(3)
SELECT * FROM MyTable WHERE MyDateField = L_NEXT_N_WEEKS(3)
SELECT { [ TOP <numeric_literal> | DISTINCT ] { * | { <expression> [ [ AS ] <column_reference> ] | { <table_name> | <correlation_name> } .* } [ , ... ] } [ INTO csv:// [ filename= ] <file_path> [ ;delimiter=tab ] ] { FROM <table_reference> [ [ AS ] <identifier> ] } [ , ... ] [ [ INNER | { { LEFT | RIGHT | FULL } [ OUTER ] } ] JOIN <table_reference> [ ON <search_condition> ] [ [ AS ] <identifier> ] ] [ ... ] [ WHERE <search_condition> ] [ GROUP BY <column_reference> [ , ... ] [ HAVING <search_condition> ] [ UNION [ ALL ] <select_statement> ] [ ORDER BY <column_reference> [ ASC | DESC ] [ NULLS FIRST | NULLS LAST ] ] [ LIMIT <expression> [ { OFFSET | , } <expression> ] ] } | SCOPE_IDENTITY()
<expression> ::= | <column_reference> | @ <parameter> | ? | COUNT( * | { [ DISTINCT ] <expression> } ) | { AVG | MAX | MIN | SUM | COUNT } ( <expression> ) | NULLIF ( <expression> , <expression> ) | COALESCE ( <expression> , ... ) | CASE <expression> WHEN { <expression> | <search_condition> } THEN { <expression> | NULL } [ ... ] [ ELSE { <expression> | NULL } ] END | <literal> | <sql_function>
<search_condition> ::= { <expression> { = | > | < | >= | <= | <> | != | LIKE | NOT LIKE | IN | NOT IN | IS NULL | IS NOT NULL | AND | OR | CONTAINS | BETWEEN } [ <expression> ] } [ { AND | OR } ... ] |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
A SELECT statement can consist of the following basic clauses.
SELECT
INTO
FROM
JOIN
WHERE
GROUP BY
HAVING
UNION
ORDER BY
LIMIT
The following syntax diagram outlines the syntax supported by the SQL engine of the provider:
Return all columns:
Rename a column:
Cast a column's data as a different data type:
Search data:
Return the number of items matching the query criteria:
Return the number of unique items matching the query criteria:
Return the unique items matching the query criteria:
Summarize data:
See Aggregate Functions below for details.
Retrieve data from multiple tables.
See JOIN Queries below for details.
Sort a result set in ascending order:
Restrict a result set to the specified number of rows:
Parameterize a query to pass in inputs at execution time. This enables you to create prepared statements and mitigate SQL injection attacks.
Returns the number of rows matching the query criteria.
Returns the number of distinct, non-null field values matching the query criteria.
Returns the average of the column values.
Returns the minimum column value.
Returns the maximum column value.
Returns the total sum of the column values.
The Provider for Snowflake supports standard SQL joins like the following examples.
An inner join selects only rows from both tables that match the join condition:
A left join selects all rows in the FROM table and only matching rows in the JOIN table:
The following date literal functions can be used to filter date fields using relative intervals. Note that while the <, >, and = operators are supported for these functions, <= and >= are not.
The current day.
The previous day.
The following day.
Every day in the preceding week.
Every day in the current week.
Every day in the following week.
Also available:
L_LAST/L_THIS/L_NEXT MONTH
L_LAST/L_THIS/L_NEXT QUARTER
L_LAST/L_THIS/L_NEXT YEAR
The previous n days, excluding the current day.
The following n days, including the current day.
Also available:
L_LAST/L_NEXT_90_DAYS
Every day in every week, starting n weeks before current week, and ending in the previous week.
Every day in every week, starting the following week, and ending n weeks in the future.
Also available:
L_LAST/L_NEXT_N_MONTHS(n)
L_LAST/L_NEXT_N_QUARTERS(n)
L_LAST/L_NEXT_N_YEARS(n)
Bitwise AND of two numeric expressions (a and b).
expr1: This expression must evaluate to a data type that can be cast to INTEGER.
expr2: This expression must evaluate to a data type that can be cast to INTEGER.
Bitwise negation of a numeric expression.
expr: This expression must evaluate to a data type that can be cast to INTEGER.
Bitwise OR of two numeric expressions (a and b).
expr1: This expression must evaluate to a data type that can be cast to INTEGER.
expr2: This expression must evaluate to a data type that can be cast to INTEGER.
Shift the bits for a numeric expression n positions to the left.
expr1:
n:
Shift the bits for a numeric expression n positions to the right, with sign extension.
expr1: This expression must evaluate to a data type that can be cast to INTEGER.
n: The number of bits to shift by.
Bitwise XOR of two numeric expressions (a and b).
expr1:
expr2:
Computes the Boolean AND of two numeric expressions. In accordance with Boolean semantics. Non-zero values (including negative numbers) are regarded as True. Zero values are regarded as False.
expr1:
expr2:
Computes the Boolean NOT of a single numeric expression. In accordance with Boolean semantics: Non-zero values (including negative numbers) are regarded as True. Zero values are regarded as False.
expr1:
Computes the Boolean OR of two numeric expressions. In accordance with Boolean semantics: Non-zero values (including negative numbers) are regarded as True. Zero values are regarded as False.
expr1:
expr2:
Computes the Boolean XOR of two numeric expressions (i.e. one of the expressions, but not both expressions, is TRUE). In accordance with Boolean semantics: Non-zero values (including negative numbers) are regarded as True. Zero values are regarded as False.
expr1:
expr2:
Returns the first non-NULL expression among its arguments, or NULL if all its arguments are NULL.
expr1: Returns the first non-NULL expression among its arguments, or NULL if all its arguments are NULL.
Compares the select expression to each search expression in order. As soon as a search expression matches the selection expression, the corresponding result expression is returned.
expr: This is the "select expression". The "search expressions" are compared to this select expression, and if there is a match then DECODE returns the result that corresponds to that search expression. The select expression is typically a column, but can be a subquery, literal, or other expression.
search1: The search expressions indicate the values to compare to the select expression. If one of these search expressions matches, the function returns the corresponding result. If more than one search expression would match, only the first match's result is returned.
result1: The results are the values that will be returned if one of the search expressions matches the select expression.
search2: The search expressions indicate the values to compare to the select expression. If one of these search expressions matches, the function returns the corresponding result. If more than one search expression would match, only the first match's result is returned.
result2: The results are the values that will be returned if one of the search expressions matches the select expression.
default: If an optional default is specified, and if none of the search expressions match the select expression, then DECODE returns this default value.
Compares whether two expressions are equal. The function is NULL-safe, meaning it treats NULLs as known values for comparing equality. Note that this is different from the EQUAL comparison operator (=), which treats NULLs as unknown values.
expr1:
expr2:
Returns the largest value from a list of expressions. GREATEST supports all types, including VARIANT. The first argument determines the return type. If the first type is numeric, then the return type will be 'widened' according to the numeric types in the list of all arguments. If the first type is not numeric, then all other arguments must be convertible to the first type. If any of the argument values is NULL, the result will be NULL.
expr:
Single-level if-then-else expression. Similar to CASE, but only allows a single condition. If condition evaluates to TRUE, returns expr1, otherwise returns expr2.
condition: The condition is an expression that should evaluate to a BOOLEAN value (True, False, or NULL).
expr1: A general expression. This value is returned if the condition is true.
expr2: A general expression. This value is returned if the condition is false.
If expr1 is NULL, returns expr2, otherwise returns expr1.
expr1: A general expression.
expr2: A general expression.
Returns the smallest value from a list of expressions. LEAST supports all data types, including VARIANT.
expr: The arguments must include at least one expression. All the expressions should be of the same type or compatible types.
Returns NULL if expr1 is equal to expr2, otherwise returns expr1.
expr1: any expression
expr2: any expression
If expr1 is NULL, returns expr2, otherwise returns expr1.
expr1: The expression to be checked to see whether it's NULL.
expr2: If expr1 is NULL, this expression will be evaluated and its value will be returned.
Returns values depending on the nullness of the first argument: If expr1 is not null, then NVL2 returns expr2. If expr1 is null, then NVL2 returns expr3.
expr1: The expression to be checked to see whether it's NULL.
expr2: If expr1 is not NULL, this expression will be evaluated and its value will be returned.
expr3: If expr1 is NULL, this expression will be evaluated and its value will be returned.
If the first argument is NULL, returns NULL. Otherwise, returns the second argument. Contrast REGR_VALX and REGR_VALY with NVL: NVL is a NULL-replacing function. The less commonly used REGR_VALX and REGR_VALY are NULL-preserving functions.
expr1:
expr2:
If the second argument is NULL, returns NULL; otherwise, returns the first argument. Contrast REGR_VALX and REGR_VALY with NVL: NVL is a NULL-replacing function. The less commonly used REGR_VALX and REGR_VALY are NULL-preserving functions.
expr1:
expr2:
Returns 0 if its argument is null; otherwise, returns its argument.
expr:
Returns the version of the client from which the function was called. If called from an application using the JDBC or ODBC driver to connect to Snowflake, returns the version of the driver.
Returns the current date of the system.
Returns the current time for the system.
fract_sec_precision>: This optional argument indicates the precision with which to report the time. For example, a value of 3 says to use 3 digits after the decimal point - i.e. to specify the time with a precision of milliseconds.
Returns the current timestamp for the system.
fract_sec_precision: This optional argument indicates the precision with which to report the time. For example, a value of 3 says to use 3 digits after the decimal point - i.e. to specify the time with a precision of milliseconds.
Returns the current Snowflake version.
Returns the current time for the system. ANSI-compliant alias for CURRENT_TIME.
Returns the current timestamp for the system. ANSI-compliant alias for CURRENT_TIMESTAMP.
Returns the name of the role in use for the current session. To specify a different role for the session, execute the USE ROLE command.
Returns a unique system identifier for the Snowflake session corresponding to the present connection. This will generally be a system-generated alphanumeric string. It is NOT derived from the user name or user account.
Returns the SQL text of the statement that is currently executing.
Returns the transaction id of an open transaction in the current session.
Returns the name of the user currently logged into the system.
Returns the ID of a specified query in the current session. If no query is specified, the most recently-executed query is returned.
num: Specifies the query to return, based on the position of the query (within the session).
Returns the transaction ID of the last transaction that was either committed or rolled back in the current session.
Returns the name of the database in use for the current session. To specify a different database for the session, execute the USE DATABASE command.
Returns the name of the schema in use by the current session. To specify a different schema for the session, execute the USE SCHEMA command.
Returns active search path schemas. For more information about search path, see Object Name Resolution.
Returns the name of the warehouse in use for the current session. To specify a different warehouse for the session, execute the USE WAREHOUSE command.
Converts a value of one data type into another data type. The semantics of CAST are the same as the semantics of the corresponding TO_ datatype conversion functions. If the cast is not possible, an error is raised. For more details, see the individual TO_ datatype conversion functions.
source_expr: Expression of any supported data type to be converted into a different data type.
target_data_type: The data type to which to convert the expression. If the data type supports additional properties, such as scale and precision (for numbers/decimals), the properties can be included.
A special version of CAST , :: that is available for a subset of data type conversions. It performs the same operation (i.e converts a value of one data type into another data type), but returns a NULL value instead of raising an error when the conversion can not be performed.
source_expr: Expression of any supported data type to be converted into a different data type.
target_data_type: The data type to which to convert the expression. If the data type supports additional properties, such as scale and precision (for numbers/decimals), the properties can be included.
Converts the input expression to a string. For NULL input, the output is NULL.
expr: An expression of any data type.
format: The format of the output string
Converts the input expression to a string. For NULL input, the output is NULL.
expr: An expression of any data type.
format: The format of the output string
Converts the input expression to a binary value. For NULL input, the output is NULL.
string_expr: A string expression.
format: The binary format for conversion: HEX, BASE64, or UTF-8 (see Binary Input and Output). The default is the value of the BINARY_INPUT_FORMAT session parameter. If this parameter is not set, the default is HEX
A special version of TO_BINARY that performs the same operation (i.e. converts an input expression to a binary value), but with error handling support (i.e. if the conversion cannot be performed, it returns a NULL value instead of raising an error).
string_expr: A string expression.
format: The binary format for conversion: HEX, BASE64, or UTF-8 (see Binary Input and Output). The default is the value of the BINARY_INPUT_FORMAT session parameter. If this parameter is not set, the default is HEX
Converts an input expression to a fixed-point number. For NULL input, the output is NULL.
expr: An expression of a numeric, character, or variant type.
format: The SQL format model used to parse the input expr and return. For more information, see SQL Format Models.
precision: The maximal number of decimal digits in the resulting number; from 1 to 38. In Snowflake, precision is not used for determination of the number of bytes needed to store the number and does not have any effect on efficiency, so the default is the maximum (38).
scale: The number of fractional decimal digits (from 0 to precision - 1). 0 indicates no fractional digits (i.e. an integer number).
A special version of TO_DECIMAL, TO_NUMBER, TO_NUMERIC that performs the same operation (i.e. converts an input expression to a fixed-point number), but with error-handling support (i.e if the conversion cannot be performed, it returns a NULL value instead of raising an error).
expr: An expression of a numeric, character, or variant type.
format: The SQL format model used to parse the input expr and return. For more information, see SQL Format Models.
precision: The maximal number of decimal digits in the resulting number; from 1 to 38. In Snowflake, precision is not used for determination of the number of bytes needed to store the number and does not have any effect on efficiency, so the default is the maximum (38).
scale: The number of fractional decimal digits (from 0 to precision - 1). 0 indicates no fractional digits (i.e. an integer number).
Converts an expression to a double-precision floating-point number.
expr: An expression of a numeric, character, or variant type.
format: If the expression evaluates to a string, then the function accepts an optional format model. Format models are described at SQL Format Models. The format model specifies the format of the input string, not the format of the output value.
A special version of TO_DOUBLE that performs the same operation (i.e. converts an input expression to a double-precision floating-point number), but with error-handling support (i.e. if the conversion cannot be performed, it returns a NULL value instead of raising an error).
expr: An expression of a numeric, character, or variant type.
format: If the expression evaluates to a string, then the function accepts an optional format model. Format models are described at SQL Format Models. The format model specifies the format of the input string, not the format of the output value.
Coverts the input text or numeric expression to a Boolean value. For NULL input, the output is NULL.
text_or_numeric_expr: A text or numeric expression
A special version of TO_BOOLEAN that performs the same operation (i.e. converts an input expression to a Boolean value), but with error-handling support (i.e. if the conversion cannot be performed, it returns a NULL value instead of raising an error).
text_or_numeric_expr: A text or numeric expression
Converts an input expression to a date
expr: Expression to be converted into a date.
format: Date format specifier for string_expr or AUTO, which specifies for Snowflake to interpret the format.
A special version of TO_DATE that performs the same operation (i.e. converts an input expression to a Boolean value), but with error-handling support (i.e. if the conversion cannot be performed, it returns a NULL value instead of raising an error).
expr: Expression to be converted into a date.
format: Date format specifier for string_expr or AUTO, which specifies for Snowflake to interpret the format.
Converts an input expression into a time. If input is NULL, returns NULL.
expr: Expression to be converted into a time
format: Time format specifier for string_expr or AUTO, which specifies for Snowflake to interpret the format.
A special version of TO_TIME that performs the same operation (i.e. converts an input expression to a Boolean value), but with error-handling support (i.e. if the conversion cannot be performed, it returns a NULL value instead of raising an error).
expr: Expression to be converted into a time
format: Time format specifier for string_expr or AUTO, which specifies for Snowflake to interpret the format.
Converts an input expression into the corresponding timestamp
expr: Expression to be converted into a timestamp
format: Time format specifier for string_expr or AUTO, which specifies for Snowflake to interpret the format.
A special version of TO_TIMESTAMP that performs the same operation (i.e. converts an input expression to a Boolean value), but with error-handling support (i.e. if the conversion cannot be performed, it returns a NULL value instead of raising an error).
expr: Expression to be converted into a timestamp
format: Time format specifier for string_expr or AUTO, which specifies for Snowflake to interpret the format.
Converts an input expression into the corresponding timestamp
expr: Expression to be converted into a timestamp
format: Time format specifier for string_expr or AUTO, which specifies for Snowflake to interpret the format.
A special version of TO_TIMESTAMP_NTZ that performs the same operation (i.e. converts an input expression to a Boolean value), but with error-handling support (i.e. if the conversion cannot be performed, it returns a NULL value instead of raising an error).
expr: Expression to be converted into a timestamp
format: Time format specifier for string_expr or AUTO, which specifies for Snowflake to interpret the format.
Converts an input expression into the corresponding timestamp
expr: Expression to be converted into a timestamp
format: Time format specifier for string_expr or AUTO, which specifies for Snowflake to interpret the format.
A special version of TO_TIMESTAMP_TZ that performs the same operation (i.e. converts an input expression to a Boolean value), but with error-handling support (i.e. if the conversion cannot be performed, it returns a NULL value instead of raising an error).
expr: Expression to be converted into a timestamp
format: Time format specifier for string_expr or AUTO, which specifies for Snowflake to interpret the format.
Converts an input expression into the corresponding timestamp
expr: Expression to be converted into a timestamp
format: Time format specifier for string_expr or AUTO, which specifies for Snowflake to interpret the format.
A special version of TO_TIMESTAMP_LTZ that performs the same operation (i.e. converts an input expression to a Boolean value), but with error-handling support (i.e. if the conversion cannot be performed, it returns a NULL value instead of raising an error).
expr: Expression to be converted into a timestamp
format: Time format specifier for string_expr or AUTO, which specifies for Snowflake to interpret the format.
Each call returns a pseudo-random 64-bit integer.
seed: Seed is an integer. Different seeds will cause RANDOM to produce different output values.
Returns a random string of specified length. Individual characters are chosen uniformly at random from the following pool of characters: 0-9, a-z, A-Z.
length: Length of the string to generate
gen: The value for the generator expression, gen, is used as the seed for this uniform random distribution
Generates either a version 4 (random) or version 5 (named) RFC 4122-compliant UUID as a formatted string.
uuid: The string (known as the namespace)
name: The name of the UUID
Returns a normal-distributed floating point number, with specified mean and stddev (standard deviation).
mean: This is the value that you would like the output values centered around
stddev: This specifies the width of one standard deviation.
gen: This specifies the generator expression for the function.
Returns a uniformly random number, in the inclusive range [min, max].
min: The Minimum Number
max: The Maximum Number
gen: The generator expression for the function
Returns a Zipf-distributed integer, for N elements and characteristic exponent s
s: the characteristic exponent
N: the number of elements
gen: The generator expression for the function
Returns a sequence of monotonically increasing integers, with wrap-around. Wrap-around occurs after the largest representable integer of the integer width (1 byte).
sign: The optional sign argument. If the optional sign argument is 1, the sequence continues at the smallest representable number based on the given integer width. The default sign argument is 0.
Returns a sequence of monotonically increasing integers, with wrap-around. Wrap-around occurs after the largest representable integer of the integer width (2 byte).
sign: The optional sign argument. If the optional sign argument is 1, the sequence continues at the smallest representable number based on the given integer width. The default sign argument is 0.
Returns a sequence of monotonically increasing integers, with wrap-around. Wrap-around occurs after the largest representable integer of the integer width (4 byte).
sign: The optional sign argument. If the optional sign argument is 1, the sequence continues at the smallest representable number based on the given integer width. The default sign argument is 0.
Returns a sequence of monotonically increasing integers, with wrap-around. Wrap-around occurs after the largest representable integer of the integer width (8 byte).
sign: The optional sign argument. If the optional sign argument is 1, the sequence continues at the smallest representable number based on the given integer width. The default sign argument is 0.
Creates a date from individual numeric components that represent the year, month, and day of the month
year: The integer expression to use as a year for building a date.
month: The integer expression to use as a month for building a date, with January represented as 1, and December as 12.
day: The integer expression to use as a day for building a date, usually in the 1-31 range.
Creates a time from individual numeric components.
hour: An integer expression to use as an hour for building a time, usually in the 0-23 range.
minute: An integer expression to use as a minute for building a time, usually in the 0-59 range.
second: An integer expression to use as a second for building a time, usually in the 0-59 range.
nanoseconds: A 9-digit integer expression to use as a nanosecond for building a time
Creates a timestamp from individual numeric components. If no time zone is in effect, the function can be used to create a timestamp from a date expression and a time expression.
date_expr: provides the year, month, and day for the timestamp
time_expr: provides the hour, minute, second, and nanoseconds within the day
Creates a timestamp from individual numeric components. If no time zone is in effect, the function can be used to create a timestamp from a date expression and a time expression.
year: An integer expression to use as a year for building a timestamp.
month: An integer expression to use as a month for building a timestamp, with January represented as 1, and December as 12.
day: An integer expression to use as a day for building a timestamp, usually in the 1-31 range.
hour: An integer expression to use as an hour for building a timestamp, usually in the 0-23 range.
minute: An integer expression to use as a minute for building a timestamp, usually in the 0-59 range.
second: An integer expression to use as a second for building a timestamp, usually in the 0-59 range.
nanoseconds: An integer expression to use as a nanosecond for building a timestamp, usually in the 0-999999999 range.
time_zone: A string expression to use as a time zone for building a timestamp (e.g. America/Los_Angeles)
Creates a timestamp from individual numeric components. If no time zone is in effect, the function can be used to create a timestamp from a date expression and a time expression.
date_expr: provides the year, month, and day for the timestamp
time_expr: provides the hour, minute, second, and nanoseconds within the day
Creates a timestamp from individual numeric components. If no time zone is in effect, the function can be used to create a timestamp from a date expression and a time expression.
year: An integer expression to use as a year for building a timestamp.
month: An integer expression to use as a month for building a timestamp, with January represented as 1, and December as 12.
day: An integer expression to use as a day for building a timestamp, usually in the 1-31 range.
hour: An integer expression to use as an hour for building a timestamp, usually in the 0-23 range.
minute: An integer expression to use as a minute for building a timestamp, usually in the 0-59 range.
second: An integer expression to use as a second for building a timestamp, usually in the 0-59 range.
nanoseconds: An integer expression to use as a nanosecond for building a timestamp, usually in the 0-999999999 range.
Creates a timestamp from individual numeric components. If no time zone is in effect, the function can be used to create a timestamp from a date expression and a time expression.
year: An integer expression to use as a year for building a timestamp.
month: An integer expression to use as a month for building a timestamp, with January represented as 1, and December as 12.
day: An integer expression to use as a day for building a timestamp, usually in the 1-31 range.
hour: An integer expression to use as an hour for building a timestamp, usually in the 0-23 range.
minute: An integer expression to use as a minute for building a timestamp, usually in the 0-59 range.
second: An integer expression to use as a second for building a timestamp, usually in the 0-59 range.
nanoseconds: An integer expression to use as a nanosecond for building a timestamp, usually in the 0-999999999 range.
Creates a timestamp from individual numeric components. If no time zone is in effect, the function can be used to create a timestamp from a date expression and a time expression.
year: An integer expression to use as a year for building a timestamp.
month: An integer expression to use as a month for building a timestamp, with January represented as 1, and December as 12.
day: An integer expression to use as a day for building a timestamp, usually in the 1-31 range.
hour: An integer expression to use as an hour for building a timestamp, usually in the 0-23 range.
minute: An integer expression to use as a minute for building a timestamp, usually in the 0-59 range.
second: An integer expression to use as a second for building a timestamp, usually in the 0-59 range.
nanoseconds: An integer expression to use as a nanosecond for building a timestamp, usually in the 0-999999999 range.
time_zone: A string expression to use as a time zone for building a timestamp (e.g. America/Los_Angeles)
Extracts the specified date or time part from a date, time, or timestamp.
date_or_time_part: The date or time part to extract
date_or_time_expr: The date or time expression to extract from
Extracts the three-letter day-of-week name from the specified date or timestamp.
date_or_timestamp_expr: The date or time expression to extract from
Extracts the corresponding time part from a time or timestamp value.
time_or_timestamp_expr:
Extracts the corresponding time part from a time or timestamp value.
time_or_timestamp_expr:
Extracts the corresponding time part from a time or timestamp value.
time_or_timestamp_expr:
Extracts the corresponding date part from a date or timestamp. These functions are alternatives to using the DATE_PART (or EXTRACT) function with the equivalent date part
date_or_timestamp_expr:
Extracts the corresponding date part from a date or timestamp. These functions are alternatives to using the DATE_PART (or EXTRACT) function with the equivalent date part
date_or_timestamp_expr:
Extracts the corresponding date part from a date or timestamp. These functions are alternatives to using the DATE_PART (or EXTRACT) function with the equivalent date part
date_or_timestamp_expr:
Extracts the corresponding date part from a date or timestamp. These functions are alternatives to using the DATE_PART (or EXTRACT) function with the equivalent date part
date_or_timestamp_expr:
Extracts the corresponding date part from a date or timestamp. These functions are alternatives to using the DATE_PART (or EXTRACT) function with the equivalent date part
date_or_timestamp_expr:
Extracts the corresponding date part from a date or timestamp. These functions are alternatives to using the DATE_PART (or EXTRACT) function with the equivalent date part
date_or_timestamp_expr:
Extracts the corresponding date part from a date or timestamp. These functions are alternatives to using the DATE_PART (or EXTRACT) function with the equivalent date part
date_or_timestamp_expr:
Extracts the corresponding date part from a date or timestamp. These functions are alternatives to using the DATE_PART (or EXTRACT) function with the equivalent date part
date_or_timestamp_expr:
Extracts the corresponding date part from a date or timestamp. These functions are alternatives to using the DATE_PART (or EXTRACT) function with the equivalent date part
date_or_timestamp_expr:
Extracts the corresponding date part from a date or timestamp. These functions are alternatives to using the DATE_PART (or EXTRACT) function with the equivalent date part
date_or_timestamp_expr:
Extracts the corresponding date part from a date or timestamp. These functions are alternatives to using the DATE_PART (or EXTRACT) function with the equivalent date part
date_or_timestamp_expr:
Extracts the corresponding date part from a date or timestamp. These functions are alternatives to using the DATE_PART (or EXTRACT) function with the equivalent date part
date_or_timestamp_expr:
Extracts the corresponding date part from a date or timestamp. These functions are alternatives to using the DATE_PART (or EXTRACT) function with the equivalent date part
date_or_timestamp_expr:
Returns the last day of the specified date part for a date or timestamp. Commonly used to return the last day of the month for a date or timestamp.
date_or_time_expr:
date_part:
Extracts the three-letter month name from the specified date or timestamp.
date_or_timestamp_expr:
Returns the date of the first specified DOW (day of week) that occurs after the input date.
date_or_time_expr: Specifies the input date; can be a date or timestamp.
dow_string: Specifies the day of week used to calculate the date for the previous day. The value can be a string literal or an expression that returns a string. The string must start with the first two characters (case-insensitive) of the day name
Returns the date of the first specified DOW (day of week) that occurs before the input date.
date_or_time_expr: Specifies the input date; can be a date or timestam
dow: Specifies the day of week used to calculate the date for the previous day. The value can be a string literal or an expression that returns a string. The string must start with the first two characters (case-insensitive) of the day name
Adds or subtracts a specified number of months to a date or timestamp, preserving the end-of-month information.
date_or_timestamp_expr: This is the date or timestamp expression to which you want to add a specified number of months.
num_months_expr: This is the number of months you want to add. This should be an integer. It may be positive or negative. If the value is a non-integer numeric value (for example, FLOAT) the value will be rounded to the nearest integer.
Extracts the specified date or time part from a date, time, or timestamp.
date_or_time_part:
date_or_time_expr:
Adds the specified value for the specified date or time part to a date, time, or timestamp.
date_or_time_part: This indicates the units of time that you want to add. For example if you want to add 2 days, then this will be DAY.
value: This is the number of units of time that you want to add. For example, if you want to add 2 days, this will be 2.
date_or_time_expr: must evaluate to a date, time, or timestamp. This is the date, time, or timestamp to which you want to add. For example, if you want to add 2 days to August 1, 2018, then this will be '2018-08-01'::DATE
Adds the specified value for the specified date or time part to a date, time, or timestamp.
date_or_time_part: This indicates the units of time that you want to add. For example if you want to add 2 days, then this will be DAY
value: This is the number of units of time that you want to add. For example, if you want to add 2 days, this will be 2.
date_or_time_expr: must evaluate to a date, time, or timestamp. This is the date, time, or timestamp to which you want to add. For example, if you want to add 2 days to August 1, 2018, then this will be '2018-08-01'::DATE.
Adds the specified value for the specified date or time part to a date, time, or timestamp.
date_or_time_part:
time_value:
date_or_time_expr:
Calculates the difference between two date, time, or timestamp expressions based on the date or time part requested. The function returns the result of subtracting the second argument from the third argument.
date_or_time_part:
date_or_time_expr1: must be a date, a time, a timestamp, or an expression that can be evaluated to a date, a time, or a timestamp. The first value is subtracted from the second value.
date_or_time_expr2: must be a date, a time, a timestamp, or an expression that can be evaluated to a date, a time, or a timestamp. The first value is subtracted from the second value.
Calculates the difference between two date, time, or timestamp expressions based on the specified date or time part.
date_or_time_part:
date_or_time_expr1: must be a date, a time, a timestamp, or an expression that can be evaluated to one of those. The first value is subtracted from the second value
time_expr2: must be a date, a time, a timestamp, or an expression that can be evaluated to one of those.
Calculates the difference between two date, time, or timestamp expressions based on the specified date or time part.
date_or_time_part:
date_or_time_expr1:
date_or_time_expr2:
Truncates a date, time, or timestamp to the specified part. Note that truncation is not the same as extraction. For example: Truncating a timestamp down to the quarter returns the timestamp corresponding to midnight of the first day of the quarter for the input timestamp. Extracting the quarter date part from a timestamp returns the quarter number of the year in the timestamp.
date_or_time_part:
date_or_time_expr:
Converts a timestamp to another time zone
source_tz: String specifying the time zone for the input timestamp. Required for timestamps with no time zone (i.e. TIMEZONE_NTZ)
target_tz: String specifying the time zone to which the input timestamp should be converted.
source_timestamp_ntz: For the 3-argument version, string specifying the timestamp to convert (must be NTZ).
Converts a timestamp to another time zone
target_tz: String specifying the time zone to which the input timestamp should be converted.
source_timestamp: For the 2-argument version, string specifying the timestamp to convert (can be any timestamp variant, including NTZ).
Returns a signed 64-bit hash value. Note that HASH never returns NULL, even for NULL inputs.
expr:
Returns an aggregate signed 64-bit hash value over the (unordered) set of input rows. HASH_AGG never returns NULL, even if no input is provided. Empty input "hashes" to 0.
expr:
Returns a DDL statement that can be used to recreate the specified object. For databases and schemas, GET_DDL is recursive, i.e. it returns the DDL statements for recreating all supported objects within the specified database/schema.
object_type: Specifies the type of object for which the DDL is returned.
namespace_object_name: Specifies the fully-qualified name of the object for which the DDL is returned.
Returns the absolute value of a numeric expression.
num_expr:
Returns values from input_expr rounded to the nearest equal or larger integer, or to the nearest equal or larger value with the specified number of places after the decimal point
input_expr: The input_expr is the value to be rounded up, and should evaluate to a numeric data type, such as FLOAT or NUMBER.
scale_expr: The scale_expr indicates the number of places after the decimal to which to round upward. This should evaluate to an integer.
Returns values from input_expr rounded to the nearest equal or smaller integer, or to the nearest equal or smaller value with the specified number of places after the decimal point
input_expr: The input_expr is the value to be rounded up, and should evaluate to a numeric data type, such as FLOAT or NUMBER.
scale_expr: The scale_expr indicates the number of places after the decimal to which to round upward. This should evaluate to an integer.
Returns the remainder of input expr1 divided by input expr2.
expr1: A numeric expression.
expr2: A numeric expression.
Returns rounded values for input_expr
input_expr: The expression to round
scale_expr: If the optional scale_expr argument is specified, rounding is performed to the specified number of digits (negative digits round to factors-of-10)
Returns the sign of its argument
expr: an expression
Rounds the input expression down to the nearest (or equal) integer towards zero.
input_expr: the expression to truncate
scale_expr: If the optional scale_expr argument is provided, rounding is performed to the specified number of digits (negative digits round to factors-of-10)
Returns the cubic root of a numeric expression.
expr: a numeric expression
Computes Euler's number e raised to a floating-point value.
real_expr: a real expression
Computes the factorial of its input. The input argument must be an integer expression in the range of 0 to 33.
integer_expr: an integer expression
Returns a number (x) raised to the specified power (y).
x:
y:
Returns the square-root of a non-negative numeric expression.
expr: a non-negative numeric expression.
Returns the square of a numeric expression, i.e. a numeric expression multiplied by itself.
expr: a numeric expression
Returns the natural logarithm of a numeric expression.
expr: a numeric expression
Returns the logarithm of a numeric expression.
base: The "base" to use (e.g. 10 for base 10 arithmetic). This can be of any numeric data type (INTEGER, fixed-point, or floating point).
expr: The value for which you want to know the log.
Computes the inverse cosine (arc cosine) of its input; the result is a number in the interval [-pi, pi].
real_expr: This expression should evaluate to a real number greater than or equal to -1.0 and less than or equal to +1.0.
Computes the inverse (arc) hyperbolic cosine of its input.
real_expr: This expression should evaluate to a FLOAT number greater than or equal to 1.0.
Computes the inverse sine (arc sine) of its argument; the result is a number in the interval [-pi, pi].
real_expr: This expression should evaluate to a real number greater than or equal to -1.0 and less than or equal to +1.0.
Computes the inverse (arc) hyperbolic sine of its argument.
real_expr: This expression should evaluate to a real number.
Computes the inverse tangent (arc tangent) of its argument; the result is a number in the interval [-pi, pi].
real_expr: This expression should evaluate to a real number.
Computes the inverse (arc) hyperbolic tangent of its argument.
real_expr:
Computes the cosine of its argument; the argument should be expressed in radians.
real_expr: This expression should evaluate to a real number. The value should be in radians, not degrees.
Computes the hyperbolic cosine of its argument.
real_expr: This expression should evaluate to a real number.
Computes the cotangent of its argument; the argument should be expressed in radians.
real_expr: This expression should evaluate to a real number.
Converts radians to degrees.
real_expr: An expression representing the number of radians.
Converts degrees to radians.
real_expr:
Computes the sine of its argument; the argument should be expressed in radians.
real_expr: This expression should evaluate to a real number. The value should be in radians, not degrees.
Computes the hyperbolic sine of its argument.
real_expr: This expression should evaluate to a real number.
Computes the tangent of its argument; the argument should be expressed in radians.
real_expr: This expression should evaluate to a real number. The value should be in radians, not degrees.
Computes the hyperbolic tangent of its argument.
real_expr: This expression should evaluate to a real number.
Computes the inverse tangent (arc tangent) of the ratio of its two arguments (i.e. ATAN2(x,y) = ATAN(x/y)). The result is a number in the interval [-pi, pi].
real_expr:
real_expr:
Returns the value of pi as a floating-point value.
Calculates the great circle distance in kilometers between two points on the Earth's surface, using the Haversine formula. The two points are specified by their latitude and longitude in degrees.
lat1: Latitude of Point 1
lon1: Longitude of Point 1
lat2: Latitude of Point 2
lon2: Longitude of Point 2
Checks the validity of a JSON document. If the input string is a valid JSON document or a NULL, the output is NULL (i.e. no error). If the input cannot be translated to a valid JSON value, the output string contains the error message.
string_or_variant_expr: A VARIANT or string value (or expression) to check. If the expression is of type VARIANT, it should contain a string.
Checks the validity of an XML document. If the input string is NULL or a valid XML document, the output is NULL. In case of an XML parsing error, the output string contains the error message.
string_or_variant_expr: A VARIANT or string value (or expression) to check. If the expression is of type VARIANT, it should contain a string.
Interprets an input string as a JSON document, producing a VARIANT value.
expr: An expression of string type (e.g. VARCHAR) that holds valid JSON information.
Interprets an input string as an XML document, producing an OBJECT value. If the input is NULL, the output is NULL.
expr: An expression of string type (e.g. VARCHAR) that holds valid JSON information.
Converts a JSON "null" value to a SQL NULL value. All other variant values are passed unchanged.
expr: an expression to strip data from
Returns an array containing all elements from the source array as well as the new element. The new element is located at end of the array.
array: The source array.
new_element: The element to be appended. The element may be of almost any data type. The data type does not need to match the data type(s) of the existing elements in the array.
Returns an array containing the new element as well as all elements from the source array. The new element is positioned at the beginning of the array.
array: The source array.
new_element: The element to be prepended.
Returns a concatenation of two arrays.
array1: The source array.
array2: The array to be appended to array1.
Returns an array containing all elements from the source array as well as the new element.
array: The source array.
pos: A (zero-based) position in the source array. The new element is inserted at this position. The original element from this position (if any) and all subsequent elements (if any) are shifted by one position to the right in the resulting array (i.e. inserting at position 0 has the same effect as using ARRAY_PREPEND). A negative position is interpreted as an index from the back of the array (e.g. -1 results in insertion before the last element in the array).]
new_element: The element to be inserted. The new element is located at position pos. The relative order of the other elements from the source array is preserved.
Returns a compacted array with missing and null values removed, effectively converting sparse arrays into dense arrays.
array1: The source array.
Returns an array constructed from zero, one, or more inputs.
expr1:
expr2:
Returns an array constructed from zero, one, or more inputs.
expr1:
expr2:
Takes a VARIANT and an ARRAY value as inputs and returns True if the VARIANT is contained in the ARRAY.
variant:
array:
Returns the index of the first occurrence of an element in an array.
variant_expr: This expression should evaluate to a VARIANT value. The function searches for the first occurrence of this value in the array.
array: The array to be searched.
Returns the size of the input array. A variation of ARRAY_SIZE takes a VARIANT value as input. If the VARIANT value contains an array, the size of the array is returned; otherwise, NULL is returned if the value is not an array.
array_or_variant:
Returns an array constructed from a specified subset of elements of the input array.
array: The source array of which a subset of the elements are used to construct the resulting array.
from: A position in the source array. The position of the first element is 0. Elements from positions less than from are not included in the resulting array.
to: A position in the source array. Elements from positions equal to or greater than to are not included in the resulting array.
Returns an input array converted to a string by casting all values to strings (using TO_VARCHAR) and concatenating them (using the string from the second argument to separate the elements).
array: The array of elements to convert to a string.
separator_string: The string to put between each element, typically a space, comma, or other human-readable separator.
Compares whether two arrays have at least one element in common. Returns TRUE if there is at least one element in common; otherwise returns FALSE. The function is NULL-safe, meaning it treats NULLs as known values for comparing equality.
array1: an array
array2: an array
Returns one OBJECT per group. For each (key, value) input pair, where key must be a VARCHAR and value must be a VARIANT, the resulting OBJECT contains a key:value field.
key:
value:
Returns an object constructed from the arguments.
key1:
value1:
keyN:
valueN:
Returns an object containing the contents of the input (i.e.source) object with one or more keys removed.
object: The source object.
key1: Key to be omitted from the returned object.
key2: Key to be omitted from the returned object.
Returns an object consisting of the input object with a new key-value pair inserted (or an existing key updated with a new value).
object: The source object into which the new key-value pair is inserted.
key: The new key to be inserted into the object. Must be different from all existing keys in the object, unless updateFlag is set to TRUE.
value: The value associated with the key.
updateFlag: Boolean flag that, when set to TRUE, specifies the input value is used to update/overwrite an existing key in the object, rather than inserting a new key-value pair.
Extracts an XML element object (often referred to as simply a "tag") from a content of outer XML element object by the name of the tag and its instance number (counting from 0)
type:
tag_name:
instance_num: can be omitted, in which case the default value 0 is used.
Extracts a value from an object or array; returns NULL if either of the arguments is NULL.
expr1: An Object, Variant, or Array
expr2: A string value or an integer, which can be a constant or an expression
Casts a VARIANT value to an array.
variant_expr: An expression that evaluates to a value of type VARIANT.
Casts a VARIANT value to a binary string.
variant_expr: An expression that evaluates to a value of type VARIANT.
Casts a VARIANT value to a string. Does not convert values of other types into string.
variant_expr: An expression that evaluates to a value of type VARIANT.
Casts a VARIANT value to a string. Does not convert values of other types into string.
variant_expr: An expression that evaluates to a value of type VARIANT.
Casts a VARIANT value to a date. Does not convert from timestamps.
variant_expr: An expression that evaluates to a value of type VARIANT.
Casts a VARIANT value to a floating-point value.
variant_expr: An expression that evaluates to a value of type VARIANT.
Casts a VARIANT value to a floating-point value.
variant_expr: An expression that evaluates to a value of type VARIANT.
Casts a VARIANT value to an integer. Does not match non-integer values.
variant_expr: An expression that evaluates to a value of type VARIANT.
Casts a VARIANT value to an object.
variant_expr: An expression that evaluates to a value of type VARIANT.
Casts a VARIANT value to a time value. Does not convert from timestamps.
variant_expr: An expression that evaluates to a value of type VARIANT.
Casts a VARIANT value to the respective TIMESTAMP value with local time zone.
variant_expr: An expression that evaluates to a value of type VARIANT.
Casts a VARIANT value to the respective TIMESTAMP value with no time zone.
variant_expr: An expression that evaluates to a value of type VARIANT.
Casts a VARIANT value to the respective TIMESTAMP value with time zone.
variant_expr: An expression that evaluates to a value of type VARIANT.
Casts a VARIANT value to a fixed-point decimal (does not match floating-point values), with optional precision and scale.
variant_expr: An expression that evaluates to a value of type VARIANT.
precision: The number of significant digits of the decimal number to store.
scale: The number of significant digits after the decimal point.
Casts a VARIANT value to a fixed-point decimal (does not match floating-point values), with optional precision and scale.
variant_expr: An expression that evaluates to a value of type VARIANT.
precision: The number of significant digits of the decimal number to store.
scale: The number of significant digits after the decimal point.
Tokenizes the given string using the given set of delimiters and returns the tokens as an array. If either parameter is a NULL, a NULL is returned. An empty array is returned in case tokenization produces no tokens.
string: Text to be tokenized.
delimiter: Set of delimiters. Optional. Default value is a single space character
Tokenizes a string with the given set of delimiters and flattens the results into rows.
string: Text to be tokenized.
delimiter: Set of delimiters. Optional. Default value is a single space character
Converts the input expression into an array: If the input is an ARRAY, or VARIANT containing an array value, the result is unchanged. For NULL or a JSON null input, returns NULL. For any other value, the result is a single-element array containing this value.
expr: An expression of any data type.
Converts any VARIANT value to a string containing the JSON representation of the value. If the input is NULL, the result is also NULL.
expr: An expression of type VARIANT that holds valid JSON information.
Converts the input value to an object
expr: An expression of any data type.
Converts any value to VARIANT value or NULL (if input is NULL).
expr: An expression of any data type.
Converts any VARIANT value to a string containing the XML representation of the value. If the input is NULL, the result is also NULL.
expr1: The Variant to convert
Returns TRUE if its VARIANT argument contains an ARRAY value.
variant_expr: An expression that evaluates to a value of type VARIANT.
Returns TRUE if its VARIANT argument contains an Boolean value.
variant_expr: An expression that evaluates to a value of type VARIANT.
Returns TRUE if its VARIANT argument contains an binary value.
variant_expr: An expression that evaluates to a value of type VARIANT.
Returns TRUE if its VARIANT argument contains an string value.
variant_expr: An expression that evaluates to a value of type VARIANT.
Returns TRUE if its VARIANT argument contains an string value.
variant_expr: An expression that evaluates to a value of type VARIANT.
Returns TRUE if its VARIANT argument contains an DATE value.
variant_expr: An expression that evaluates to a value of type VARIANT.
Returns TRUE if its VARIANT argument contains an DATE value.
variant_expr: An expression that evaluates to a value of type VARIANT.
Returns TRUE if its VARIANT argument contains an fixed-point decimal value or integer value.
variant_expr: An expression that evaluates to a value of type VARIANT.
Returns TRUE if its VARIANT argument contains an a floating-point value, fixed-point decimal, or integer value.
variant_expr: An expression that evaluates to a value of type VARIANT.
Returns TRUE if its VARIANT argument contains an a floating-point value, fixed-point decimal, or integer value.
variant_expr: An expression that evaluates to a value of type VARIANT.
Returns TRUE if its VARIANT argument contains an integer value.
variant_expr: An expression that evaluates to a value of type VARIANT.
Returns TRUE if its VARIANT argument contains an NULL value.
variant_expr: An expression that evaluates to a value of type VARIANT.
Returns TRUE if its VARIANT argument contains an OBJECT value.
variant_expr: An expression that evaluates to a value of type VARIANT.
Returns TRUE if its VARIANT argument contains an TIME value.
variant_expr: An expression that evaluates to a value of type VARIANT.
Verifies whether a VARIANT value contains the respective TIMESTAMP value
variant_expr: An expression that evaluates to a value of type VARIANT.
Verifies whether a VARIANT value contains the respective TIMESTAMP value
variant_expr: An expression that evaluates to a value of type VARIANT.
Verifies whether a VARIANT value contains the respective TIMESTAMP value
variant_expr: An expression that evaluates to a value of type VARIANT.
Reports the type of a value stored in a VARIANT column. The type is returned as a string.
column: The column to detect the type of
Returns the number of times that a pattern occurs in a string.
subject: Subject to match.
pattern: Pattern to match.
position: Number of characters from the beginning of the string where the function starts searching for matches. Default: 1 (the search for a match starts at the first character on the left)
parameters: String of one or more characters that specifies the parameters used for searching for matches
Returns the position of the specified occurrence of the regular expression pattern in the string subject. If no match is found, returns 0.
subject: Subject to match.
pattern: Pattern to match.
position: Number of characters from the beginning of the string where the function starts searching for matches. Default: 1
occurrence: Specifies which occurrence of the pattern to match. The function skips the first occurrence -1 matches. Default: 1
option: Specifies whether to return the offset of the first character of the match (0) or the offset of the first character following the end of the match (1). Default: 0
parameters: String of one or more characters that specifies the parameters used for searching for matches. Supported values: c, i, m, e, s
Returns true if the subject matches the pattern. Both expressions must be text expressions.
subject: Subject to match.
pattern: Pattern to match.
parameters: String of one or more characters that specifies the parameters used for searching for matches. Supported values: c, i, m, e, s
Returns the subject with the specified pattern (or all occurrences of the pattern) either removed or replaced by a replacement string. If no matches are found, returns the original subject.
subject: Subject to match.
pattern: Pattern to match.
replacement: String the replaces the substrings matched by the pattern. If an empty string is specified, the function removes all matched patterns and returns the resulting string. Default: '' (empty string).
position: Number of characters from the beginning of the string where the function starts searching for matches. Default: 1 (the search for a match starts at the first character on the left)
occurrence: Specifies which occurrence of the pattern to replace. If 0 is specified, all occurrences are replaced. Default: 0 (all occurrences)
parameters: String of one or more characters that specifies the parameters used for searching for matches. Supported values: c, i, m, e, s
Returns the position of the specified occurrence of the regular expression pattern in the string subject. If no match is found, returns 0.
subject: Subject to match.
pattern: Pattern to match.
position: Number of characters from the beginning of the string where the function starts searching for matches. Default: 1
occurrence: Specifies which occurrence of the pattern to match. The function skips the first occurrence -1 matches. Default: 1
parameters: String of one or more characters that specifies the parameters used for searching for matches. Supported values: c, i, m, e, s
Returns true if the subject matches the specified pattern. Both inputs must be text expressions.
subject: Subject to match
pattern: Pattern to match.
parameters: String of one or more characters that specifies the parameters used for searching for matches. Supported values: c, i, m, e, s
Returns the ASCII code for the first character of a string. If the string is empty, a value of 0 is returned.
input: The string for which the ASCII code for the first character in the string is returned.
Returns the length of a string or binary value in bits. Snowflake doesn't use fractional bytes so length is always calculated as 8 * OCTET_LENGTH.
string_or_binary: The string or binary value for which the length is returned.
Searches for the first occurrence of the first argument in the second argument and, if successful, returns the position (1-based) of the first argument in the second argument.
expr1: A string or binary expression representing the value we are looking for.
expr2: A string or binary expression representing the value in which we are searching.
start_pos: A number indicating the position from where to start the search (with 1 representing the start of expr1). Default: 1
Converts a Unicode code point (including 7-bit ASCII) into the character that matches the input Unicode. If an invalid code point is specified, an error is returned.
input: The Unicode code point for which the character is returned.
Converts a Unicode code point (including 7-bit ASCII) into the character that matches the input Unicode. If an invalid code point is specified, an error is returned.
input: The Unicode code point for which the character is returned.
Concatenates two strings or two binary values. If one of the them is null, the result is also null.
expr1: A string to concatenate
expr2: A string to concatenate
Returns true if expr1 contains expr2. Both expressions must be text or binary expressions.
expr1: A string or binary expression representing the value we are looking fo
expr2: A string or binary expression representing the value in which we are searching.
Computes the Levenshtein distance between two input strings. It is the number of single-character insertions, deletions or substitutions needed to convert one string to another.
expr1: A string
expr2: A string
Returns TRUE if the first expression ends with second expression. Both expressions must be text or binary expressions.
expr1: A string or binary expression representing the value we are looking fo
expr2: A string or binary expression representing the value in which we are searching.
Allows matching of strings based on comparison with a pattern. Unlike the LIKE function, string matching is case-insensitive. LIKE, ILIKE, and RLIKE all perform similar operations; however, RLIKE uses POSIX EXE (Extended Regular Expression) syntax instead of the SQL pattern syntax used by LIKE and ILIKE.
subject: Subject to match.
pattern: Pattern to match.
escape: Character(s) inserted in front of a wildcard character to indicate that the wildcard should be interpreted as a regular character and not as a wildcard.
Returns the input string (expr) with the first letter of each word in uppercase and the subsequent letters in lowercase.
expr: The input string
delimiters: an optional argument specifying a string of one or more characters that INITCAP uses as separators for words in the input expression:
Replaces a substring of the specified length, starting at the specified position, with a new string or binary value. This function should not be confused with the INSERT DML command.
base_expr: The string or BINARY expression for which you want to insert/replace characters.
pos: The offset at which to start inserting characters. This is 1-based, not 0-based.
len: The number of characters (starting at pos) that you want to replace. Valid values range from 0 to the number of characters between pos and the end of the string.
insert_expr: The string to insert into the base_expr. If this string is empty, and if len is greater than zero, then effectively the operation becomes a delete (some characters are deleted, and none are added).
Returns a leftmost substring of its input. LEFT(STR,N) is equivalent to SUBSTR(STR,1,N).
string_expr: The string expression
length_expr: The length to extract
Returns the length of a input string or binary value. For strings, the length is the number of characters, and UTF-8 characters are counted as a single character. For binary, the length is the number of bytes.
expr: The expression to measure
Allows case-sensitive matching of strings based on comparison with a pattern. For case-insensitive matching, use ILIKE instead.
subject: Subject to match.
pattern: Pattern to match.
escape: Character(s) inserted in front of a wildcard character to indicate that the wildcard should be interpreted as a regular character and not as a wildcard.
Returns the input string (expr) with all characters converted to lowercase.
expr: The input string
Left-pads a string with characters from another string, or left-pads a binary value with bytes from another binary value.
base: The base string to pad
n: The number of characters to bad
pad: The character to pad
Removes leading characters, including whitespace, from a string.
expr: The string expression to be trimmed.
characters: One or more characters to remove from the left side of expr. The default value is ' ' (a single blank space character),
Returns the length of a string or binary value in bytes. This will be the same as LENGTH for ASCII strings and greater than LENGTH for strings using Unicode code points. For binary, this is always the same as LENGTH.
string_or_binary: The string or binary value for which the length is returned.
Returns a JSON object consisting of all the components from a valid INET (Internet Protocol) or CIDR (Classless Internet Domain Routing) IPv4 or IPv6 string.
expr: A string expression.
type: Identifies the type of IP address. Supports either INET or CIDR; case-insenstitive.
permissive: Flag that determines how parse errors are handled:
Returns a JSON object consisting of all the components (fragment, host, path, port, query, scheme) in a valid input URL/URI.
string: String to parse.
permissive: Flag that determines how parse errors are handled
Searches for the first occurrence of the first argument in the second argument and, if successful, returns the position (1-based) of the first argument in the second argument.
expr1: A string or binary expression representing the value we are looking for.
expr2: A string or binary expression representing the value in which we are searching.
start_pos: A number indicating the position from where to start the search (with 1 representing the start of expr2).
Searches for the first occurrence of the first argument in the second argument and, if successful, returns the position (1-based) of the first argument in the second argument.
expr1: A string or binary expression representing the value we are looking for.
expr2: A string or binary expression representing the value in which we are searching.
Builds a string by repeating the input for the specified number of times.
input: The input string from which the output string is built.
n: The number of times the input string should be repeated. The minimum valid number is 0 (which results in an empty string).
Removes all occurrences of a specified substring, and optionally replaces them with another string
subject: The subject is the string in which to do the replacements. Typically, this is a column, but it can be a literal.
pattern: This is the substring that you want to replace. Typically, this is a literal, but it can be a column or expression. Note that this is not a "regular expression"; if you want to use regular expressions to search for a pattern, use the REGEXP_REPLACE function.
replacement: This is the value used as a replacement for the pattern. If this is omitted, or is an empty string, then the REPLACE function simply deletes all occurrences of the pattern.
everses the order of characters in a string, or of bytes in a binary value.
subject: The string to reverse
Returns a rightmost substring of its input.
string_expr: The string expression
length_expr: The length to extract
Right-pads a string or binary value with characters from another string.
base: The base string to pad
n: The number of times to pad
pad: The string to pad with
Removes trailing characters, including whitespace, from a string.
expr: The string expression to be trimmed.
characters: One or more characters to remove from the right side of expr:
Returns the length of its argument, minus trailing whitespace, but including leading whitespace.
string_expr: The string expression to measure
Builds a string consisting of the specified number of blank spaces.
n: The number of blank spaces used to build the string.
Splits a given string with a given separator and returns the result in an array of strings. Contiguous split strings in the source string, or the presence of a split string at the beginning or end of the source string, results in an empty string in the output. An empty separator string results in an array containing only the source string. If either parameter is a NULL, a NULL is returned.
string: Text to be split into parts.
separator: Text to split string by.
Splits a string with the given delimiter and flattens the results into rows.
string: Text to be split into parts.
delimiter: Text to split string by.
Tokenizes a given string and returns the requested part.
string: Text to be tokenized.
delimiter: Text representing the set of delimiters to tokenize on. Each character in the delimiter string is a delimiter. If the delimiter is empty, and the string is empty, then the function returns NULL. If the delimiter is empty, and the string is non empty, then the whole string will be treated as one token. The default value of the delimiter is a single space character.
partNr: Requested token (1-based, i.e. the first token is token number 1, not token number 0). If the token number is out of range, then NULL is returned. The default value is 1.
Splits a given string and returns the requested part. If a part does not exist, an empty string is returned. If any parameter is NULL, NULL is returned.
string: Text to be split into parts.
delimiter: Text representing the delimiter to split by.
partNr: Requested part of the split (1-based). 0 is treated as 1. If the value is negative, the parts are counted from the right side of the string.
Returns true if expr1 starts with expr2. Both expressions must be text or binary expressions.
expr1: A string or binary expression representing the value we are looking fo
expr2: A string or binary expression representing the value in which we are searching.
Returns the portion of the string or binary value from base_expr, starting from the character/byte specified by start_expr, with optionally limited length.
base_expr: The base string
start_expr: The position to start from
length_expr: Up to length_expr characters/bytes are returned, otherwise all the characters until the end of the string or binary value are returned.
Returns the portion of the string or binary value from base_expr, starting from the character/byte specified by start_expr, with optionally limited length.
input: A BINARY or string value (or expression) to be compressed.
method: A string with compression method and optional compression level. Supported methods are: SNAPPY. ZLIB. ZSTD. BZ2. The compression level is specified in parentheses, for example: zlib(1). The compression level is a non-negative integer. 0 means default level (same as omitting the compression level). The compression level is ignored if the method doesn't support compression levels.
Returns the portion of the string or binary value from base_expr, starting from the character/byte specified by start_expr, with optionally limited length.
input: A BINARY value (or expression) with data that was compressed using one of the compression methods specified in COMPRESS. If you attempt to decompress a compressed string, rather than a compressed BINARY value, you will not get an error; the function will return a BINARY value. See the Usage Notes below for details.
method: The compression method originally used to compress the input.
Decompresses the compressed BINARY input parameter to a string.
input: A BINARY value (or expression) with data that was compressed using one of the compression methods specified in COMPRESS.
method: The compression method originally used to compress the input.
Returns the portion of the string or binary value from base_expr, starting from the character/byte specified by start_expr, with optionally limited length.
base_expr: The base string
start_expr: The position to start from
length_expr: Up to length_expr characters/bytes are returned, otherwise all the characters until the end of the string or binary value are returned.
Translates subject from the characters in sourceAlphabet to the characters in targetAlphabet.
subject: A string expression that is translated. If a character in subject is not contained in sourceAlphabet, the character is added to the result without any translation.
sourceAlphabet: A string with all characters that are modified by this function. Each character is either translated to the corresponding character in the targetAlphabet or omitted in the result if the targetAlphabet has no corresponding character (i.e. has less characters than the sourceAlphabet).
targetAlphabet: A string with all characters that are used to replace characters from the sourceAlphabet.
Removes leading and trailing characters from a string.
expr: A string expression to be trimmed.
characters: One or more characters to remove from the left and right side of expr: The default value is ' ' (a single blank space character), i.e. if no characters are specified, all leading and trailing blank spaces are removed.
Returns the Unicode code point for the first Unicode character in a string. If the string is empty, a value of 0 is returned.
input: The string for which the Unicode code point for the first character in the string is returned.
Returns the input string expr with all characters converted to uppercase.
expr: The input string
Decodes a Base64-encoded string to a binary.
input: A Base64-encoded string expression.
alphabet: A string consisting of up to three ASCII characters
Decodes a Base64-encoded string to a binary.
input: A Base64-encoded string expression.
alphabet: A string consisting of up to three ASCII characters
Encodes the input (string or binary) using Base64 encoding.
input: A string or binary expression to be encoded.
max_line_length: A positive integer that specifies the maximum number of characters in a single line of the output.
alphabet: A string consisting of up to three ASCII characters
Decodes a hex-encoded string to a binary.
input: A string expression containing only hexadecimal digits. Typically, this input string is generated by calling the function HEX_ENCODE.
Decodes a hex-encoded string to a string.
input: A hex-encoded string expression. Typically the input was created by a call to HEX_ENCODE.
Encodes the input using hexadecimal (also 'hex' or 'base16') encoding. The result is comprised of 16 different symbols: The numbers '0' to '9' as well as the letters 'A' to 'F'
input: A binary or string expression to be encoded.
case: This optional boolean argument controls the case of the letters ('A', 'B', 'C', 'D', 'E' and 'F') used in the encoding. The default value is 1 and indicates that uppercase letters are used. The value 0 indicates that lowercase letters are used. All other values are illegal and result in an error.
A special version of BASE64_DECODE_BINARY that returns a NULL value if an error occurs during decoding.
input: A Base64-encoded string expression.
alphabet: A string consisting of up to three ASCII characters
A special version of BASE64_DECODE_BINARY that returns a NULL value if an error occurs during decoding
input: A Base64-encoded string expression.
alphabet: A string consisting of up to three ASCII characters
A special version of HEX_DECODE_BINARY that returns a NULL value if an error occurs during decoding.
input: A string expression containing only hexadecimal digits. Typically, this input string is generated by calling the function HEX_ENCODE.
A special version of HEX_DECODE_BINARY that returns a NULL value if an error occurs during decoding.
input: A hex-encoded string expression. Typically the input was created by a call to HEX_ENCODE.
Returns a 32-character hex-encoded string containing the 128-bit MD5 message digest.
msg: A string expression, the message to be hashed.
Returns a 32-character hex-encoded string containing the 128-bit MD5 message digest.
msg: A string expression, the message to be hashed
Returns a 16-byte BINARY value containing the 128-bit MD5 message digest.
msg: A string expression, the message to be hashed.
Returns the 128-bit MD5 message digest interpreted as a signed 128-bit big endian number. This representation is useful for maximally efficient storage and comparison of MD5 digests.
msg: A string expression, the message to be hashed.
Returns a 40-character hex-encoded string containing the 160-bit SHA-1 message digest.
msg: A string expression, the message to be hashed.
Returns a 40-character hex-encoded string containing the 160-bit SHA-1 message digest.
msg: A string expression, the message to be hashed.
Returns a 20-byte binary containing the 160-bit SHA-1 message digest.
msg: A string expression, the message to be hashed.
Returns a hex-encoded string containing the N-bit SHA-2 message digest, where N is the specified output digest size.
msg: A string expression, the message to be hashed
digest_size: Size (in bits) of the output, corresponding to the specific SHA-2 function used to encrypt the string
Returns a hex-encoded string containing the N-bit SHA-2 message digest, where N is the specified output digest size.
msg: A string expression, the message to be hashed
digest_size: Size (in bits) of the output, corresponding to the specific SHA-2 function used to encrypt the string
Returns a binary containing the N-bit SHA-2 message digest, where N is the specified output digest size.
msg: A string expression, the message to be hashed
digest_size: Size (in bits) of the output, corresponding to the specific SHA-2 function used to encrypt the string
Returns some value of the expression from the group. The result is non-deterministic.
expr: A group of values to choose from
Returns the average of non-NULL records. If all records inside a group are NULL, NULL is returned.
expr: Expression is an expression that evaluates to a numeric data type (INTEGER, FLOAT, DECIMAL, etc.).
Returns the correlation coefficient for non-null pairs in a group. It is computed for non-null pairs using the following formula: COVAR_POP(y, x) / (STDDEV_POP(x) * STDDEV_POP(y))
y:
c:
Returns either the number of non-NULL records for the specified columns, or a total number of records.
expr1: A column name.
expr2: You may include additional column name(s) if you wish. For example, you could list the number of distinct combinations of last name and first name.
Returns the population covariance for non-null pairs in a group. It is computed for non-null pairs using the following formula: (SUM(x*y) - SUM(x) * SUM(y) / COUNT(*)) / COUNT(*) Where x is the independent variable and y is the dependent variable.
x:
y:
Returns the sample covariance for non-null pairs in a group. It is computed for non-null pairs using the following formula: (SUM(x*y) - SUM(x) * SUM(y) / COUNT(*)) / (COUNT(*) - 1) Where x is the independent variable and y is the dependent variable.
y:
x:
Returns the concatenated input values, separated by the delimiter string.
expr: The expression (typically a column name) that determines the values to be put into the list. The expression should evaluate to a string, or to a data type that can be cast to string.
delimiter: A string, or an expression that evaluates to a string. In practice, this is usually a single-character string. The string should be surrounded by single quotes, as shown in the examples below.
Returns the minimum or maximum value for the records within expr. NULL values are ignored unless all the records are NULL, in which case a NULL value is returned.
expr:
Returns the minimum or maximum value for the records within expr. NULL values are ignored unless all the records are NULL, in which case a NULL value is returned.
expr:
Determines the median of a set of values.
expr: The expression must evaluate to a numeric data type (INTEGER, FLOAT, DECIMAL, or equivalent).
Return a percentile value based on a continuous distribution of the input column (specified in order_by_expr). If no input row lies exactly at the desired percentile, the result is calculated using linear interpolation of the two nearest input values. NULL values are ignored in the calculation.
percentile: The percentile of the value that you want to find. The percentile must be a constant between 0.0 and 1.0. For example, if you want to find the value at the 90th percentile, specify 0.9.
Returns a percentile value based on a discrete distribution of the input column (specified in order_by_expr). The returned value is that whose row has the smallest CUME_DIST value that is greater than or equal to the given percentile. NULL values are ignored in the calculation.
percentile: The percentile of the value that you want to find. The percentile must be a constant between 0.0 and 1.0. For example, if you want to find the value at the 90th percentile, specify 0.9.
Returns the sample standard deviation (square root of sample variance) of non-NULL values. If all records inside a group are NULL, returns NULL.
expr: An expression that evaluates to a numeric value.
Returns the sample standard deviation (square root of sample variance) of non-NULL values. If all records inside a group are NULL, returns NULL.
expr: An expression that evaluates to a numeric value (integer, floating point, or fixed point).
Returns the population standard deviation (square root of variance) of non-NULL values. If all records inside a group are NULL, returns NULL.
x:
Returns the sum of non-NULL records for expr. If the DISTINCT keyword is used, the sum of unique non-null values is computed. If all records inside a group are NULL, a value of NULL is returned.
expr: The expression to sum
Returns the population variance of non-NULL records in a group. If all records inside a group are NULL, a NULL is returned.
x:
Returns the population variance of non-NULL records in a group. If all records inside a group are NULL, a NULL is returned.
x:
Returns the sample variance of non-NULL records in a group. If all records inside a group are NULL, a NULL is returned.
x:
Returns the sample variance of non-NULL records in a group. If all records inside a group are NULL, a NULL is returned.
x:
Returns the sample variance of non-NULL records in a group. If all records inside a group are NULL, a NULL is returned.
x:
Finds the cumulative distribution of a value with regard to other values within the same window partition.
Returns the rank of a value within a group of values, without gaps in the ranks. The rank value starts at 1 and continues up sequentially. If two values are the same, they will have the same rank.
Returns the first value within an ordered group of values.
expr:
Accesses data in a previous row in the same result set without having to join the table to itself.
expr: The string expression to be returned.
offset: The number of rows backward from the current row from which to obtain a value; e.g., an offset of 2 returns the expr value with an interval of 2 rows.
default: The expression to return when the offset goes out of the bounds of the window. Supports any expression whose type is compatible with expr
Returns the last value within an ordered group of values.
expr:
Accesses data in a subsequent row in the same result set without having to join the table to itself.
expr: The string expression to be returned.
offset: The number of rows forward from the current row from which to obtain a value; e.g., an offset of 2 returns the expr value with an interval of 2 rows.
default: The expression to return when the offset goes out of the bounds of the window. Supports any expression whose type is compatible with expr.
Returns the nth value (up to 1000) within an ordered group of values.
expr:
n: Input value n cannot be greater than 1000.
Divides an ordered data set equally into the number of buckets specified by constant_value. Buckets are sequentially numbered 1 through constant_value.
constant_value: The desired number of buckets; must be a positive integer value.
Returns the relative rank of a value within a group of values.
Returns the rank of a value within an ordered group of values.
Returns a unique row number for each row within a window partition.
Constructs equi-width histograms, in which the histogram range is divided into intervals of identical size, and returns the bucket number into which the value of an expression falls, after it has been evaluated. The function returns an integer value or null (if any input is null).
expr: The expression for which the histogram is created. This expression must evaluate to a numeric value or to a value that can be implicitly converted to a numeric value.
min_value: The low and high end points of the acceptable range for the expression. The end points must also evaluate to numeric values and not be equal.
max_value: The low and high end points of the acceptable range for the expression. The end points must also evaluate to numeric values and not be equal.
num_buckets: The desired number of buckets; must be a positive integer value. A value from the expression is assigned to each bucket, and the function then returns the corresponding bucket number.
Returns the bitwise AND value of all non-NULL numeric records in a group. If all records inside the group are NULL, or if the group is empty, the function returns NULL.
expr:
Returns the bitwise OR value of all non-NULL numeric records in a group. If all records inside the group are NULL, or if the group is empty, the function returns NULL.
expr:
Returns the bitwise XOR value of all non-NULL numeric records in a group. If all records inside the group are NULL, or if the group is empty, the function returns NULL.
expr:
Returns the input values, pivoted into an ARRAY. If the input is empty, an empty ARRAY is returned.
expr: The expression (typically a column name) that determines the values to be put into the list
Returns the average of the independent variable for non-null pairs in a group, where x is the independent variable and y is the dependent variable.
y:
x:
Returns the number of non-null number pairs in a group.
y:
x:
Returns the intercept of the univariate linear regression line for non-null pairs in a group. It is computed for non-null pairs using the following formula: AVG(y)-REGR_SLOPE(y,x)*AVG(x) Where x is the independent variable and y is the dependent variable
y:
x:
Returns the coefficient of determination for non-null pairs in a group.
y:
x:
Returns the slope of the linear regression line for non-null pairs in a group
y:
x:
Returns REGR_COUNT(y, x) * VAR_POP(x) for non-null pairs.
y:
x:
Returns REGR_COUNT(expr1, expr2) * COVAR_POP(expr1, expr2) for non-null pairs.
y:
x:
Returns REGR_COUNT(y, x) * VAR_POP(y) for non-null pairs.
y:
x:
Returns the average of the dependent variable for non-null pairs in a group, where x is the independent variable and y is the dependent variable.
y:
x:
Uses HyperLogLog to return an approximation of the distinct cardinality of the input.
expr:
Uses HyperLogLog to return an approximation of the distinct cardinality of the input.
expr:
Combines (merges) input states into a single output state. This allows scenarios where HLL_ACCUMULATE is run over horizontal partitions of the same table, producing an algorithm state for each table partition. These states can later be combined using HLL_COMBINE, producing the same output state as a single run of HLL_ACCUMULATE over the entire table.
state: An expression that contains state information generated by a call to HLL_ACCUMULATE.
Returns the cardinality estimate for the given HyperLogLog state. A HyperLogLog state produced by HLL_ACCUMULATE and HLL_COMBINE can be used to compute a cardinality estimate using the HLL_ESTIMATE function.
state: An expression that contains state information generated by a call to HLL_ACCUMULATE or HLL_COMBINE.
Returns the HyperLogLog state at the end of aggregation.
expr:
Converts input in BINARY format to OBJECT format. The HyperLogLog states operated on by HLL_ACCUMULATE, HLL_COMBINE, and HLL_ESTIMATE are in a proprietary binary format that may change in future versions of Snowflake. For long-term storage of HyperLogLog states, and for integration with external tools, Snowflake supports converting states from the BINARY format to an OBJECT (which can be printed and exported as JSON), and vice versa.
binary_expr:
Converts input in OBJECT format to BINARY format. The HyperLogLog states operated on by HLL_ACCUMULATE, HLL_COMBINE, and HLL_ESTIMATE are in a proprietary binary format that may change in future versions of Snowflake. For long-term storage of HyperLogLog states, and for integration with external tools, Snowflake supports using HLL_IMPORT to convert states from an OBJECT format to BINARY, and vice versa.
obj:
Returns an estimation of the similarity (Jaccard index) of inputs based on their MinHash states. For more information about Jaccard indexes and the related function MINHASH, see Estimating Similarity of Two or More Sets.
expr: The expression(s) should be one or more MinHash states returned by calls to the MINHASH function. In other words, the expressions must be MinHash state information, not the column or expression for which you want the approximate similarity. (The example below helps make this clear.)
Returns an estimation of the similarity (Jaccard index) of inputs based on their MinHash states. For more information about Jaccard indexes and the related function MINHASH, see Estimating Similarity of Two or More Sets.
expr: The expression(s) should be one or more MinHash states returned by calls to the MINHASH function. In other words, the expressions must be MinHash state information, not the column or expression for which you want the approximate similarity. (The example below helps make this clear.)
Returns a MinHash state containing an array of size k constructed by applying k number of different hash functions to the input rows and keeping the minimum of each hash function. This MinHash state can then be input to the APPROXIMATE_SIMILARITY function to estimate the similarity with one or more other MinHash states.
k: specifies the number of hash functions to be created. The larger the value, the better the approximation; however, this value has a linear impact on the computation time for estimating similarity using APPROXIMATE_SIMILARITY. The suggested value is 100.
expr:
Combines input MinHash states into a single MinHash output state. This Minhash state can then be input to the APPROXIMATE_SIMILARITY function to estimate the similarity with other MinHash states.
state: Input MinHash state must have MinHash arrays of equal length.
Uses Space-Saving to return an approximation of the most frequent values in the input, along with their approximate frequencies. The output is a JSON array of arrays. In the inner arrays, the first entry is a value in the input, and the second entry corresponds to its estimated frequency in the input. The outer array contains k items, sorted by descending frequency.
expr: The expression (e.g. column name) for which you want to find the most common values.
k: The number of values whose counts you want approximated. For example, if you want to see the top 10 most common values, then set k to 10.
counters: This is the maximum number of distinct values that can be tracked at a time during the estimation process. For example, if counters is set to 100000, then the algorithm tracks 100,000 distinct values, attempting to keep the 100,000 most frequent values.
Returns the Space-Saving summary at the end of aggregation. (For more information about the Space-Saving summary, see Estimating Frequent Values.) The function APPROX_TOP_K discards its internal, intermediate state when the final cardinality estimate is returned. However, in certain advanced use cases, such as estimating incremental frequent values during bulk loading, you might want to keep the intermediate state, in which case you would use APPROX_TOP_K_ACCUMULATE instead of APPROX_TOP_K..
expr: The expression (e.g. column name) for which you want to find the most common values.
counters: This is the maximum number of distinct values that can be tracked at a time during the estimation process. For example, if counters is set to 100000, then the algorithm tracks 100,000 distinct values, attempting to keep the 100,000 most frequent values
Combines (merges) input states into a single output state. This allows scenarios where APPROX_TOP_K_ACCUMULATE is run over horizontal partitions of the same table, producing an algorithm state for each table partition. These states can later be combined using APPROX_TOP_K_COMBINE, producing the same output state as a single run of APPROX_TOP_K_ACCUMULATE over the entire table.
state: An expression that contains state information generated by a call to APPROX_TOP_K_ACCUMULATE.
counters: This is the maximum number of distinct values that can be tracked at a time during the estimation process. For example, if counters is set to 100000, then the algorithm tracks 100,000 distinct values, attempting to keep the 100,000 most frequent values.
Returns the approximate most frequent values and their estimated frequency for the given Space-Saving state. (For more information about the Space-Saving summary, see Estimating Frequent Values.)
state: An expression that contains state information generated by a call to APPROX_TOP_K_ACCUMULATE or APPROX_TOP_K_COMBINE.
k: The number of values whose counts you want approximated. For example, if you want to see the top 10 most common values, then set k to 10.
Returns an approximated value for the desired percentile (i.e. if column c has n numbers, then APPROX_PERCENTILE(c, p) returns a number such that approximately n * p of the numbers in c are smaller than the returned number).
expr: A valid expression, such as a column name, that evaluates to a numeric value.
percentile: A constant real value greater than or equal to 0.0 and less than 1.0. This indicates the percentile (from 0 to 99.999...). E.g. The value 0.65 indicates the 65th percentile.
Returns the internal representation of the t-Digest state (as a JSON object) at the end of aggregation. (For more information about t-Digest, see: Estimating Percentile Values.)
expr: A valid expression, such as a column name, that evaluates to a numeric value.
Combines (merges) percentile input states into a single output state.
state: An expression that contains state information generated by a call to APPROX_PERCENTILE_ACCUMULATE.
Returns the desired approximated percentile value for the specified t-Digest state.
state: An expression that contains state information generated by a call to APPROX_PERCENTILE_ACCUMULATE or APPROX_PERCENTILE_COMBINE.
percentile: A constant real value greater than or equal to 0.0 and less than 1.0. This indicates the percentile (from 0 to 99.999...). E.g. The value 0.65 indicates the 65th percentile.
Describes which of a list of expressions are grouped in a row produced by a GROUP BY query.
expr1:
Describes which of a list of expressions are grouped in a row produced by a GROUP BY query.
expr1:
Aborts the specified session.
session_id: Identifier for the session to abort.
Aborts the specified transaction, if it is running. If the transaction has already been committed or rolled back, then the state of the transaction is not altered.
transaction_id: Identifier for the transaction to abort. To obtain transaction IDs, you can use the SHOW TRANSACTIONS or SHOW LOCKS commands.
Cancels all active/running queries in the specified session.
session_id: Identifier for the session for which to cancel all queries.
Cancels the specified query (or statement) if it is currently active/running.
query_id: Identifier for the query to cancel.
Forces a pipe paused using ALTER PIPE to resume. This is necessary if the pipe owner transfers ownership of the pipe to another role while the pipe is paused.
pipe_name: Pipe to resume running.
Waits for a specified amount of time before proceeding.
amount: Number specifying the amount of time to wait as determined by time_unit.
time_unit: Time unit for amount. Accepted values are DAYS, HOURS, MINUTES, SECONDS, MILLISECONDS, MICROSECONDS, NANOSECONDS. The unit should be in single quotes
Returns the commit time of the last DML change performed on a table or a view. In case of a view, the function returns the latest commit time of all the objects referenced in the view.
object_name: ecifies the table or view for which the commit time for the last DML is returned
Retrieves a JSON representation of the current status of a pipe.
pipe_name: Pipe for which you want to retrieve the current status.
Returns a string representing the SQL data type associated with an expression.
expr:
Bitwise AND of two numeric expressions (a and b).
expr1: This expression must evaluate to a data type that can be cast to INTEGER.
expr2: This expression must evaluate to a data type that can be cast to INTEGER.
Bitwise negation of a numeric expression.
expr: This expression must evaluate to a data type that can be cast to INTEGER.
Bitwise OR of two numeric expressions (a and b).
expr1: This expression must evaluate to a data type that can be cast to INTEGER.
expr2: This expression must evaluate to a data type that can be cast to INTEGER.
Shift the bits for a numeric expression n positions to the left.
expr1:
n:
Shift the bits for a numeric expression n positions to the right, with sign extension.
expr1: This expression must evaluate to a data type that can be cast to INTEGER.
n: The number of bits to shift by.
Bitwise XOR of two numeric expressions (a and b).
expr1:
expr2:
Computes the Boolean AND of two numeric expressions. In accordance with Boolean semantics. Non-zero values (including negative numbers) are regarded as True. Zero values are regarded as False.
expr1:
expr2:
Computes the Boolean NOT of a single numeric expression. In accordance with Boolean semantics: Non-zero values (including negative numbers) are regarded as True. Zero values are regarded as False.
expr1:
Computes the Boolean OR of two numeric expressions. In accordance with Boolean semantics: Non-zero values (including negative numbers) are regarded as True. Zero values are regarded as False.
expr1:
expr2:
Computes the Boolean XOR of two numeric expressions (i.e. one of the expressions, but not both expressions, is TRUE). In accordance with Boolean semantics: Non-zero values (including negative numbers) are regarded as True. Zero values are regarded as False.
expr1:
expr2:
Returns the first non-NULL expression among its arguments, or NULL if all its arguments are NULL.
expr1: Returns the first non-NULL expression among its arguments, or NULL if all its arguments are NULL.
Compares the select expression to each search expression in order. As soon as a search expression matches the selection expression, the corresponding result expression is returned.
expr: This is the "select expression". The "search expressions" are compared to this select expression, and if there is a match then DECODE returns the result that corresponds to that search expression. The select expression is typically a column, but can be a subquery, literal, or other expression.
search1: The search expressions indicate the values to compare to the select expression. If one of these search expressions matches, the function returns the corresponding result. If more than one search expression would match, only the first match's result is returned.
result1: The results are the values that will be returned if one of the search expressions matches the select expression.
search2: The search expressions indicate the values to compare to the select expression. If one of these search expressions matches, the function returns the corresponding result. If more than one search expression would match, only the first match's result is returned.
result2: The results are the values that will be returned if one of the search expressions matches the select expression.
default: If an optional default is specified, and if none of the search expressions match the select expression, then DECODE returns this default value.
Compares whether two expressions are equal. The function is NULL-safe, meaning it treats NULLs as known values for comparing equality. Note that this is different from the EQUAL comparison operator (=), which treats NULLs as unknown values.
expr1:
expr2:
Returns the largest value from a list of expressions. GREATEST supports all types, including VARIANT. The first argument determines the return type. If the first type is numeric, then the return type will be 'widened' according to the numeric types in the list of all arguments. If the first type is not numeric, then all other arguments must be convertible to the first type. If any of the argument values is NULL, the result will be NULL.
expr:
Single-level if-then-else expression. Similar to CASE, but only allows a single condition. If condition evaluates to TRUE, returns expr1, otherwise returns expr2.
condition: The condition is an expression that should evaluate to a BOOLEAN value (True, False, or NULL).
expr1: A general expression. This value is returned if the condition is true.
expr2: A general expression. This value is returned if the condition is false.
If expr1 is NULL, returns expr2, otherwise returns expr1.
expr1: A general expression.
expr2: A general expression.
Returns the smallest value from a list of expressions. LEAST supports all data types, including VARIANT.
expr: The arguments must include at least one expression. All the expressions should be of the same type or compatible types.
Returns NULL if expr1 is equal to expr2, otherwise returns expr1.
expr1: any expression
expr2: any expression
If expr1 is NULL, returns expr2, otherwise returns expr1.
expr1: The expression to be checked to see whether it's NULL.
expr2: If expr1 is NULL, this expression will be evaluated and its value will be returned.
Returns values depending on the nullness of the first argument: If expr1 is not null, then NVL2 returns expr2. If expr1 is null, then NVL2 returns expr3.
expr1: The expression to be checked to see whether it's NULL.
expr2: If expr1 is not NULL, this expression will be evaluated and its value will be returned.
expr3: If expr1 is NULL, this expression will be evaluated and its value will be returned.
If the first argument is NULL, returns NULL. Otherwise, returns the second argument. Contrast REGR_VALX and REGR_VALY with NVL: NVL is a NULL-replacing function. The less commonly used REGR_VALX and REGR_VALY are NULL-preserving functions.
expr1:
expr2:
If the second argument is NULL, returns NULL; otherwise, returns the first argument. Contrast REGR_VALX and REGR_VALY with NVL: NVL is a NULL-replacing function. The less commonly used REGR_VALX and REGR_VALY are NULL-preserving functions.
expr1:
expr2:
Returns 0 if its argument is null; otherwise, returns its argument.
expr:
Returns the version of the client from which the function was called. If called from an application using the JDBC or ODBC driver to connect to Snowflake, returns the version of the driver.
Returns the current date of the system.
Returns the current time for the system.
fract_sec_precision>: This optional argument indicates the precision with which to report the time. For example, a value of 3 says to use 3 digits after the decimal point - i.e. to specify the time with a precision of milliseconds.
Returns the current timestamp for the system.
fract_sec_precision: This optional argument indicates the precision with which to report the time. For example, a value of 3 says to use 3 digits after the decimal point - i.e. to specify the time with a precision of milliseconds.
Returns the current Snowflake version.
Returns the current time for the system. ANSI-compliant alias for CURRENT_TIME.
Returns the current timestamp for the system. ANSI-compliant alias for CURRENT_TIMESTAMP.
Returns the name of the role in use for the current session. To specify a different role for the session, execute the USE ROLE command.
Returns a unique system identifier for the Snowflake session corresponding to the present connection. This will generally be a system-generated alphanumeric string. It is NOT derived from the user name or user account.
Returns the SQL text of the statement that is currently executing.
Returns the transaction id of an open transaction in the current session.
Returns the name of the user currently logged into the system.
Returns the ID of a specified query in the current session. If no query is specified, the most recently-executed query is returned.
num: Specifies the query to return, based on the position of the query (within the session).
Returns the transaction ID of the last transaction that was either committed or rolled back in the current session.
Returns the name of the database in use for the current session. To specify a different database for the session, execute the USE DATABASE command.
Returns the name of the schema in use by the current session. To specify a different schema for the session, execute the USE SCHEMA command.
Returns active search path schemas. For more information about search path, see Object Name Resolution.
Returns the name of the warehouse in use for the current session. To specify a different warehouse for the session, execute the USE WAREHOUSE command.
Converts a value of one data type into another data type. The semantics of CAST are the same as the semantics of the corresponding TO_ datatype conversion functions. If the cast is not possible, an error is raised. For more details, see the individual TO_ datatype conversion functions.
source_expr: Expression of any supported data type to be converted into a different data type.
target_data_type: The data type to which to convert the expression. If the data type supports additional properties, such as scale and precision (for numbers/decimals), the properties can be included.
A special version of CAST , :: that is available for a subset of data type conversions. It performs the same operation (i.e converts a value of one data type into another data type), but returns a NULL value instead of raising an error when the conversion can not be performed.
source_expr: Expression of any supported data type to be converted into a different data type.
target_data_type: The data type to which to convert the expression. If the data type supports additional properties, such as scale and precision (for numbers/decimals), the properties can be included.
Converts the input expression to a string. For NULL input, the output is NULL.
expr: An expression of any data type.
format: The format of the output string
Converts the input expression to a string. For NULL input, the output is NULL.
expr: An expression of any data type.
format: The format of the output string
Converts the input expression to a binary value. For NULL input, the output is NULL.
string_expr: A string expression.
format: The binary format for conversion: HEX, BASE64, or UTF-8 (see Binary Input and Output). The default is the value of the BINARY_INPUT_FORMAT session parameter. If this parameter is not set, the default is HEX
A special version of TO_BINARY that performs the same operation (i.e. converts an input expression to a binary value), but with error handling support (i.e. if the conversion cannot be performed, it returns a NULL value instead of raising an error).
string_expr: A string expression.
format: The binary format for conversion: HEX, BASE64, or UTF-8 (see Binary Input and Output). The default is the value of the BINARY_INPUT_FORMAT session parameter. If this parameter is not set, the default is HEX
Converts an input expression to a fixed-point number. For NULL input, the output is NULL.
expr: An expression of a numeric, character, or variant type.
format: The SQL format model used to parse the input expr and return. For more information, see SQL Format Models.
precision: The maximal number of decimal digits in the resulting number; from 1 to 38. In Snowflake, precision is not used for determination of the number of bytes needed to store the number and does not have any effect on efficiency, so the default is the maximum (38).
scale: The number of fractional decimal digits (from 0 to precision - 1). 0 indicates no fractional digits (i.e. an integer number).
A special version of TO_DECIMAL, TO_NUMBER, TO_NUMERIC that performs the same operation (i.e. converts an input expression to a fixed-point number), but with error-handling support (i.e if the conversion cannot be performed, it returns a NULL value instead of raising an error).
expr: An expression of a numeric, character, or variant type.
format: The SQL format model used to parse the input expr and return. For more information, see SQL Format Models.
precision: The maximal number of decimal digits in the resulting number; from 1 to 38. In Snowflake, precision is not used for determination of the number of bytes needed to store the number and does not have any effect on efficiency, so the default is the maximum (38).
scale: The number of fractional decimal digits (from 0 to precision - 1). 0 indicates no fractional digits (i.e. an integer number).
Converts an expression to a double-precision floating-point number.
expr: An expression of a numeric, character, or variant type.
format: If the expression evaluates to a string, then the function accepts an optional format model. Format models are described at SQL Format Models. The format model specifies the format of the input string, not the format of the output value.
A special version of TO_DOUBLE that performs the same operation (i.e. converts an input expression to a double-precision floating-point number), but with error-handling support (i.e. if the conversion cannot be performed, it returns a NULL value instead of raising an error).
expr: An expression of a numeric, character, or variant type.
format: If the expression evaluates to a string, then the function accepts an optional format model. Format models are described at SQL Format Models. The format model specifies the format of the input string, not the format of the output value.
Coverts the input text or numeric expression to a Boolean value. For NULL input, the output is NULL.
text_or_numeric_expr: A text or numeric expression
A special version of TO_BOOLEAN that performs the same operation (i.e. converts an input expression to a Boolean value), but with error-handling support (i.e. if the conversion cannot be performed, it returns a NULL value instead of raising an error).
text_or_numeric_expr: A text or numeric expression
Converts an input expression to a date
expr: Expression to be converted into a date.
format: Date format specifier for string_expr or AUTO, which specifies for Snowflake to interpret the format.
A special version of TO_DATE that performs the same operation (i.e. converts an input expression to a Boolean value), but with error-handling support (i.e. if the conversion cannot be performed, it returns a NULL value instead of raising an error).
expr: Expression to be converted into a date.
format: Date format specifier for string_expr or AUTO, which specifies for Snowflake to interpret the format.
Converts an input expression into a time. If input is NULL, returns NULL.
expr: Expression to be converted into a time
format: Time format specifier for string_expr or AUTO, which specifies for Snowflake to interpret the format.
A special version of TO_TIME that performs the same operation (i.e. converts an input expression to a Boolean value), but with error-handling support (i.e. if the conversion cannot be performed, it returns a NULL value instead of raising an error).
expr: Expression to be converted into a time
format: Time format specifier for string_expr or AUTO, which specifies for Snowflake to interpret the format.
Converts an input expression into the corresponding timestamp
expr: Expression to be converted into a timestamp
format: Time format specifier for string_expr or AUTO, which specifies for Snowflake to interpret the format.
A special version of TO_TIMESTAMP that performs the same operation (i.e. converts an input expression to a Boolean value), but with error-handling support (i.e. if the conversion cannot be performed, it returns a NULL value instead of raising an error).
expr: Expression to be converted into a timestamp
format: Time format specifier for string_expr or AUTO, which specifies for Snowflake to interpret the format.
Converts an input expression into the corresponding timestamp
expr: Expression to be converted into a timestamp
format: Time format specifier for string_expr or AUTO, which specifies for Snowflake to interpret the format.
A special version of TO_TIMESTAMP_NTZ that performs the same operation (i.e. converts an input expression to a Boolean value), but with error-handling support (i.e. if the conversion cannot be performed, it returns a NULL value instead of raising an error).
expr: Expression to be converted into a timestamp
format: Time format specifier for string_expr or AUTO, which specifies for Snowflake to interpret the format.
Converts an input expression into the corresponding timestamp
expr: Expression to be converted into a timestamp
format: Time format specifier for string_expr or AUTO, which specifies for Snowflake to interpret the format.
A special version of TO_TIMESTAMP_TZ that performs the same operation (i.e. converts an input expression to a Boolean value), but with error-handling support (i.e. if the conversion cannot be performed, it returns a NULL value instead of raising an error).
expr: Expression to be converted into a timestamp
format: Time format specifier for string_expr or AUTO, which specifies for Snowflake to interpret the format.
Converts an input expression into the corresponding timestamp
expr: Expression to be converted into a timestamp
format: Time format specifier for string_expr or AUTO, which specifies for Snowflake to interpret the format.
A special version of TO_TIMESTAMP_LTZ that performs the same operation (i.e. converts an input expression to a Boolean value), but with error-handling support (i.e. if the conversion cannot be performed, it returns a NULL value instead of raising an error).
expr: Expression to be converted into a timestamp
format: Time format specifier for string_expr or AUTO, which specifies for Snowflake to interpret the format.
Each call returns a pseudo-random 64-bit integer.
seed: Seed is an integer. Different seeds will cause RANDOM to produce different output values.
Returns a random string of specified length. Individual characters are chosen uniformly at random from the following pool of characters: 0-9, a-z, A-Z.
length: Length of the string to generate
gen: The value for the generator expression, gen, is used as the seed for this uniform random distribution
Generates either a version 4 (random) or version 5 (named) RFC 4122-compliant UUID as a formatted string.
uuid: The string (known as the namespace)
name: The name of the UUID
Returns a normal-distributed floating point number, with specified mean and stddev (standard deviation).
mean: This is the value that you would like the output values centered around
stddev: This specifies the width of one standard deviation.
gen: This specifies the generator expression for the function.
Returns a uniformly random number, in the inclusive range [min, max].
min: The Minimum Number
max: The Maximum Number
gen: The generator expression for the function
Returns a Zipf-distributed integer, for N elements and characteristic exponent s
s: the characteristic exponent
N: the number of elements
gen: The generator expression for the function
Returns a sequence of monotonically increasing integers, with wrap-around. Wrap-around occurs after the largest representable integer of the integer width (1 byte).
sign: The optional sign argument. If the optional sign argument is 1, the sequence continues at the smallest representable number based on the given integer width. The default sign argument is 0.
Returns a sequence of monotonically increasing integers, with wrap-around. Wrap-around occurs after the largest representable integer of the integer width (2 byte).
sign: The optional sign argument. If the optional sign argument is 1, the sequence continues at the smallest representable number based on the given integer width. The default sign argument is 0.
Returns a sequence of monotonically increasing integers, with wrap-around. Wrap-around occurs after the largest representable integer of the integer width (4 byte).
sign: The optional sign argument. If the optional sign argument is 1, the sequence continues at the smallest representable number based on the given integer width. The default sign argument is 0.
Returns a sequence of monotonically increasing integers, with wrap-around. Wrap-around occurs after the largest representable integer of the integer width (8 byte).
sign: The optional sign argument. If the optional sign argument is 1, the sequence continues at the smallest representable number based on the given integer width. The default sign argument is 0.
Creates a date from individual numeric components that represent the year, month, and day of the month
year: The integer expression to use as a year for building a date.
month: The integer expression to use as a month for building a date, with January represented as 1, and December as 12.
day: The integer expression to use as a day for building a date, usually in the 1-31 range.
Creates a time from individual numeric components.
hour: An integer expression to use as an hour for building a time, usually in the 0-23 range.
minute: An integer expression to use as a minute for building a time, usually in the 0-59 range.
second: An integer expression to use as a second for building a time, usually in the 0-59 range.
nanoseconds: A 9-digit integer expression to use as a nanosecond for building a time
Creates a timestamp from individual numeric components. If no time zone is in effect, the function can be used to create a timestamp from a date expression and a time expression.
date_expr: provides the year, month, and day for the timestamp
time_expr: provides the hour, minute, second, and nanoseconds within the day
Creates a timestamp from individual numeric components. If no time zone is in effect, the function can be used to create a timestamp from a date expression and a time expression.
year: An integer expression to use as a year for building a timestamp.
month: An integer expression to use as a month for building a timestamp, with January represented as 1, and December as 12.
day: An integer expression to use as a day for building a timestamp, usually in the 1-31 range.
hour: An integer expression to use as an hour for building a timestamp, usually in the 0-23 range.
minute: An integer expression to use as a minute for building a timestamp, usually in the 0-59 range.
second: An integer expression to use as a second for building a timestamp, usually in the 0-59 range.
nanoseconds: An integer expression to use as a nanosecond for building a timestamp, usually in the 0-999999999 range.
time_zone: A string expression to use as a time zone for building a timestamp (e.g. America/Los_Angeles)
Creates a timestamp from individual numeric components. If no time zone is in effect, the function can be used to create a timestamp from a date expression and a time expression.
date_expr: provides the year, month, and day for the timestamp
time_expr: provides the hour, minute, second, and nanoseconds within the day
Creates a timestamp from individual numeric components. If no time zone is in effect, the function can be used to create a timestamp from a date expression and a time expression.
year: An integer expression to use as a year for building a timestamp.
month: An integer expression to use as a month for building a timestamp, with January represented as 1, and December as 12.
day: An integer expression to use as a day for building a timestamp, usually in the 1-31 range.
hour: An integer expression to use as an hour for building a timestamp, usually in the 0-23 range.
minute: An integer expression to use as a minute for building a timestamp, usually in the 0-59 range.
second: An integer expression to use as a second for building a timestamp, usually in the 0-59 range.
nanoseconds: An integer expression to use as a nanosecond for building a timestamp, usually in the 0-999999999 range.
Creates a timestamp from individual numeric components. If no time zone is in effect, the function can be used to create a timestamp from a date expression and a time expression.
year: An integer expression to use as a year for building a timestamp.
month: An integer expression to use as a month for building a timestamp, with January represented as 1, and December as 12.
day: An integer expression to use as a day for building a timestamp, usually in the 1-31 range.
hour: An integer expression to use as an hour for building a timestamp, usually in the 0-23 range.
minute: An integer expression to use as a minute for building a timestamp, usually in the 0-59 range.
second: An integer expression to use as a second for building a timestamp, usually in the 0-59 range.
nanoseconds: An integer expression to use as a nanosecond for building a timestamp, usually in the 0-999999999 range.
Creates a timestamp from individual numeric components. If no time zone is in effect, the function can be used to create a timestamp from a date expression and a time expression.
year: An integer expression to use as a year for building a timestamp.
month: An integer expression to use as a month for building a timestamp, with January represented as 1, and December as 12.
day: An integer expression to use as a day for building a timestamp, usually in the 1-31 range.
hour: An integer expression to use as an hour for building a timestamp, usually in the 0-23 range.
minute: An integer expression to use as a minute for building a timestamp, usually in the 0-59 range.
second: An integer expression to use as a second for building a timestamp, usually in the 0-59 range.
nanoseconds: An integer expression to use as a nanosecond for building a timestamp, usually in the 0-999999999 range.
time_zone: A string expression to use as a time zone for building a timestamp (e.g. America/Los_Angeles)
Extracts the specified date or time part from a date, time, or timestamp.
date_or_time_part: The date or time part to extract
date_or_time_expr: The date or time expression to extract from
Extracts the three-letter day-of-week name from the specified date or timestamp.
date_or_timestamp_expr: The date or time expression to extract from
Extracts the corresponding time part from a time or timestamp value.
time_or_timestamp_expr:
Extracts the corresponding time part from a time or timestamp value.
time_or_timestamp_expr:
Extracts the corresponding time part from a time or timestamp value.
time_or_timestamp_expr:
Extracts the corresponding date part from a date or timestamp. These functions are alternatives to using the DATE_PART (or EXTRACT) function with the equivalent date part
date_or_timestamp_expr:
Extracts the corresponding date part from a date or timestamp. These functions are alternatives to using the DATE_PART (or EXTRACT) function with the equivalent date part
date_or_timestamp_expr:
Extracts the corresponding date part from a date or timestamp. These functions are alternatives to using the DATE_PART (or EXTRACT) function with the equivalent date part
date_or_timestamp_expr:
Extracts the corresponding date part from a date or timestamp. These functions are alternatives to using the DATE_PART (or EXTRACT) function with the equivalent date part
date_or_timestamp_expr:
Extracts the corresponding date part from a date or timestamp. These functions are alternatives to using the DATE_PART (or EXTRACT) function with the equivalent date part
date_or_timestamp_expr:
Extracts the corresponding date part from a date or timestamp. These functions are alternatives to using the DATE_PART (or EXTRACT) function with the equivalent date part
date_or_timestamp_expr:
Extracts the corresponding date part from a date or timestamp. These functions are alternatives to using the DATE_PART (or EXTRACT) function with the equivalent date part
date_or_timestamp_expr:
Extracts the corresponding date part from a date or timestamp. These functions are alternatives to using the DATE_PART (or EXTRACT) function with the equivalent date part
date_or_timestamp_expr:
Extracts the corresponding date part from a date or timestamp. These functions are alternatives to using the DATE_PART (or EXTRACT) function with the equivalent date part
date_or_timestamp_expr:
Extracts the corresponding date part from a date or timestamp. These functions are alternatives to using the DATE_PART (or EXTRACT) function with the equivalent date part
date_or_timestamp_expr:
Extracts the corresponding date part from a date or timestamp. These functions are alternatives to using the DATE_PART (or EXTRACT) function with the equivalent date part
date_or_timestamp_expr:
Extracts the corresponding date part from a date or timestamp. These functions are alternatives to using the DATE_PART (or EXTRACT) function with the equivalent date part
date_or_timestamp_expr:
Extracts the corresponding date part from a date or timestamp. These functions are alternatives to using the DATE_PART (or EXTRACT) function with the equivalent date part
date_or_timestamp_expr:
Returns the last day of the specified date part for a date or timestamp. Commonly used to return the last day of the month for a date or timestamp.
date_or_time_expr:
date_part:
Extracts the three-letter month name from the specified date or timestamp.
date_or_timestamp_expr:
Returns the date of the first specified DOW (day of week) that occurs after the input date.
date_or_time_expr: Specifies the input date; can be a date or timestamp.
dow_string: Specifies the day of week used to calculate the date for the previous day. The value can be a string literal or an expression that returns a string. The string must start with the first two characters (case-insensitive) of the day name
Returns the date of the first specified DOW (day of week) that occurs before the input date.
date_or_time_expr: Specifies the input date; can be a date or timestam
dow: Specifies the day of week used to calculate the date for the previous day. The value can be a string literal or an expression that returns a string. The string must start with the first two characters (case-insensitive) of the day name
Adds or subtracts a specified number of months to a date or timestamp, preserving the end-of-month information.
date_or_timestamp_expr: This is the date or timestamp expression to which you want to add a specified number of months.
num_months_expr: This is the number of months you want to add. This should be an integer. It may be positive or negative. If the value is a non-integer numeric value (for example, FLOAT) the value will be rounded to the nearest integer.
Extracts the specified date or time part from a date, time, or timestamp.
date_or_time_part:
date_or_time_expr:
Adds the specified value for the specified date or time part to a date, time, or timestamp.
date_or_time_part: This indicates the units of time that you want to add. For example if you want to add 2 days, then this will be DAY.
value: This is the number of units of time that you want to add. For example, if you want to add 2 days, this will be 2.
date_or_time_expr: must evaluate to a date, time, or timestamp. This is the date, time, or timestamp to which you want to add. For example, if you want to add 2 days to August 1, 2018, then this will be '2018-08-01'::DATE
Adds the specified value for the specified date or time part to a date, time, or timestamp.
date_or_time_part: This indicates the units of time that you want to add. For example if you want to add 2 days, then this will be DAY
value: This is the number of units of time that you want to add. For example, if you want to add 2 days, this will be 2.
date_or_time_expr: must evaluate to a date, time, or timestamp. This is the date, time, or timestamp to which you want to add. For example, if you want to add 2 days to August 1, 2018, then this will be '2018-08-01'::DATE.
Adds the specified value for the specified date or time part to a date, time, or timestamp.
date_or_time_part:
time_value:
date_or_time_expr:
Calculates the difference between two date, time, or timestamp expressions based on the date or time part requested. The function returns the result of subtracting the second argument from the third argument.
date_or_time_part:
date_or_time_expr1: must be a date, a time, a timestamp, or an expression that can be evaluated to a date, a time, or a timestamp. The first value is subtracted from the second value.
date_or_time_expr2: must be a date, a time, a timestamp, or an expression that can be evaluated to a date, a time, or a timestamp. The first value is subtracted from the second value.
Calculates the difference between two date, time, or timestamp expressions based on the specified date or time part.
date_or_time_part:
date_or_time_expr1: must be a date, a time, a timestamp, or an expression that can be evaluated to one of those. The first value is subtracted from the second value
time_expr2: must be a date, a time, a timestamp, or an expression that can be evaluated to one of those.
Calculates the difference between two date, time, or timestamp expressions based on the specified date or time part.
date_or_time_part:
date_or_time_expr1:
date_or_time_expr2:
Truncates a date, time, or timestamp to the specified part. Note that truncation is not the same as extraction. For example: Truncating a timestamp down to the quarter returns the timestamp corresponding to midnight of the first day of the quarter for the input timestamp. Extracting the quarter date part from a timestamp returns the quarter number of the year in the timestamp.
date_or_time_part:
date_or_time_expr:
Converts a timestamp to another time zone
source_tz: String specifying the time zone for the input timestamp. Required for timestamps with no time zone (i.e. TIMEZONE_NTZ)
target_tz: String specifying the time zone to which the input timestamp should be converted.
source_timestamp_ntz: For the 3-argument version, string specifying the timestamp to convert (must be NTZ).
Converts a timestamp to another time zone
target_tz: String specifying the time zone to which the input timestamp should be converted.
source_timestamp: For the 2-argument version, string specifying the timestamp to convert (can be any timestamp variant, including NTZ).
Returns a signed 64-bit hash value. Note that HASH never returns NULL, even for NULL inputs.
expr:
Returns an aggregate signed 64-bit hash value over the (unordered) set of input rows. HASH_AGG never returns NULL, even if no input is provided. Empty input "hashes" to 0.
expr:
Returns a DDL statement that can be used to recreate the specified object. For databases and schemas, GET_DDL is recursive, i.e. it returns the DDL statements for recreating all supported objects within the specified database/schema.
object_type: Specifies the type of object for which the DDL is returned.
namespace_object_name: Specifies the fully-qualified name of the object for which the DDL is returned.
Returns the absolute value of a numeric expression.
num_expr:
Returns values from input_expr rounded to the nearest equal or larger integer, or to the nearest equal or larger value with the specified number of places after the decimal point
input_expr: The input_expr is the value to be rounded up, and should evaluate to a numeric data type, such as FLOAT or NUMBER.
scale_expr: The scale_expr indicates the number of places after the decimal to which to round upward. This should evaluate to an integer.
Returns values from input_expr rounded to the nearest equal or smaller integer, or to the nearest equal or smaller value with the specified number of places after the decimal point
input_expr: The input_expr is the value to be rounded up, and should evaluate to a numeric data type, such as FLOAT or NUMBER.
scale_expr: The scale_expr indicates the number of places after the decimal to which to round upward. This should evaluate to an integer.
Returns the remainder of input expr1 divided by input expr2.
expr1: A numeric expression.
expr2: A numeric expression.
Returns rounded values for input_expr
input_expr: The expression to round
scale_expr: If the optional scale_expr argument is specified, rounding is performed to the specified number of digits (negative digits round to factors-of-10)
Returns the sign of its argument
expr: an expression
Rounds the input expression down to the nearest (or equal) integer towards zero.
input_expr: the expression to truncate
scale_expr: If the optional scale_expr argument is provided, rounding is performed to the specified number of digits (negative digits round to factors-of-10)
Returns the cubic root of a numeric expression.
expr: a numeric expression
Computes Euler's number e raised to a floating-point value.
real_expr: a real expression
Computes the factorial of its input. The input argument must be an integer expression in the range of 0 to 33.
integer_expr: an integer expression
Returns a number (x) raised to the specified power (y).
x:
y:
Returns the square-root of a non-negative numeric expression.
expr: a non-negative numeric expression.
Returns the square of a numeric expression, i.e. a numeric expression multiplied by itself.
expr: a numeric expression
Returns the natural logarithm of a numeric expression.
expr: a numeric expression
Returns the logarithm of a numeric expression.
base: The "base" to use (e.g. 10 for base 10 arithmetic). This can be of any numeric data type (INTEGER, fixed-point, or floating point).
expr: The value for which you want to know the log.
Computes the inverse cosine (arc cosine) of its input; the result is a number in the interval [-pi, pi].
real_expr: This expression should evaluate to a real number greater than or equal to -1.0 and less than or equal to +1.0.
Computes the inverse (arc) hyperbolic cosine of its input.
real_expr: This expression should evaluate to a FLOAT number greater than or equal to 1.0.
Computes the inverse sine (arc sine) of its argument; the result is a number in the interval [-pi, pi].
real_expr: This expression should evaluate to a real number greater than or equal to -1.0 and less than or equal to +1.0.
Computes the inverse (arc) hyperbolic sine of its argument.
real_expr: This expression should evaluate to a real number.
Computes the inverse tangent (arc tangent) of its argument; the result is a number in the interval [-pi, pi].
real_expr: This expression should evaluate to a real number.
Computes the inverse (arc) hyperbolic tangent of its argument.
real_expr:
Computes the cosine of its argument; the argument should be expressed in radians.
real_expr: This expression should evaluate to a real number. The value should be in radians, not degrees.
Computes the hyperbolic cosine of its argument.
real_expr: This expression should evaluate to a real number.
Computes the cotangent of its argument; the argument should be expressed in radians.
real_expr: This expression should evaluate to a real number.
Converts radians to degrees.
real_expr: An expression representing the number of radians.
Converts degrees to radians.
real_expr:
Computes the sine of its argument; the argument should be expressed in radians.
real_expr: This expression should evaluate to a real number. The value should be in radians, not degrees.
Computes the hyperbolic sine of its argument.
real_expr: This expression should evaluate to a real number.
Computes the tangent of its argument; the argument should be expressed in radians.
real_expr: This expression should evaluate to a real number. The value should be in radians, not degrees.
Computes the hyperbolic tangent of its argument.
real_expr: This expression should evaluate to a real number.
Computes the inverse tangent (arc tangent) of the ratio of its two arguments (i.e. ATAN2(x,y) = ATAN(x/y)). The result is a number in the interval [-pi, pi].
real_expr:
real_expr:
Returns the value of pi as a floating-point value.
Calculates the great circle distance in kilometers between two points on the Earth's surface, using the Haversine formula. The two points are specified by their latitude and longitude in degrees.
lat1: Latitude of Point 1
lon1: Longitude of Point 1
lat2: Latitude of Point 2
lon2: Longitude of Point 2
Checks the validity of a JSON document. If the input string is a valid JSON document or a NULL, the output is NULL (i.e. no error). If the input cannot be translated to a valid JSON value, the output string contains the error message.
string_or_variant_expr: A VARIANT or string value (or expression) to check. If the expression is of type VARIANT, it should contain a string.
Checks the validity of an XML document. If the input string is NULL or a valid XML document, the output is NULL. In case of an XML parsing error, the output string contains the error message.
string_or_variant_expr: A VARIANT or string value (or expression) to check. If the expression is of type VARIANT, it should contain a string.
Interprets an input string as a JSON document, producing a VARIANT value.
expr: An expression of string type (e.g. VARCHAR) that holds valid JSON information.
Interprets an input string as an XML document, producing an OBJECT value. If the input is NULL, the output is NULL.
expr: An expression of string type (e.g. VARCHAR) that holds valid JSON information.
Converts a JSON "null" value to a SQL NULL value. All other variant values are passed unchanged.
expr: an expression to strip data from
Returns an array containing all elements from the source array as well as the new element. The new element is located at end of the array.
array: The source array.
new_element: The element to be appended. The element may be of almost any data type. The data type does not need to match the data type(s) of the existing elements in the array.
Returns an array containing the new element as well as all elements from the source array. The new element is positioned at the beginning of the array.
array: The source array.
new_element: The element to be prepended.
Returns a concatenation of two arrays.
array1: The source array.
array2: The array to be appended to array1.
Returns an array containing all elements from the source array as well as the new element.
array: The source array.
pos: A (zero-based) position in the source array. The new element is inserted at this position. The original element from this position (if any) and all subsequent elements (if any) are shifted by one position to the right in the resulting array (i.e. inserting at position 0 has the same effect as using ARRAY_PREPEND). A negative position is interpreted as an index from the back of the array (e.g. -1 results in insertion before the last element in the array).]
new_element: The element to be inserted. The new element is located at position pos. The relative order of the other elements from the source array is preserved.
Returns a compacted array with missing and null values removed, effectively converting sparse arrays into dense arrays.
array1: The source array.
Returns an array constructed from zero, one, or more inputs.
expr1:
expr2:
Returns an array constructed from zero, one, or more inputs.
expr1:
expr2:
Takes a VARIANT and an ARRAY value as inputs and returns True if the VARIANT is contained in the ARRAY.
variant:
array:
Returns the index of the first occurrence of an element in an array.
variant_expr: This expression should evaluate to a VARIANT value. The function searches for the first occurrence of this value in the array.
array: The array to be searched.
Returns the size of the input array. A variation of ARRAY_SIZE takes a VARIANT value as input. If the VARIANT value contains an array, the size of the array is returned; otherwise, NULL is returned if the value is not an array.
array_or_variant:
Returns an array constructed from a specified subset of elements of the input array.
array: The source array of which a subset of the elements are used to construct the resulting array.
from: A position in the source array. The position of the first element is 0. Elements from positions less than from are not included in the resulting array.
to: A position in the source array. Elements from positions equal to or greater than to are not included in the resulting array.
Returns an input array converted to a string by casting all values to strings (using TO_VARCHAR) and concatenating them (using the string from the second argument to separate the elements).
array: The array of elements to convert to a string.
separator_string: The string to put between each element, typically a space, comma, or other human-readable separator.
Compares whether two arrays have at least one element in common. Returns TRUE if there is at least one element in common; otherwise returns FALSE. The function is NULL-safe, meaning it treats NULLs as known values for comparing equality.
array1: an array
array2: an array
Returns one OBJECT per group. For each (key, value) input pair, where key must be a VARCHAR and value must be a VARIANT, the resulting OBJECT contains a key:value field.
key:
value:
Returns an object constructed from the arguments.
key1:
value1:
keyN:
valueN:
Returns an object containing the contents of the input (i.e.source) object with one or more keys removed.
object: The source object.
key1: Key to be omitted from the returned object.
key2: Key to be omitted from the returned object.
Returns an object consisting of the input object with a new key-value pair inserted (or an existing key updated with a new value).
object: The source object into which the new key-value pair is inserted.
key: The new key to be inserted into the object. Must be different from all existing keys in the object, unless updateFlag is set to TRUE.
value: The value associated with the key.
updateFlag: Boolean flag that, when set to TRUE, specifies the input value is used to update/overwrite an existing key in the object, rather than inserting a new key-value pair.
Extracts an XML element object (often referred to as simply a "tag") from a content of outer XML element object by the name of the tag and its instance number (counting from 0)
type:
tag_name:
instance_num: can be omitted, in which case the default value 0 is used.
Extracts a value from an object or array; returns NULL if either of the arguments is NULL.
expr1: An Object, Variant, or Array
expr2: A string value or an integer, which can be a constant or an expression
Casts a VARIANT value to an array.
variant_expr: An expression that evaluates to a value of type VARIANT.
Casts a VARIANT value to a binary string.
variant_expr: An expression that evaluates to a value of type VARIANT.
Casts a VARIANT value to a string. Does not convert values of other types into string.
variant_expr: An expression that evaluates to a value of type VARIANT.
Casts a VARIANT value to a string. Does not convert values of other types into string.
variant_expr: An expression that evaluates to a value of type VARIANT.
Casts a VARIANT value to a date. Does not convert from timestamps.
variant_expr: An expression that evaluates to a value of type VARIANT.
Casts a VARIANT value to a floating-point value.
variant_expr: An expression that evaluates to a value of type VARIANT.
Casts a VARIANT value to a floating-point value.
variant_expr: An expression that evaluates to a value of type VARIANT.
Casts a VARIANT value to an integer. Does not match non-integer values.
variant_expr: An expression that evaluates to a value of type VARIANT.
Casts a VARIANT value to an object.
variant_expr: An expression that evaluates to a value of type VARIANT.
Casts a VARIANT value to a time value. Does not convert from timestamps.
variant_expr: An expression that evaluates to a value of type VARIANT.
Casts a VARIANT value to the respective TIMESTAMP value with local time zone.
variant_expr: An expression that evaluates to a value of type VARIANT.
Casts a VARIANT value to the respective TIMESTAMP value with no time zone.
variant_expr: An expression that evaluates to a value of type VARIANT.
Casts a VARIANT value to the respective TIMESTAMP value with time zone.
variant_expr: An expression that evaluates to a value of type VARIANT.
Casts a VARIANT value to a fixed-point decimal (does not match floating-point values), with optional precision and scale.
variant_expr: An expression that evaluates to a value of type VARIANT.
precision: The number of significant digits of the decimal number to store.
scale: The number of significant digits after the decimal point.
Casts a VARIANT value to a fixed-point decimal (does not match floating-point values), with optional precision and scale.
variant_expr: An expression that evaluates to a value of type VARIANT.
precision: The number of significant digits of the decimal number to store.
scale: The number of significant digits after the decimal point.
Tokenizes the given string using the given set of delimiters and returns the tokens as an array. If either parameter is a NULL, a NULL is returned. An empty array is returned in case tokenization produces no tokens.
string: Text to be tokenized.
delimiter: Set of delimiters. Optional. Default value is a single space character
Tokenizes a string with the given set of delimiters and flattens the results into rows.
string: Text to be tokenized.
delimiter: Set of delimiters. Optional. Default value is a single space character
Converts the input expression into an array: If the input is an ARRAY, or VARIANT containing an array value, the result is unchanged. For NULL or a JSON null input, returns NULL. For any other value, the result is a single-element array containing this value.
expr: An expression of any data type.
Converts any VARIANT value to a string containing the JSON representation of the value. If the input is NULL, the result is also NULL.
expr: An expression of type VARIANT that holds valid JSON information.
Converts the input value to an object
expr: An expression of any data type.
Converts any value to VARIANT value or NULL (if input is NULL).
expr: An expression of any data type.
Converts any VARIANT value to a string containing the XML representation of the value. If the input is NULL, the result is also NULL.
expr1: The Variant to convert
Returns TRUE if its VARIANT argument contains an ARRAY value.
variant_expr: An expression that evaluates to a value of type VARIANT.
Returns TRUE if its VARIANT argument contains an Boolean value.
variant_expr: An expression that evaluates to a value of type VARIANT.
Returns TRUE if its VARIANT argument contains an binary value.
variant_expr: An expression that evaluates to a value of type VARIANT.
Returns TRUE if its VARIANT argument contains an string value.
variant_expr: An expression that evaluates to a value of type VARIANT.
Returns TRUE if its VARIANT argument contains an string value.
variant_expr: An expression that evaluates to a value of type VARIANT.
Returns TRUE if its VARIANT argument contains an DATE value.
variant_expr: An expression that evaluates to a value of type VARIANT.
Returns TRUE if its VARIANT argument contains an DATE value.
variant_expr: An expression that evaluates to a value of type VARIANT.
Returns TRUE if its VARIANT argument contains an fixed-point decimal value or integer value.
variant_expr: An expression that evaluates to a value of type VARIANT.
Returns TRUE if its VARIANT argument contains an a floating-point value, fixed-point decimal, or integer value.
variant_expr: An expression that evaluates to a value of type VARIANT.
Returns TRUE if its VARIANT argument contains an a floating-point value, fixed-point decimal, or integer value.
variant_expr: An expression that evaluates to a value of type VARIANT.
Returns TRUE if its VARIANT argument contains an integer value.
variant_expr: An expression that evaluates to a value of type VARIANT.
Returns TRUE if its VARIANT argument contains an NULL value.
variant_expr: An expression that evaluates to a value of type VARIANT.
Returns TRUE if its VARIANT argument contains an OBJECT value.
variant_expr: An expression that evaluates to a value of type VARIANT.
Returns TRUE if its VARIANT argument contains an TIME value.
variant_expr: An expression that evaluates to a value of type VARIANT.
Verifies whether a VARIANT value contains the respective TIMESTAMP value
variant_expr: An expression that evaluates to a value of type VARIANT.
Verifies whether a VARIANT value contains the respective TIMESTAMP value
variant_expr: An expression that evaluates to a value of type VARIANT.
Verifies whether a VARIANT value contains the respective TIMESTAMP value
variant_expr: An expression that evaluates to a value of type VARIANT.
Reports the type of a value stored in a VARIANT column. The type is returned as a string.
column: The column to detect the type of
Returns the number of times that a pattern occurs in a string.
subject: Subject to match.
pattern: Pattern to match.
position: Number of characters from the beginning of the string where the function starts searching for matches. Default: 1 (the search for a match starts at the first character on the left)
parameters: String of one or more characters that specifies the parameters used for searching for matches
Returns the position of the specified occurrence of the regular expression pattern in the string subject. If no match is found, returns 0.
subject: Subject to match.
pattern: Pattern to match.
position: Number of characters from the beginning of the string where the function starts searching for matches. Default: 1
occurrence: Specifies which occurrence of the pattern to match. The function skips the first occurrence -1 matches. Default: 1
option: Specifies whether to return the offset of the first character of the match (0) or the offset of the first character following the end of the match (1). Default: 0
parameters: String of one or more characters that specifies the parameters used for searching for matches. Supported values: c, i, m, e, s
Returns true if the subject matches the pattern. Both expressions must be text expressions.
subject: Subject to match.
pattern: Pattern to match.
parameters: String of one or more characters that specifies the parameters used for searching for matches. Supported values: c, i, m, e, s
Returns the subject with the specified pattern (or all occurrences of the pattern) either removed or replaced by a replacement string. If no matches are found, returns the original subject.
subject: Subject to match.
pattern: Pattern to match.
replacement: String the replaces the substrings matched by the pattern. If an empty string is specified, the function removes all matched patterns and returns the resulting string. Default: '' (empty string).
position: Number of characters from the beginning of the string where the function starts searching for matches. Default: 1 (the search for a match starts at the first character on the left)
occurrence: Specifies which occurrence of the pattern to replace. If 0 is specified, all occurrences are replaced. Default: 0 (all occurrences)
parameters: String of one or more characters that specifies the parameters used for searching for matches. Supported values: c, i, m, e, s
Returns the position of the specified occurrence of the regular expression pattern in the string subject. If no match is found, returns 0.
subject: Subject to match.
pattern: Pattern to match.
position: Number of characters from the beginning of the string where the function starts searching for matches. Default: 1
occurrence: Specifies which occurrence of the pattern to match. The function skips the first occurrence -1 matches. Default: 1
parameters: String of one or more characters that specifies the parameters used for searching for matches. Supported values: c, i, m, e, s
Returns true if the subject matches the specified pattern. Both inputs must be text expressions.
subject: Subject to match
pattern: Pattern to match.
parameters: String of one or more characters that specifies the parameters used for searching for matches. Supported values: c, i, m, e, s
Returns the ASCII code for the first character of a string. If the string is empty, a value of 0 is returned.
input: The string for which the ASCII code for the first character in the string is returned.
Returns the length of a string or binary value in bits. Snowflake doesn't use fractional bytes so length is always calculated as 8 * OCTET_LENGTH.
string_or_binary: The string or binary value for which the length is returned.
Searches for the first occurrence of the first argument in the second argument and, if successful, returns the position (1-based) of the first argument in the second argument.
expr1: A string or binary expression representing the value we are looking for.
expr2: A string or binary expression representing the value in which we are searching.
start_pos: A number indicating the position from where to start the search (with 1 representing the start of expr1). Default: 1
Converts a Unicode code point (including 7-bit ASCII) into the character that matches the input Unicode. If an invalid code point is specified, an error is returned.
input: The Unicode code point for which the character is returned.
Converts a Unicode code point (including 7-bit ASCII) into the character that matches the input Unicode. If an invalid code point is specified, an error is returned.
input: The Unicode code point for which the character is returned.
Concatenates two strings or two binary values. If one of the them is null, the result is also null.
expr1: A string to concatenate
expr2: A string to concatenate
Returns true if expr1 contains expr2. Both expressions must be text or binary expressions.
expr1: A string or binary expression representing the value we are looking fo
expr2: A string or binary expression representing the value in which we are searching.
Computes the Levenshtein distance between two input strings. It is the number of single-character insertions, deletions or substitutions needed to convert one string to another.
expr1: A string
expr2: A string
Returns TRUE if the first expression ends with second expression. Both expressions must be text or binary expressions.
expr1: A string or binary expression representing the value we are looking fo
expr2: A string or binary expression representing the value in which we are searching.
Allows matching of strings based on comparison with a pattern. Unlike the LIKE function, string matching is case-insensitive. LIKE, ILIKE, and RLIKE all perform similar operations; however, RLIKE uses POSIX EXE (Extended Regular Expression) syntax instead of the SQL pattern syntax used by LIKE and ILIKE.
subject: Subject to match.
pattern: Pattern to match.
escape: Character(s) inserted in front of a wildcard character to indicate that the wildcard should be interpreted as a regular character and not as a wildcard.
Returns the input string (expr) with the first letter of each word in uppercase and the subsequent letters in lowercase.
expr: The input string
delimiters: an optional argument specifying a string of one or more characters that INITCAP uses as separators for words in the input expression:
Replaces a substring of the specified length, starting at the specified position, with a new string or binary value. This function should not be confused with the INSERT DML command.
base_expr: The string or BINARY expression for which you want to insert/replace characters.
pos: The offset at which to start inserting characters. This is 1-based, not 0-based.
len: The number of characters (starting at pos) that you want to replace. Valid values range from 0 to the number of characters between pos and the end of the string.
insert_expr: The string to insert into the base_expr. If this string is empty, and if len is greater than zero, then effectively the operation becomes a delete (some characters are deleted, and none are added).
Returns a leftmost substring of its input. LEFT(STR,N) is equivalent to SUBSTR(STR,1,N).
string_expr: The string expression
length_expr: The length to extract
Returns the length of a input string or binary value. For strings, the length is the number of characters, and UTF-8 characters are counted as a single character. For binary, the length is the number of bytes.
expr: The expression to measure
Allows case-sensitive matching of strings based on comparison with a pattern. For case-insensitive matching, use ILIKE instead.
subject: Subject to match.
pattern: Pattern to match.
escape: Character(s) inserted in front of a wildcard character to indicate that the wildcard should be interpreted as a regular character and not as a wildcard.
Returns the input string (expr) with all characters converted to lowercase.
expr: The input string
Left-pads a string with characters from another string, or left-pads a binary value with bytes from another binary value.
base: The base string to pad
n: The number of characters to bad
pad: The character to pad
Removes leading characters, including whitespace, from a string.
expr: The string expression to be trimmed.
characters: One or more characters to remove from the left side of expr. The default value is ' ' (a single blank space character),
Returns the length of a string or binary value in bytes. This will be the same as LENGTH for ASCII strings and greater than LENGTH for strings using Unicode code points. For binary, this is always the same as LENGTH.
string_or_binary: The string or binary value for which the length is returned.
Returns a JSON object consisting of all the components from a valid INET (Internet Protocol) or CIDR (Classless Internet Domain Routing) IPv4 or IPv6 string.
expr: A string expression.
type: Identifies the type of IP address. Supports either INET or CIDR; case-insenstitive.
permissive: Flag that determines how parse errors are handled:
Returns a JSON object consisting of all the components (fragment, host, path, port, query, scheme) in a valid input URL/URI.
string: String to parse.
permissive: Flag that determines how parse errors are handled
Searches for the first occurrence of the first argument in the second argument and, if successful, returns the position (1-based) of the first argument in the second argument.
expr1: A string or binary expression representing the value we are looking for.
expr2: A string or binary expression representing the value in which we are searching.
start_pos: A number indicating the position from where to start the search (with 1 representing the start of expr2).
Searches for the first occurrence of the first argument in the second argument and, if successful, returns the position (1-based) of the first argument in the second argument.
expr1: A string or binary expression representing the value we are looking for.
expr2: A string or binary expression representing the value in which we are searching.
Builds a string by repeating the input for the specified number of times.
input: The input string from which the output string is built.
n: The number of times the input string should be repeated. The minimum valid number is 0 (which results in an empty string).
Removes all occurrences of a specified substring, and optionally replaces them with another string
subject: The subject is the string in which to do the replacements. Typically, this is a column, but it can be a literal.
pattern: This is the substring that you want to replace. Typically, this is a literal, but it can be a column or expression. Note that this is not a "regular expression"; if you want to use regular expressions to search for a pattern, use the REGEXP_REPLACE function.
replacement: This is the value used as a replacement for the pattern. If this is omitted, or is an empty string, then the REPLACE function simply deletes all occurrences of the pattern.
everses the order of characters in a string, or of bytes in a binary value.
subject: The string to reverse
Returns a rightmost substring of its input.
string_expr: The string expression
length_expr: The length to extract
Right-pads a string or binary value with characters from another string.
base: The base string to pad
n: The number of times to pad
pad: The string to pad with
Removes trailing characters, including whitespace, from a string.
expr: The string expression to be trimmed.
characters: One or more characters to remove from the right side of expr:
Returns the length of its argument, minus trailing whitespace, but including leading whitespace.
string_expr: The string expression to measure
Builds a string consisting of the specified number of blank spaces.
n: The number of blank spaces used to build the string.
Splits a given string with a given separator and returns the result in an array of strings. Contiguous split strings in the source string, or the presence of a split string at the beginning or end of the source string, results in an empty string in the output. An empty separator string results in an array containing only the source string. If either parameter is a NULL, a NULL is returned.
string: Text to be split into parts.
separator: Text to split string by.
Splits a string with the given delimiter and flattens the results into rows.
string: Text to be split into parts.
delimiter: Text to split string by.
Tokenizes a given string and returns the requested part.
string: Text to be tokenized.
delimiter: Text representing the set of delimiters to tokenize on. Each character in the delimiter string is a delimiter. If the delimiter is empty, and the string is empty, then the function returns NULL. If the delimiter is empty, and the string is non empty, then the whole string will be treated as one token. The default value of the delimiter is a single space character.
partNr: Requested token (1-based, i.e. the first token is token number 1, not token number 0). If the token number is out of range, then NULL is returned. The default value is 1.
Splits a given string and returns the requested part. If a part does not exist, an empty string is returned. If any parameter is NULL, NULL is returned.
string: Text to be split into parts.
delimiter: Text representing the delimiter to split by.
partNr: Requested part of the split (1-based). 0 is treated as 1. If the value is negative, the parts are counted from the right side of the string.
Returns true if expr1 starts with expr2. Both expressions must be text or binary expressions.
expr1: A string or binary expression representing the value we are looking fo
expr2: A string or binary expression representing the value in which we are searching.
Returns the portion of the string or binary value from base_expr, starting from the character/byte specified by start_expr, with optionally limited length.
base_expr: The base string
start_expr: The position to start from
length_expr: Up to length_expr characters/bytes are returned, otherwise all the characters until the end of the string or binary value are returned.
Returns the portion of the string or binary value from base_expr, starting from the character/byte specified by start_expr, with optionally limited length.
input: A BINARY or string value (or expression) to be compressed.
method: A string with compression method and optional compression level. Supported methods are: SNAPPY. ZLIB. ZSTD. BZ2. The compression level is specified in parentheses, for example: zlib(1). The compression level is a non-negative integer. 0 means default level (same as omitting the compression level). The compression level is ignored if the method doesn't support compression levels.
Returns the portion of the string or binary value from base_expr, starting from the character/byte specified by start_expr, with optionally limited length.
input: A BINARY value (or expression) with data that was compressed using one of the compression methods specified in COMPRESS. If you attempt to decompress a compressed string, rather than a compressed BINARY value, you will not get an error; the function will return a BINARY value. See the Usage Notes below for details.
method: The compression method originally used to compress the input.
Decompresses the compressed BINARY input parameter to a string.
input: A BINARY value (or expression) with data that was compressed using one of the compression methods specified in COMPRESS.
method: The compression method originally used to compress the input.
Returns the portion of the string or binary value from base_expr, starting from the character/byte specified by start_expr, with optionally limited length.
base_expr: The base string
start_expr: The position to start from
length_expr: Up to length_expr characters/bytes are returned, otherwise all the characters until the end of the string or binary value are returned.
Translates subject from the characters in sourceAlphabet to the characters in targetAlphabet.
subject: A string expression that is translated. If a character in subject is not contained in sourceAlphabet, the character is added to the result without any translation.
sourceAlphabet: A string with all characters that are modified by this function. Each character is either translated to the corresponding character in the targetAlphabet or omitted in the result if the targetAlphabet has no corresponding character (i.e. has less characters than the sourceAlphabet).
targetAlphabet: A string with all characters that are used to replace characters from the sourceAlphabet.
Removes leading and trailing characters from a string.
expr: A string expression to be trimmed.
characters: One or more characters to remove from the left and right side of expr: The default value is ' ' (a single blank space character), i.e. if no characters are specified, all leading and trailing blank spaces are removed.
Returns the Unicode code point for the first Unicode character in a string. If the string is empty, a value of 0 is returned.
input: The string for which the Unicode code point for the first character in the string is returned.
Returns the input string expr with all characters converted to uppercase.
expr: The input string
Decodes a Base64-encoded string to a binary.
input: A Base64-encoded string expression.
alphabet: A string consisting of up to three ASCII characters
Decodes a Base64-encoded string to a binary.
input: A Base64-encoded string expression.
alphabet: A string consisting of up to three ASCII characters
Encodes the input (string or binary) using Base64 encoding.
input: A string or binary expression to be encoded.
max_line_length: A positive integer that specifies the maximum number of characters in a single line of the output.
alphabet: A string consisting of up to three ASCII characters
Decodes a hex-encoded string to a binary.
input: A string expression containing only hexadecimal digits. Typically, this input string is generated by calling the function HEX_ENCODE.
Decodes a hex-encoded string to a string.
input: A hex-encoded string expression. Typically the input was created by a call to HEX_ENCODE.
Encodes the input using hexadecimal (also 'hex' or 'base16') encoding. The result is comprised of 16 different symbols: The numbers '0' to '9' as well as the letters 'A' to 'F'
input: A binary or string expression to be encoded.
case: This optional boolean argument controls the case of the letters ('A', 'B', 'C', 'D', 'E' and 'F') used in the encoding. The default value is 1 and indicates that uppercase letters are used. The value 0 indicates that lowercase letters are used. All other values are illegal and result in an error.
A special version of BASE64_DECODE_BINARY that returns a NULL value if an error occurs during decoding.
input: A Base64-encoded string expression.
alphabet: A string consisting of up to three ASCII characters
A special version of BASE64_DECODE_BINARY that returns a NULL value if an error occurs during decoding
input: A Base64-encoded string expression.
alphabet: A string consisting of up to three ASCII characters
A special version of HEX_DECODE_BINARY that returns a NULL value if an error occurs during decoding.
input: A string expression containing only hexadecimal digits. Typically, this input string is generated by calling the function HEX_ENCODE.
A special version of HEX_DECODE_BINARY that returns a NULL value if an error occurs during decoding.
input: A hex-encoded string expression. Typically the input was created by a call to HEX_ENCODE.
Returns a 32-character hex-encoded string containing the 128-bit MD5 message digest.
msg: A string expression, the message to be hashed.
Returns a 32-character hex-encoded string containing the 128-bit MD5 message digest.
msg: A string expression, the message to be hashed
Returns a 16-byte BINARY value containing the 128-bit MD5 message digest.
msg: A string expression, the message to be hashed.
Returns the 128-bit MD5 message digest interpreted as a signed 128-bit big endian number. This representation is useful for maximally efficient storage and comparison of MD5 digests.
msg: A string expression, the message to be hashed.
Returns a 40-character hex-encoded string containing the 160-bit SHA-1 message digest.
msg: A string expression, the message to be hashed.
Returns a 40-character hex-encoded string containing the 160-bit SHA-1 message digest.
msg: A string expression, the message to be hashed.
Returns a 20-byte binary containing the 160-bit SHA-1 message digest.
msg: A string expression, the message to be hashed.
Returns a hex-encoded string containing the N-bit SHA-2 message digest, where N is the specified output digest size.
msg: A string expression, the message to be hashed
digest_size: Size (in bits) of the output, corresponding to the specific SHA-2 function used to encrypt the string
Returns a hex-encoded string containing the N-bit SHA-2 message digest, where N is the specified output digest size.
msg: A string expression, the message to be hashed
digest_size: Size (in bits) of the output, corresponding to the specific SHA-2 function used to encrypt the string
Returns a binary containing the N-bit SHA-2 message digest, where N is the specified output digest size.
msg: A string expression, the message to be hashed
digest_size: Size (in bits) of the output, corresponding to the specific SHA-2 function used to encrypt the string
Returns some value of the expression from the group. The result is non-deterministic.
expr: A group of values to choose from
Returns the average of non-NULL records. If all records inside a group are NULL, NULL is returned.
expr: Expression is an expression that evaluates to a numeric data type (INTEGER, FLOAT, DECIMAL, etc.).
Returns the correlation coefficient for non-null pairs in a group. It is computed for non-null pairs using the following formula: COVAR_POP(y, x) / (STDDEV_POP(x) * STDDEV_POP(y))
y:
c:
Returns either the number of non-NULL records for the specified columns, or a total number of records.
expr1: A column name.
expr2: You may include additional column name(s) if you wish. For example, you could list the number of distinct combinations of last name and first name.
Returns the population covariance for non-null pairs in a group. It is computed for non-null pairs using the following formula: (SUM(x*y) - SUM(x) * SUM(y) / COUNT(*)) / COUNT(*) Where x is the independent variable and y is the dependent variable.
x:
y:
Returns the sample covariance for non-null pairs in a group. It is computed for non-null pairs using the following formula: (SUM(x*y) - SUM(x) * SUM(y) / COUNT(*)) / (COUNT(*) - 1) Where x is the independent variable and y is the dependent variable.
y:
x:
Returns the concatenated input values, separated by the delimiter string.
expr: The expression (typically a column name) that determines the values to be put into the list. The expression should evaluate to a string, or to a data type that can be cast to string.
delimiter: A string, or an expression that evaluates to a string. In practice, this is usually a single-character string. The string should be surrounded by single quotes, as shown in the examples below.
Returns the minimum or maximum value for the records within expr. NULL values are ignored unless all the records are NULL, in which case a NULL value is returned.
expr:
Returns the minimum or maximum value for the records within expr. NULL values are ignored unless all the records are NULL, in which case a NULL value is returned.
expr:
Determines the median of a set of values.
expr: The expression must evaluate to a numeric data type (INTEGER, FLOAT, DECIMAL, or equivalent).
Return a percentile value based on a continuous distribution of the input column (specified in order_by_expr). If no input row lies exactly at the desired percentile, the result is calculated using linear interpolation of the two nearest input values. NULL values are ignored in the calculation.
percentile: The percentile of the value that you want to find. The percentile must be a constant between 0.0 and 1.0. For example, if you want to find the value at the 90th percentile, specify 0.9.
Returns a percentile value based on a discrete distribution of the input column (specified in order_by_expr). The returned value is that whose row has the smallest CUME_DIST value that is greater than or equal to the given percentile. NULL values are ignored in the calculation.
percentile: The percentile of the value that you want to find. The percentile must be a constant between 0.0 and 1.0. For example, if you want to find the value at the 90th percentile, specify 0.9.
Returns the sample standard deviation (square root of sample variance) of non-NULL values. If all records inside a group are NULL, returns NULL.
expr: An expression that evaluates to a numeric value.
Returns the sample standard deviation (square root of sample variance) of non-NULL values. If all records inside a group are NULL, returns NULL.
expr: An expression that evaluates to a numeric value (integer, floating point, or fixed point).
Returns the population standard deviation (square root of variance) of non-NULL values. If all records inside a group are NULL, returns NULL.
x:
Returns the sum of non-NULL records for expr. If the DISTINCT keyword is used, the sum of unique non-null values is computed. If all records inside a group are NULL, a value of NULL is returned.
expr: The expression to sum
Returns the population variance of non-NULL records in a group. If all records inside a group are NULL, a NULL is returned.
x:
Returns the population variance of non-NULL records in a group. If all records inside a group are NULL, a NULL is returned.
x:
Returns the sample variance of non-NULL records in a group. If all records inside a group are NULL, a NULL is returned.
x:
Returns the sample variance of non-NULL records in a group. If all records inside a group are NULL, a NULL is returned.
x:
Returns the sample variance of non-NULL records in a group. If all records inside a group are NULL, a NULL is returned.
x:
Finds the cumulative distribution of a value with regard to other values within the same window partition.
Returns the rank of a value within a group of values, without gaps in the ranks. The rank value starts at 1 and continues up sequentially. If two values are the same, they will have the same rank.
Returns the first value within an ordered group of values.
expr:
Accesses data in a previous row in the same result set without having to join the table to itself.
expr: The string expression to be returned.
offset: The number of rows backward from the current row from which to obtain a value; e.g., an offset of 2 returns the expr value with an interval of 2 rows.
default: The expression to return when the offset goes out of the bounds of the window. Supports any expression whose type is compatible with expr
Returns the last value within an ordered group of values.
expr:
Accesses data in a subsequent row in the same result set without having to join the table to itself.
expr: The string expression to be returned.
offset: The number of rows forward from the current row from which to obtain a value; e.g., an offset of 2 returns the expr value with an interval of 2 rows.
default: The expression to return when the offset goes out of the bounds of the window. Supports any expression whose type is compatible with expr.
Returns the nth value (up to 1000) within an ordered group of values.
expr:
n: Input value n cannot be greater than 1000.
Divides an ordered data set equally into the number of buckets specified by constant_value. Buckets are sequentially numbered 1 through constant_value.
constant_value: The desired number of buckets; must be a positive integer value.
Returns the relative rank of a value within a group of values.
Returns the rank of a value within an ordered group of values.
Returns a unique row number for each row within a window partition.
Constructs equi-width histograms, in which the histogram range is divided into intervals of identical size, and returns the bucket number into which the value of an expression falls, after it has been evaluated. The function returns an integer value or null (if any input is null).
expr: The expression for which the histogram is created. This expression must evaluate to a numeric value or to a value that can be implicitly converted to a numeric value.
min_value: The low and high end points of the acceptable range for the expression. The end points must also evaluate to numeric values and not be equal.
max_value: The low and high end points of the acceptable range for the expression. The end points must also evaluate to numeric values and not be equal.
num_buckets: The desired number of buckets; must be a positive integer value. A value from the expression is assigned to each bucket, and the function then returns the corresponding bucket number.
Returns the bitwise AND value of all non-NULL numeric records in a group. If all records inside the group are NULL, or if the group is empty, the function returns NULL.
expr:
Returns the bitwise OR value of all non-NULL numeric records in a group. If all records inside the group are NULL, or if the group is empty, the function returns NULL.
expr:
Returns the bitwise XOR value of all non-NULL numeric records in a group. If all records inside the group are NULL, or if the group is empty, the function returns NULL.
expr:
Returns the input values, pivoted into an ARRAY. If the input is empty, an empty ARRAY is returned.
expr: The expression (typically a column name) that determines the values to be put into the list
Returns the average of the independent variable for non-null pairs in a group, where x is the independent variable and y is the dependent variable.
y:
x:
Returns the number of non-null number pairs in a group.
y:
x:
Returns the intercept of the univariate linear regression line for non-null pairs in a group. It is computed for non-null pairs using the following formula: AVG(y)-REGR_SLOPE(y,x)*AVG(x) Where x is the independent variable and y is the dependent variable
y:
x:
Returns the coefficient of determination for non-null pairs in a group.
y:
x:
Returns the slope of the linear regression line for non-null pairs in a group
y:
x:
Returns REGR_COUNT(y, x) * VAR_POP(x) for non-null pairs.
y:
x:
Returns REGR_COUNT(expr1, expr2) * COVAR_POP(expr1, expr2) for non-null pairs.
y:
x:
Returns REGR_COUNT(y, x) * VAR_POP(y) for non-null pairs.
y:
x:
Returns the average of the dependent variable for non-null pairs in a group, where x is the independent variable and y is the dependent variable.
y:
x:
Uses HyperLogLog to return an approximation of the distinct cardinality of the input.
expr:
Uses HyperLogLog to return an approximation of the distinct cardinality of the input.
expr:
Combines (merges) input states into a single output state. This allows scenarios where HLL_ACCUMULATE is run over horizontal partitions of the same table, producing an algorithm state for each table partition. These states can later be combined using HLL_COMBINE, producing the same output state as a single run of HLL_ACCUMULATE over the entire table.
state: An expression that contains state information generated by a call to HLL_ACCUMULATE.
Returns the cardinality estimate for the given HyperLogLog state. A HyperLogLog state produced by HLL_ACCUMULATE and HLL_COMBINE can be used to compute a cardinality estimate using the HLL_ESTIMATE function.
state: An expression that contains state information generated by a call to HLL_ACCUMULATE or HLL_COMBINE.
Returns the HyperLogLog state at the end of aggregation.
expr:
Converts input in BINARY format to OBJECT format. The HyperLogLog states operated on by HLL_ACCUMULATE, HLL_COMBINE, and HLL_ESTIMATE are in a proprietary binary format that may change in future versions of Snowflake. For long-term storage of HyperLogLog states, and for integration with external tools, Snowflake supports converting states from the BINARY format to an OBJECT (which can be printed and exported as JSON), and vice versa.
binary_expr:
Converts input in OBJECT format to BINARY format. The HyperLogLog states operated on by HLL_ACCUMULATE, HLL_COMBINE, and HLL_ESTIMATE are in a proprietary binary format that may change in future versions of Snowflake. For long-term storage of HyperLogLog states, and for integration with external tools, Snowflake supports using HLL_IMPORT to convert states from an OBJECT format to BINARY, and vice versa.
obj:
Returns an estimation of the similarity (Jaccard index) of inputs based on their MinHash states. For more information about Jaccard indexes and the related function MINHASH, see Estimating Similarity of Two or More Sets.
expr: The expression(s) should be one or more MinHash states returned by calls to the MINHASH function. In other words, the expressions must be MinHash state information, not the column or expression for which you want the approximate similarity. (The example below helps make this clear.)
Returns an estimation of the similarity (Jaccard index) of inputs based on their MinHash states. For more information about Jaccard indexes and the related function MINHASH, see Estimating Similarity of Two or More Sets.
expr: The expression(s) should be one or more MinHash states returned by calls to the MINHASH function. In other words, the expressions must be MinHash state information, not the column or expression for which you want the approximate similarity. (The example below helps make this clear.)
Returns a MinHash state containing an array of size k constructed by applying k number of different hash functions to the input rows and keeping the minimum of each hash function. This MinHash state can then be input to the APPROXIMATE_SIMILARITY function to estimate the similarity with one or more other MinHash states.
k: specifies the number of hash functions to be created. The larger the value, the better the approximation; however, this value has a linear impact on the computation time for estimating similarity using APPROXIMATE_SIMILARITY. The suggested value is 100.
expr:
Combines input MinHash states into a single MinHash output state. This Minhash state can then be input to the APPROXIMATE_SIMILARITY function to estimate the similarity with other MinHash states.
state: Input MinHash state must have MinHash arrays of equal length.
Uses Space-Saving to return an approximation of the most frequent values in the input, along with their approximate frequencies. The output is a JSON array of arrays. In the inner arrays, the first entry is a value in the input, and the second entry corresponds to its estimated frequency in the input. The outer array contains k items, sorted by descending frequency.
expr: The expression (e.g. column name) for which you want to find the most common values.
k: The number of values whose counts you want approximated. For example, if you want to see the top 10 most common values, then set k to 10.
counters: This is the maximum number of distinct values that can be tracked at a time during the estimation process. For example, if counters is set to 100000, then the algorithm tracks 100,000 distinct values, attempting to keep the 100,000 most frequent values.
Returns the Space-Saving summary at the end of aggregation. (For more information about the Space-Saving summary, see Estimating Frequent Values.) The function APPROX_TOP_K discards its internal, intermediate state when the final cardinality estimate is returned. However, in certain advanced use cases, such as estimating incremental frequent values during bulk loading, you might want to keep the intermediate state, in which case you would use APPROX_TOP_K_ACCUMULATE instead of APPROX_TOP_K..
expr: The expression (e.g. column name) for which you want to find the most common values.
counters: This is the maximum number of distinct values that can be tracked at a time during the estimation process. For example, if counters is set to 100000, then the algorithm tracks 100,000 distinct values, attempting to keep the 100,000 most frequent values
Combines (merges) input states into a single output state. This allows scenarios where APPROX_TOP_K_ACCUMULATE is run over horizontal partitions of the same table, producing an algorithm state for each table partition. These states can later be combined using APPROX_TOP_K_COMBINE, producing the same output state as a single run of APPROX_TOP_K_ACCUMULATE over the entire table.
state: An expression that contains state information generated by a call to APPROX_TOP_K_ACCUMULATE.
counters: This is the maximum number of distinct values that can be tracked at a time during the estimation process. For example, if counters is set to 100000, then the algorithm tracks 100,000 distinct values, attempting to keep the 100,000 most frequent values.
Returns the approximate most frequent values and their estimated frequency for the given Space-Saving state. (For more information about the Space-Saving summary, see Estimating Frequent Values.)
state: An expression that contains state information generated by a call to APPROX_TOP_K_ACCUMULATE or APPROX_TOP_K_COMBINE.
k: The number of values whose counts you want approximated. For example, if you want to see the top 10 most common values, then set k to 10.
Returns an approximated value for the desired percentile (i.e. if column c has n numbers, then APPROX_PERCENTILE(c, p) returns a number such that approximately n * p of the numbers in c are smaller than the returned number).
expr: A valid expression, such as a column name, that evaluates to a numeric value.
percentile: A constant real value greater than or equal to 0.0 and less than 1.0. This indicates the percentile (from 0 to 99.999...). E.g. The value 0.65 indicates the 65th percentile.
Returns the internal representation of the t-Digest state (as a JSON object) at the end of aggregation. (For more information about t-Digest, see: Estimating Percentile Values.)
expr: A valid expression, such as a column name, that evaluates to a numeric value.
Combines (merges) percentile input states into a single output state.
state: An expression that contains state information generated by a call to APPROX_PERCENTILE_ACCUMULATE.
Returns the desired approximated percentile value for the specified t-Digest state.
state: An expression that contains state information generated by a call to APPROX_PERCENTILE_ACCUMULATE or APPROX_PERCENTILE_COMBINE.
percentile: A constant real value greater than or equal to 0.0 and less than 1.0. This indicates the percentile (from 0 to 99.999...). E.g. The value 0.65 indicates the 65th percentile.
Describes which of a list of expressions are grouped in a row produced by a GROUP BY query.
expr1:
Describes which of a list of expressions are grouped in a row produced by a GROUP BY query.
expr1:
Aborts the specified session.
session_id: Identifier for the session to abort.
Aborts the specified transaction, if it is running. If the transaction has already been committed or rolled back, then the state of the transaction is not altered.
transaction_id: Identifier for the transaction to abort. To obtain transaction IDs, you can use the SHOW TRANSACTIONS or SHOW LOCKS commands.
Cancels all active/running queries in the specified session.
session_id: Identifier for the session for which to cancel all queries.
Cancels the specified query (or statement) if it is currently active/running.
query_id: Identifier for the query to cancel.
Forces a pipe paused using ALTER PIPE to resume. This is necessary if the pipe owner transfers ownership of the pipe to another role while the pipe is paused.
pipe_name: Pipe to resume running.
Waits for a specified amount of time before proceeding.
amount: Number specifying the amount of time to wait as determined by time_unit.
time_unit: Time unit for amount. Accepted values are DAYS, HOURS, MINUTES, SECONDS, MILLISECONDS, MICROSECONDS, NANOSECONDS. The unit should be in single quotes
Returns the commit time of the last DML change performed on a table or a view. In case of a view, the function returns the latest commit time of all the objects referenced in the view.
object_name: ecifies the table or view for which the commit time for the last DML is returned
Retrieves a JSON representation of the current status of a pipe.
pipe_name: Pipe for which you want to retrieve the current status.
Returns a string representing the SQL data type associated with an expression.
expr:
SELECT {
[ TOP <numeric_literal> | DISTINCT ]
{
*
| {
<expression> [ [ AS ] <column_reference> ]
| { <table_name> | <correlation_name> } .*
} [ , ... ]
}
[ INTO csv:// [ filename= ] <file_path> [ ;delimiter=tab ] ]
{
FROM <table_reference> [ [ AS ] <identifier> ]
} [ , ... ]
[ [
INNER | { { LEFT | RIGHT | FULL } [ OUTER ] }
] JOIN <table_reference> [ ON <search_condition> ] [ [ AS ] <identifier> ]
] [ ... ]
[ WHERE <search_condition> ]
[ GROUP BY <column_reference> [ , ... ]
[ HAVING <search_condition> ]
[ UNION [ ALL ] <select_statement> ]
[
ORDER BY
<column_reference> [ ASC | DESC ] [ NULLS FIRST | NULLS LAST ]
]
[
LIMIT <expression>
[
{ OFFSET | , }
<expression>
]
]
} | SCOPE_IDENTITY()
<expression> ::=
| <column_reference>
| @ <parameter>
| ?
| COUNT( * | { [ DISTINCT ] <expression> } )
| { AVG | MAX | MIN | SUM | COUNT } ( <expression> )
| NULLIF ( <expression> , <expression> )
| COALESCE ( <expression> , ... )
| CASE <expression>
WHEN { <expression> | <search_condition> } THEN { <expression> | NULL } [ ... ]
[ ELSE { <expression> | NULL } ]
END
| <literal>
| <sql_function>
<search_condition> ::=
{
<expression> { = | > | < | >= | <= | <> | != | LIKE | NOT LIKE | IN | NOT IN | IS NULL | IS NOT NULL | AND | OR | CONTAINS | BETWEEN } [ <expression> ]
} [ { AND | OR } ... ]
SELECT * FROM [DemoDB].[PUBLIC].Products
SELECT [ProductName] AS MY_ProductName FROM [DemoDB].[PUBLIC].Products
SELECT CAST(Price AS VARCHAR) AS Str_Price FROM [DemoDB].[PUBLIC].Products
SELECT * FROM [DemoDB].[PUBLIC].Products WHERE ProductName = 'Konbu'
SELECT COUNT(*) AS MyCount FROM [DemoDB].[PUBLIC].Products
SELECT COUNT(DISTINCT ProductName) FROM [DemoDB].[PUBLIC].Products
SELECT DISTINCT ProductName FROM [DemoDB].[PUBLIC].Products
SELECT ProductName, MAX(Price) FROM [DemoDB].[PUBLIC].Products GROUP BY ProductName
SELECT Customers.ContactName, Orders.OrderDate FROM Customers, Orders WHERE Customers.CustomerId=Orders.CustomerId
SELECT Id, ProductName FROM [DemoDB].[PUBLIC].Products ORDER BY ProductName ASC
SELECT Id, ProductName FROM [DemoDB].[PUBLIC].Products LIMIT 10
SELECT * FROM [DemoDB].[PUBLIC].Products WHERE ProductName = @param
SELECT COUNT(*) FROM [DemoDB].[PUBLIC].Products WHERE ProductName = 'Konbu'
SELECT COUNT(DISTINCT Id) AS DistinctValues FROM [DemoDB].[PUBLIC].Products WHERE ProductName = 'Konbu'
SELECT ProductName, AVG(Price) FROM [DemoDB].[PUBLIC].Products WHERE ProductName = 'Konbu'
GROUP BY ProductName
SELECT MIN(Price), ProductName FROM [DemoDB].[PUBLIC].Products WHERE ProductName = 'Konbu'
GROUP BY ProductName
SELECT ProductName, MAX(Price) FROM [DemoDB].[PUBLIC].Products WHERE ProductName = 'Konbu'
GROUP BY ProductName
SELECT SUM(Price) FROM [DemoDB].[PUBLIC].Products WHERE ProductName = 'Konbu'
SELECT Customers.ContactName, Orders.OrderDate FROM Customers, Orders WHERE Customers.CustomerId=Orders.CustomerId
SELECT Customers.ContactName, Orders.OrderDate FROM Customers LEFT OUTER JOIN Orders ON Customers.CustomerId=Orders.CustomerId
SELECT * FROM MyTable WHERE MyDateField = L_TODAY()
SELECT * FROM MyTable WHERE MyDateField = L_YESTERDAY()
SELECT * FROM MyTable WHERE MyDateField = L_TOMORROW()
SELECT * FROM MyTable WHERE MyDateField = L_LAST_WEEK()
SELECT * FROM MyTable WHERE MyDateField = L_THIS_WEEK()
SELECT * FROM MyTable WHERE MyDateField = L_NEXT_WEEK()
SELECT * FROM MyTable WHERE MyDateField = L_LAST_N_DAYS(3)
SELECT * FROM MyTable WHERE MyDateField = L_NEXT_N_DAYS(3)
SELECT * FROM MyTable WHERE MyDateField = L_LAST_N_WEEKS(3)
SELECT * FROM MyTable WHERE MyDateField = L_NEXT_N_WEEKS(3)