SQL Functions

LiveRamp's Data Collaboration Platform supports the SQL functions listed on this page.

Tip

For information about user-defined functions (), see "User-Defined Functions."

Limitations:

Many SQL functions cannot be applied to a sensitive column, such as a rampid column.
Window Functions cannot be applied to an asset with a query threshold (minimum aggregation).

Aggregate Functions

Aggregate functions return a single value based on multiple rows/values of input.

Function	Description
&	Return the result of the bitwise AND of two expressions.
^	Return the result of the bitwise exclusive OR (XOR) of two expressions.
\|	Return the result of the bitwise OR of two expressions.
~	Return the result of the bitwise NOT of the expression.
approx_count_distinct	Estimate cardinality (the number of elements of a set) using the HyperLogLog++ algorithm.
approx_percentile	Return the approximate percentile of a numeric column. You must specify a column and a percentage. The percentage value or percentage array values must be between 0 and 1. Note Do not use the approx_percentile function when you need precise percentiles. The approx_percentile function is intended to efficiently calculate approximate percentiles for large amounts of data when the exact percentiles are not critical. The approx_percentile function works the same as the percentile_approx function. LiveRamp's implementation of this function does not support Apache Spark's accuracy parameter.
avg	Calculate the mean from a group of values.
collect_list	Return the collected list of non-unique elements.
collect_set	Return the collected set of unique elements.
count	Return the total number of rows specified by the expression. Multi-column counts are not supported.
cume_dist	Return the position of the specified value in relation to all values in its partition. The `qt` function is not supported when used with this function.
max	Return the maximum value specified in the expression.
min	Return the minimum value specified in the expression.
percentile_approx	Return the approximate percentile of a numeric column. You must specify a column and a percentage. The percentage value or percentage array values must be between 0 and 1. Note Do not use the approx_percentile function when you need precise percentiles. The approx_percentile function is intended to efficiently calculate approximate percentiles for large amounts of data when the exact percentiles are not critical. The percentile_approx function works the same as the approx_percentile function. LiveRamp's implementation of this function does not support Apache Spark's accuracy parameter.
shiftleft	Shift left (base, expr). For example: SELECT shiftleft(4, 1);
shiftright	Shift right (base, expr). For example: SELECT shiftright(2, 1);
sum	Calculate the sum from a group of values.

Array Functions

Array functions return an array with an element for each row in a subquery.

Function	Description
array	Return an array with the specified elements.
array_agg	Return a list of non-unique elements. The results are likely to be different each time (non-deterministic) because the row order is shuffled.
array_contains	Return true if the array contains the specified value. This function may not work with some aggregations because the corresponding unnest operator has some limitations on BigQuery.
array_distinct	Remove any duplicate values from the returned array.
array_intersect	Return an array of the distinct elements in two specified columns and remove any duplicates.
array_join	Concatenate the elements specified in an array and replace null values with an optional string.
generate_array	Return an array based on a start, end, and (optional) step integer expression. This function's parameters don't support the float data type. The generate_array and sequence functions work the same way.
sequence	Return an array based on a start, end, and (optional) step integer expression. This function's parameters don't support the float data type. The generate_array and sequence functions work the same way.
size	Return the size of an array or a map.
sort_array	Sort the input array in ascending or descending order

Bitwise Functions

Bitwise functions perform bitwise operations on numbers or numeric records.

Function	Description
bit_and	Return the bitwise AND of non-null input values.
bit_count	Return the number of bits set in the expression as an unsigned 64-bit integer.
bit_or	Return the result of the bitwise OR of non-null input values.
bit_xor	Return the bitwise XOR of non-null input values.

Conversion Functions

Conversion functions convert an expression to a different data type.

Function	Description
cast	Cast the specified value as the specified data type.
chr	Return the ASCII character that is the binary equivalent of the value specified in the expression. If the number is larger than 256, return the equivalent % of 256: chr(n % 256).
date	Cast the specified value to the date data type. The value can be a timestamp or a string in ISO 8601 format.
double	Cast the specified value to the double-precision floating-point format.
int	Cast the specified value as the int data type.
string	Cast the specified value as the string data type.
timestamp	Cast the specified value to the timestamp data type.

Date and Timestamp Functions

Date and Timestamp functions calculate and manipulate date and time values.

Function	Description
+	Return the sum of the first and second expressions. You can use this mathematical function for date addition in the following formats: Add the specified number of days and return the resulting date: date + `num_days` Where `date` is the date function and `num_days` is a numeric value. Add the specified number of date parts and return the resulting date: date + INTERVAL `num_parts` `part_name` Where `num_parts` is a numeric value and `part_name` is an interval (such as SECOND, MINUTE, HOUR, DAY, WEEK, MONTH, QUARTER, or YEAR). For example: date + INTERVAL 3 MONTH
current_date	Return the current date.
current_timestamp	Return the current timestamp.
current_timezone	Return the local time zone of the current session.
date	Cast the specified value to the date data type. The value can be a timestamp or a string in the ISO 8601 format. `date + num` adds the specified number of days. For example, the following statement would change an s_rec_start_date of 2023-03-13 to 2023-03-14. select s_rec_start_date, date(s_rec_start_date) + 1 from store
date_add	Return the date that is num_days after start_date.
date_diff	Return the difference between the start date and the end date expressions based on the requested unit. date_diff(`unit`, `start_date`, `end_date`) Where `unit` strings must not be in quotation marks.
date_from_unix_date	Return the date from the specified number of days since the Unix epoch (1970-01-01).
date_part	Return the specified date or time part from a date, a time, or a timestamp in the following format: date_part(`part`, `source_date`) For example: `select s_rec_start_date, date_part(year, date(s_rec_start_date)) y from store` Where `s_rec_start_date` is a date column with complete dates (such as YYYY-MM-DD format) and date_part would extract the YYYY value.
date_sub	Return the date that is num_days before start_date.
date_trunc	Return the specified timestamp after truncating it to the specified format. date_trunc(`field`, `source_date`) For example: `select s_rec_start_date, date_trunc(week, date(s_rec_start_date)) w from store` Where `s_rec_start_date` is a date column with complete dates and date_trunc would extract the date at start of the week (`w`) for each date value. For example, a s_rec_start_date value of 2023-03-15 (a Wednesday) would return 2023-03-13 (a Monday).
dayofweek	Return the day of the week for the timestamp (where 1 = Sunday, 2 = Monday, 3 = Tuesday, 4 = Wednesday, 5 = Thursday, 6 = Friday, and 7 = Saturday).
from_utc_timestamp	Return the specified UTC timestamp after converting it to the specified time zone.
last_day	Return the last day of the month based on the specified date.
now	Return the current timestamp.
parse_date	Convert a string representation of a date to a DATE value. `parse_date(format_string, date_string)` This function is only supported on BigQuery.
timestamp	Cast the specified value to the timestamp data type.
timestamp_micros	Return a timestamp based on the specified number of microseconds since the Unix epoch.
timestamp_millis	Return a timestamp based on the specified number of milliseconds since the Unix epoch.
timestamp_seconds	Return a timestamp based on the specified number of seconds since the Unix epoch.
trunc	Return the specified date after truncating it to the time portion based on the specified format model.
unix_micros	Return the number of microseconds since the Unix epoch.
unix_millis	Return the number of milliseconds since the Unix epoch.
unix_seconds	Return the number of seconds since the Unix epoch.
weekday	Return the day of the week for the timestamp (where 0 = Monday, 1 = Tuesday, 2 = Wednesday, 3 = Thursday, 4 = Friday, 5 = Saturday, and 6 = Sunday).

LiveRamp Data Collaboration Platform Functions

LiveRamp Data Collaboration Platform functions provide ways to set query thresholds and return information about RampIDs.

Function	Description
qt	Protect row-level data when creating a UDF or when querying a table or view. Include the following in your query statement or UDF creation statement: `qt(table_name, column_name, threshold_value)` Where: `table_name` is replaced with the name of a table or a view in SQL Editor's All Assets pane. `column_name` is replaced with the name of a column in your table or view. `threshold_value` is replaced with a numeric value for the needed threshold. For example, you could include `qt(brand, rampid, 5000)` in your UDF creation statement to apply a minimum aggregation of 5000 results. Note The qt function is used as a replacement for the table it references. Window Functions cannot be applied to an asset with a query threshold (minimum aggregation).
rampid_matched	Return false if the RampID value indicates that no RampID was matched against the individual. For example, you can use this function in a `where` clause and specify the column name that includes RampIDs: `where rampid_matched(rampid_column_name) = true` Note RampID columns are sensitive, so this function cannot be used in arbitrary filter expressions. You are permitted to use this function to filter out rows that lack a valid RampID.
rampid_type	When used in a `where` clause, returns the following RampID types: `HOUSEHOLD_MAINTAINED`: Represents adults living together at the same location who exhibit a persistent relationship. It is a pseudonymized version of an AbiliTec Household ID that is based on PII. `INDIVIDUAL_DERIVED`: The RampID is generated from the input PII provided when there is no maintained record for the input data. `INDIVIDUAL_MAINTAINED`: Represents a person that the Data Collaboration Platform can fully recognize. `NULL`: No RampID is specified. `PLACEHOLDER_COOKIE`: Represents a placeholder cookie ID that LiveRamp has not yet identified `PLACEHOLDER_MOBILE`: Represents an identifier provided for a mobile device ID that the Data Collaboration Platform has not yet identified `UNKNOWN`: The identifier is not a valid RampID. `UNMATCHED`: A match could not be found.

Function

Description

Protect row-level data when creating a UDF or when querying a table or view. Include the following in your query statement or UDF creation statement:

qt(table_name, column_name, threshold_value)

Where:

table_name is replaced with the name of a table or a view in SQL Editor's All Assets pane.
column_name is replaced with the name of a column in your table or view.
threshold_value is replaced with a numeric value for the needed threshold.

For example, you could include qt(brand, rampid, 5000) in your UDF creation statement to apply a minimum aggregation of 5000 results.

Note

The qt function is used as a replacement for the table it references.
Window Functions cannot be applied to an asset with a query threshold (minimum aggregation).

rampid_matched

Return false if the RampID value indicates that no RampID was matched against the individual. For example, you can use this function in a where clause and specify the column name that includes RampIDs:

where rampid_matched(rampid_column_name) = true

Note

RampID columns are sensitive, so this function cannot be used in arbitrary filter expressions. You are permitted to use this function to filter out rows that lack a valid RampID.

rampid_type

When used in a where clause, returns the following RampID types:

HOUSEHOLD_MAINTAINED: Represents adults living together at the same location who exhibit a persistent relationship. It is a pseudonymized version of an AbiliTec Household ID that is based on PII.
INDIVIDUAL_DERIVED: The RampID is generated from the input PII provided when there is no maintained record for the input data.
INDIVIDUAL_MAINTAINED: Represents a person that the Data Collaboration Platform can fully recognize.
NULL: No RampID is specified.
PLACEHOLDER_COOKIE: Represents a placeholder cookie ID that LiveRamp has not yet identified
PLACEHOLDER_MOBILE: Represents an identifier provided for a mobile device ID that the Data Collaboration Platform has not yet identified
UNKNOWN: The identifier is not a valid RampID.
UNMATCHED: A match could not be found.

Logical Functions

Logical functions perform a logical operation or comparison on the specified expressions.

Note

When using aggregation, equality and comparison operators are not supported with the qt function (except for the in function).

Function	Description
!	Return the logical NOT of the specified expression, such as returning false when true is specified.
!=	Return true if the first expression is not equal to the second expression. Otherwise, return false. The expressions must be of the same type. The `qt` function is not supported when used with this function.
<	Return true if the first expression is less than the second expression. The `qt` function is not supported when used with this function.
<	Return true if the first expression is less than the second expression. Otherwise, return false. The expressions must be of the same type. The `qt` function is not supported when used with this function.
<=	Return true if the first expression is less than or equal to the second expression. Otherwise, return false. The expressions must be of the same type. The `qt` function is not supported when used with this function.
<=>	If two expressions are not null, return the same result as the EQUAL operator. If both expressions are null, return true. If one expression is null, return false. The `qt` function is not supported when used with this function.
<>	Return true if the first expression is not equal to the second expression. Otherwise, return false. The expressions must be of the same type. The `qt` function is not supported when used with this function.
=	Return true if the first expression is equal to the second expression. Otherwise, return false. The expressions must be of the same type. The `qt` function is not supported when used with this function.
==	Return true if the first expression is equal to the second expression. Otherwise, return false. The expressions must be of the same type. The `qt` function is not supported when used with this function.
>	Return true if the first expression is greater than the second expression. Otherwise, return false. The expressions must be of the same type. The `qt` function is not supported when used with this function.
>=	Return true if the first expression is greater than or equal to the second expression. Otherwise, return false. The expressions must be of the same type. The `qt` function is not supported when used with this function.
and	Return the logical AND of two expressions.
between	Evaluate whether the values in the first expression are between the second and third expressions. The `qt` function is not supported when used with this function.
case	Return a specified result when a condition is met. Otherwise, return the value specified by the ELSE clause. The `qt` function is not supported when used with this function.
coalesce	Return the first non-null argument. Otherwise, return null.
if	Return the second expression if the first expression is true. Otherwise, return the third expression. The `qt` function is not supported when used with this function.
ifnull	Return the first argument if it's not null. Otherwise, return the second argument. The `qt` function is not supported when used with this function.

in	Return true if the specified value equals any of the specified arguments.
isnotnull	Return true if the specified argument is not null. Otherwise, return false.
isnull	Return true if the specified argument is null. Otherwise, return false.
not	Return the logical NOT of the expression.
nullif	Return null if the first expression is equal to the second expression. The `qt` function is not supported when used with this function.
or	Return the logical OR of the expression.
when	Return a specified result when branched conditions are met. Otherwise, return the value specified by the ELSE clause. The expressions must be of the Boolean type. The `qt` function is not supported when used with this function.

Mathematical Functions

Mathematical functions perform calculations on the specified values.

Function	Description
-	Return the difference between the first and second expressions.
*	Return the product of the first and second expressions.
/	Divide the first expression by the second expression and return the floating-point representation of the result.
+	Return the sum of the first and second expressions. You can also use this function for date addition. For more information, see "Datetime Functions" above.
abs	Return the absolute value of the specified numeric value.
acos	Return the inverse cosine of the specified value.
acosh	Return the inverse hyperbolic cosine of the specified value.
asin	Return the inverse sine of the specified value.
asinh	Return the inverse hyperbolic sine of the specified value.
atan	Return the inverse tangent of the specified value.
atanh	Returns the angle in radians between the positive x-axis of a plane and the point given by the specified coordinates.
ceil	Return the smallest integer that is not smaller than the specified value.
ceiling	Return the smallest integer that is not smaller than the specified value.
corr	Return the Pearson correlation coefficient between the specified set of number pairs.
cos	Return the cosine of the specified value.
cosh	Return the hyperbolic cosine of the specified value.
covar_pop	Return the population covariance of the specified set of number pairs.
covar_samp	Return the sample covariance of the specified set of number pairs.
div	Divide the first expression by the second expression and return the resulting integer.
e	Return Euler's number.
exp	Return e to the power of the specified value.
floor	Return the largest integer that is not greater than the specified value.
greatest	Return the greatest value of all the specified parameters.
least	Return the smallest value of all the specified parameters. You can use this function with numeric (such as integer and float), date, and timestamp expressions.
log10	Return the logarithm of the specified expression with base 10.
mod	Return the remainder from the division of the first expression by the second expression.
pi	Return an approximate value of pi.
pow	Return the value of the first expression raised to the power of the second expression.
power	Return the value of the first expression raised to the power of the second expression.
rand	Return a random value with independent and identically distributed uniformly distributed values within [0, 1]. Seed values are not supported. When run across different data warehouses, this function does not support resulting values via common table expressions (CTEs). You can use this function to generate a pseudo-random uniform number between 0 and 1 when sampling table rows.
random	Return a random value with independent and identically distributed uniformly distributed values within [0, 1]. Seed values are not supported. When run across different data warehouses, this function does not support resulting values via common table expressions (CTEs). You can use this function to generate a pseudo-random uniform number between 0 and 1 when sampling table rows.
round	Return the value of the first expression rounded to the specified decimal places.
sign	If the specified value is negative, return -1.0. If the specified value is 0, return 0. If the specified value is positive, return 1.0.
sin	Return the sine of the specified value.
sinh	Return the hyperbolic sine of the specified value.
sqrt	Return the square root of the specified value.
stddev	Return the sample standard deviation of the specified values.
stddev_pop	Return the population standard deviation of the specified values.
stddev_samp	Return the sample standard deviation of the specified values.
tan	Return the tangent of the specified value.
tanh	Return the hyperbolic tangent of the specified value.
var_pop	Return the population variance of the specified values.
var_samp	Return the sample variance of the specified values.
variance	Return the sample variance of the specified values.

String Functions

String functions perform operations on string values.

Function	Description
\|\|	Concatenate the specified values or arrays.
ascii	Return the ASCII code of the first character of the specified value.
char_length	Return the character length of the specified string.
character_length	Return the character length of the specified string.
concat	Return the concatenation of the specified values.
initcap	Return the specified set of characters after capitalizing the first letter of each word and setting other characters to lowercase.
instr	Return the numeric position of the first letter of the second specified string within the first string.
length	Return the character length of the specified string or the number of bytes of the specified binary data.
like	Return true if the specified string matches the specified pattern. Otherwise, return false.
lower	Return the specified string in all lowercase characters.
lpad	Return the value padded to the left with a specified number of characters. (Optional) You can specify the character to pad with.
ltrim	Return the specified string after removing any leading space characters.
octet_length	Return the byte length of the specified string or the number of bytes of specified binary data.
regexp_extract	Return the first string in the specified value that matches the specified REGEX. This function does not support an optional group index argument. Only one capture group is allowed.
repeat	Return the specified string repeated the specified number of times.
replace	Replace occurrences of the specified string and replace them with another string.
rpad	Return the value padded to the right with a specified number of characters. (Optional) You can specify the character to pad with.
rtrim	Return the specified string after removing any trailing space characters.
soundex	Return the Soundex code (a phonetic representation) for the specified string.
split	Split a string at instances of REGEX values and return an array. (Optional) Specify an integer to limit the number of times to apply the REGEX.
string	Cast the specified value as the string data type.
substr	Return part of the specified string based on a specified starting position and length.
substring	Return part of the specified string based on a specified starting position and length. `substring(value, pos, [len])` has the index starting at 1 instead of 0
translate	Return the specified string after replacing any characters specified in a second string with the corresponding values in a third string.
trim	Return the specified string after removing any leading and trailing space characters.
upper	Return the specified string in all uppercase characters.

Window Functions

Window functions return a value for each row based on values from a set of table rows.

Window functions cannot be applied to an asset with a query threshold (minimum aggregation).

Function	Description
dense_rank	Return the rank of a value in a group of values. Each result represents the previously assigned rank +1. The `qt` function is not supported when used with this function.
lag	Return the value of the preceding row. The `qt` function is not supported when used with this function.
lead	Return the value of the input string at the row offset (integer) after the current row in the window. The `qt` function is not supported when used with this function.
nth_value	Return the value of the input at the offset^th row from beginning of the window frame. The `qt` function is not supported when used with this function.
ntile	Divide window partition rows into n buckets from 1 through n.
percent_rank	Return the percentage ranking of a value in a group of values.
rank	Return the ranking of a value in a group of values. Each result represents the number of rows that precede or is equal to the current row of the partition +1. The `qt` function is not supported when used with this function.
row_number	Assign a unique and sequential number to each row based on the ordering of rows within the window partition. The `qt` function is not supported when used with this function.

Other Functions

Function	Description
hash	Return a hash value of the arguments
typeof	Return the Data Definition Language- (DDL) formatted string for the specified value's data type.

In this section:

cookiemobile device ID

SQL Functions

Tip

Aggregate Functions

Note

Note

Array Functions

Bitwise Functions

Conversion Functions

Date and Timestamp Functions

LiveRamp Data Collaboration Platform Functions

Note

Note

Logical Functions

Note

Mathematical Functions

String Functions

Window Functions

Other Functions

Search results