Built-in Functions

The built-in scalar and aggregate functions for math, strings, dates, JSON, regex, hashing, geospatial work, and H3 indexing.

Collection

• ARRAYConstruct an ARRAY value from a list of expressions.
• ARRAY_AGGAggregate a column of values into a single array.
• ARRAY_APPENDReturn a new array with one element added at the end.
• ARRAY_AVGReturn the arithmetic mean of the numeric elements in an array.
• ARRAY_CATConcatenate two arrays into one.
• ARRAY_COMPACTReturn a new array with all NULL elements removed.
• ARRAY_CONTAINSTest whether an array contains a value.
• ARRAY_DIMSReturn the dimensions of an array as a text representation.
• ARRAY_DISTINCTReturn a new array with duplicate elements removed.
• ARRAY_EXCEPTReturn the set difference of two arrays.
• ARRAY_FILLCreate a multi-dimensional array filled with a single value.
• ARRAY_FIRSTReturn the first element of an array.
• ARRAY_INSERTInsert an element at a given position in an array, shifting subsequent elements right.
• ARRAY_INTERSECTReturn the distinct elements present in both arrays.
• ARRAY_JOINConcatenate array elements into a single delimited string.
• ARRAY_LASTReturn the last element of an array.
• ARRAY_LENGTHReturn the length of a given array dimension.
• ARRAY_LOWERReturn the lower bound (starting subscript) of an array dimension.
• ARRAY_MAXReturn the maximum element of an array.
• ARRAY_MINReturn the minimum element of an array.
• ARRAY_NDIMSReturn the number of dimensions (rank) of an array.
• ARRAY_OVERLAPTest whether two arrays share at least one element.
• ARRAY_POSITIONReturn the 1-based position of the first matching element in an array.
• ARRAY_POSITIONSReturn every 1-based position where a value appears in an array.
• ARRAY_PREPENDReturn a new array with one element added at the beginning.
• ARRAY_REMOVEReturn a new array with every occurrence of a value removed.
• ARRAY_REPEATCreate an array by repeating a single value a given number of times.
• ARRAY_REPLACEReturn a new array with every occurrence of a value replaced by another value.
• ARRAY_REVERSEReturn a new array with the elements in reverse order.
• ARRAY_SAMPLEReturn a random sample of n elements from an array without replacement.
• ARRAY_SHUFFLEReturn a new array with the elements in a random order.
• ARRAY_SIZEReturn the element count of an array.
• ARRAY_SLICEReturn the sub-array between two 1-based positions, inclusive on both ends.
• ARRAY_SORTReturn a new array with the elements sorted in ascending order.
• ARRAY_SUMReturn the sum of the numeric elements of an array.
• ARRAY_UNIONReturn the distinct union of two arrays.
• ARRAY_UPPERReturn the upper bound (largest subscript) of an array dimension.
• ARRAYS_OVERLAPTest whether two arrays share at least one element.
• ARRAYS_ZIPMerge multiple arrays element-wise into an array of structs.
• CARDINALITYReturn the element count of an array or key count of a map.
• CONCATConcatenate two or more arrays, strings, or binary values into a single value.
• ELEMENT_ATReturn the element at a given array position or the value for a given map key.
• FLATTENFlatten a nested array by one level, concatenating inner arrays into a single flat array.
• GENERATE_SUBSCRIPTSGenerate the set of valid 1-based subscripts along an array dimension.
• GETReturn the array element at a given 0-based index.
• MAPConstruct a MAP value from alternating key-value arguments.
• MAP_CONCATMerge two or more maps into a single map; later values win on duplicate keys.
• MAP_CONTAINS_KEYTest whether a map contains a given key.
• MAP_ENTRIESReturn the entries of a map as an array of key-value structs.
• MAP_FROM_ARRAYSBuild a map by pairing elements of a key array and a value array.
• MAP_FROM_ENTRIESBuild a map from an array of key-value structs.
• MAP_KEYSReturn the keys of a map as an array.
• MAP_KVConstruct a map from alternating key-value arguments.
• MAP_VALUESReturn the values of a map as an array.
• SEQUENCEGenerate an array of values from start to stop with a given step.
• SHUFFLEReturn a new array with the elements in a random order.
• SIZEReturn the element count of an array or map.
• SLICEReturn a sub-array starting at a position with a given length.
• SORT_ARRAYSort an array in ascending or descending order.
• UNNESTExpand an array into a set of rows, one per element.

Conditional

• ASSERT_TRUERaise a runtime error if the condition is FALSE or NULL; return NULL silently when TRUE.
• COALESCEReturn the first non-NULL argument from a list of expressions.
• GREATESTReturn the greatest value from a list of expressions.
• IFReturn true_value if the condition is TRUE, otherwise false_value.
• IFNULLReturn expr1 if not NULL, otherwise expr2.
• ISNANReturn TRUE if the floating-point value is NaN, otherwise FALSE.
• ISNOTNULLReturn TRUE if the expression is not NULL, otherwise FALSE.
• ISNULLReturn TRUE if the expression is NULL, otherwise FALSE.
• LEASTReturn the smallest value from a list of expressions.
• NANVLReturn expr1 if not NaN, otherwise return expr2.
• NULLIFReturn NULL if two expressions are equal, otherwise return the first.
• NVLReturn expr1 if not NULL, otherwise expr2.
• NVL2Return one value if expr is not NULL, another if it is NULL.
• RAISE_ERRORUnconditionally raise a runtime error with a message.
• WHENEvaluate condition-value pairs and return the value of the first TRUE condition.

Datetime

• ADD_MONTHSAdd (or subtract) a number of months to a date, clamping to the end of the month when needed.
• AGECompute a symbolic year-month-day interval between two timestamps.
• CLOCK_TIMESTAMPReturn the actual wall-clock timestamp, re-read on every call.
• CURRENT_DATEReturn the current session date with no time component.
• CURRENT_TIMEReturn the current time-of-day with time zone information.
• CURRENT_TIMESTAMPReturn the current date and time, fixed at transaction start.
• DATE_ADDAdd (or subtract) a signed number of days to a date.
• DATE_BINBin a timestamp into uniform-width intervals aligned to a chosen origin.
• DATE_DIFFReturn the difference between two dates or timestamps in a specified unit.
• DATE_FORMATFormat a date or timestamp as a string using percent-prefixed pattern tokens.
• DATE_PARTExtract a named field from a date, timestamp, or interval (functional form of EXTRACT).
• DATE_SUBSubtract a signed number of days from a date.
• DATE_TRUNCTruncate a timestamp down to the start of a specified unit (year, month, day, hour, etc.).
• DATEDIFFReturn the number of whole days between two dates as end minus start.
• DAYOFMONTHExtract the day-of-month (1 through 31) from a date.
• DAYOFWEEKExtract the day-of-week number from a date, with Sunday as 1 and Saturday as 7.
• DAYOFYEARExtract the ordinal day of the year (1 through 366) from a date.
• DAYS_BETWEENReturn the number of whole days between two dates as date1 minus date2.
• EXTRACTExtract a named field from a date, timestamp, or interval using SQL-standard syntax.
• EXTRACT_EPOCHExtract seconds since 1970-01-01 UTC from a timestamp, or total duration in seconds from an interval.
• FROM_UNIXTIMEConvert a Unix epoch seconds value into a formatted date-time string.
• HOURExtract the hour-of-day (0 through 23) from a timestamp.
• INTERVAL_MULTIPLYMultiply an INTERVAL by a numeric factor to scale its duration.
• ISFINITETest whether a date, timestamp, or interval value is finite (not positive or negative infinity).
• JUSTIFY_DAYSNormalize an INTERVAL so the days component stays below 30 by rolling excess days into months.
• JUSTIFY_HOURSNormalize an INTERVAL so the hours component stays below 24 by rolling excess hours into days.
• JUSTIFY_INTERVALNormalize an INTERVAL by rolling excess hours into days and excess days into months.
• LAST_DAYReturn the last day of the month that contains the given date.
• LOCALTIMEReturn the current time-of-day without time zone information.
• LOCALTIMESTAMPReturn the current date and time without time zone information.
• MAKE_DATEConstruct a DATE from integer year, month, and day components.
• MAKE_INTERVALConstruct an INTERVAL value from optional year, month, week, day, hour, minute, and second components.
• MAKE_TIMEConstruct a TIME value from hour, minute, and second components.
• MAKE_TIMESTAMPConstruct a TIMESTAMP WITHOUT TIME ZONE from year, month, day, hour, minute, and second components.
• MAKE_TIMESTAMPTZConstruct a TIMESTAMP WITH TIME ZONE from components with an optional zone name.
• MINUTEExtract the minute-of-hour (0 through 59) from a timestamp.
• MONTHExtract the month component (1 through 12) from a date or timestamp.
• MONTHS_BETWEENReturn the number of months between two dates, including a fractional part when days differ.
• NEXT_DAYReturn the next date strictly after the input that falls on the specified weekday.
• NOWReturn the current transaction timestamp, identical to CURRENT_TIMESTAMP.
• QUARTERExtract the calendar quarter (1 through 4) from a date or timestamp.
• SECONDExtract the whole-second component (0 through 59) from a timestamp.
• STATEMENT_TIMESTAMPReturn the timestamp captured at the start of the current statement.
• TIMEOFDAYReturn the current wall-clock date and time as a formatted text string.
• TO_CHARFormat a date, timestamp, interval, or numeric value as a string using template patterns.
• TO_DATEParse a string into a DATE using an explicit or default format pattern.
• TO_TIMESTAMPParse a string into a TIMESTAMP, or convert an epoch-seconds number into a TIMESTAMP.
• TO_UNIX_TIMESTAMPConvert a TIMESTAMP value into Unix epoch seconds.
• TRANSACTION_TIMESTAMPReturn the timestamp captured at the start of the current transaction.
• TRUNCTruncate a DATE to the start of a specified calendar unit.
• UNIX_TIMESTAMPReturn the current Unix epoch seconds, or parse a string and return its epoch seconds.
• WEEKOFYEARExtract the ISO 8601 week number (1 through 53) from a date.
• YEARExtract the four-digit year component from a date or timestamp.
• YEARS_BETWEENReturn the number of whole completed years between two dates.

Encoding

• CONVERT_FROMDecode binary data (BYTEA) into text using a specified source character encoding.
• CONVERT_TOEncode a text string as BYTEA using a specified target character encoding.
• PG_DECODEDecode a text string into binary data using base64, hex, or escape format.
• PG_ENCODEEncode binary data (BYTEA) as text using base64, hex, or escape format.

Geospatial

• ST_AREACompute the planar area of a polygon or multipolygon geometry in the units of its coordinate system.
• ST_AS_TEXTSerialize a geometry to its Well-Known Text (WKT) representation.
• ST_AZIMUTHCompute the initial azimuth in radians from one point to another on a spherical Earth.
• ST_BEARINGCompute the initial great-circle bearing in compass degrees from one point to another on a spherical Earth.
• ST_CONTAINSTest whether the first geometry completely contains the second geometry.
• ST_DISTANCECompute the planar Euclidean distance between two geometries in the units of their coordinate system.
• ST_DISTANCE_HAVERSINECompute the great-circle distance between two points on a spherical Earth using the Haversine formula.
• ST_DISTANCE_SPHERECompute the great-circle distance between two points on a spherical Earth using the spherical law of cosines.
• ST_DISTANCE_VINCENTYCompute the geodesic distance between two points on the WGS 84 ellipsoid using Vincenty's inverse formula.
• ST_FINAL_BEARINGCompute the final bearing in compass degrees at the destination of a great-circle path from one point to another.
• ST_GEOM_FROM_TEXTParse a Well-Known Text (WKT) string into an internal geometry value.
• ST_LENGTHCompute the total planar length of a line or perimeter of a polygon in the units of the coordinate system.
• ST_MAKE_POINTCreate a POINT geometry from numeric x and y coordinates.
• ST_WITHINTest whether the first geometry lies completely inside the second geometry.
• ST_XReturn the X coordinate of a POINT geometry.
• ST_YReturn the Y coordinate of a POINT geometry.

H3

• H3_CELL_AREAReturn the exact surface area of an H3 cell in square meters on the WGS 84 ellipsoid.
• H3_CELL_AREA_KM2Return the exact surface area of an H3 cell in square kilometers on the WGS 84 ellipsoid.
• H3_CELL_TO_BOUNDARYReturn the boundary of an H3 cell as a WKT POLYGON string.
• H3_CELL_TO_CENTER_CHILDReturn the center child of an H3 cell at a finer resolution.
• H3_CELL_TO_CHILDRENReturn all child cells of an H3 cell at a specified finer resolution.
• H3_CELL_TO_LATReturn the WGS 84 latitude of the center of an H3 cell.
• H3_CELL_TO_LNGReturn the WGS 84 longitude of the center of an H3 cell.
• H3_CELL_TO_PARENTReturn the H3 parent cell that contains a given cell at a coarser resolution.
• H3_CELL_TO_STRINGFormat an H3 cell index as its canonical 15 hexadecimal-digit string representation.
• H3_CONTAINS_FASTTest whether a coarser H3 cell contains a finer H3 cell using a fast hierarchical prefix check.
• H3_EDGE_LENGTHReturn the average edge length of an H3 cell in meters for its resolution.
• H3_GET_RESOLUTIONReturn the H3 hierarchy resolution of a cell as an integer 0 through 15.
• H3_GRID_DISTANCEReturn the grid distance between two H3 cells as an integer number of hex steps.
• H3_GRID_PATHReturn the array of H3 cells on the shortest grid path from one cell to another.
• H3_HEX_DISKReturn the array of H3 cells within k grid steps of a center cell, inclusive of the center.
• H3_HEX_RINGReturn the array of H3 cells on the ring exactly k grid steps from a center cell.
• H3_IS_PENTAGONTest whether an H3 cell is one of the 12 pentagon cells at its resolution.
• H3_IS_RES_CLASS_IIITest whether an H3 cell is at a Class III (odd-numbered) resolution in the H3 hierarchy.
• H3_IS_VALID_CELLTest whether a BIGINT value encodes a valid H3 cell index.
• H3_LATLNG_TO_CELLConvert a latitude and longitude pair to the H3 cell index that contains the point at a given resolution.
• H3_LATLNG_TO_CELL_FASTConvert latitude and longitude to an H3 cell using a vectorized, multi-threaded batch path.
• H3_POLYFILLReturn the H3 cells at a given resolution whose centers fall inside a WKT polygon.
• H3_SPATIAL_JOIN_FASTTest whether two H3 cells overlap (equal or one is an ancestor of the other) using a fast hierarchical check.
• H3_STRING_TO_CELLParse an H3 cell string in canonical 15 hex-digit form into its BIGINT cell index.

Hash

• BASE64Encode binary data or a string as a Base64 string (RFC 4648).
• CRC32Compute the CRC-32 checksum of a string as a non-negative 32-bit integer.
• HASHCompute a non-cryptographic 32-bit hash of one expression.
• HASH_DECODEDecode binary data to a string using a named character set.
• HASH_ENCODEEncode a string as binary data using a named character set.
• HEXConvert an integer or string to its hexadecimal text representation.
• MD5Compute the MD5 digest of a string as a 32-character lowercase hex string.
• SHACompute the SHA-1 digest of a string as a 40-character lowercase hex string.
• SHA1Compute the SHA-1 digest of a string as a 40-character lowercase hex string.
• SHA2Compute a SHA-2 family digest (SHA-224, SHA-256, SHA-384, or SHA-512) as a lowercase hex string.
• UNBASE64Decode a Base64 string (RFC 4648) to binary data.
• UNHEXDecode a hexadecimal string into binary data.
• WEIGHTED_CHAR_CHECKSUMCompute a position-weighted character checksum of a string.
• XXHASH64Compute a non-cryptographic 64-bit xxHash of one or more expressions.

JSON

• GET_JSON_OBJECTExtract a JSON sub-value from a JSON-formatted string using a JSONPath expression.
• JSON_AGGAggregate values from grouped rows into a single JSON array.
• JSON_ARRAY_LENGTHReturn the number of elements in a JSON array.
• JSON_BUILD_ARRAYBuild a JSON array from a variadic list of values.
• JSON_BUILD_OBJECTBuild a JSON object from alternating key-value arguments.
• JSON_EXTRACT_PATHExtract a JSON sub-value by traversing a sequence of keys.
• JSON_EXTRACT_PATH_TEXTExtract a JSON sub-value at a path and return it as text.
• JSON_OBJECT_KEYSReturn the top-level keys of a JSON object as a set of text values.
• JSON_STRIP_NULLSRecursively remove object keys whose value is JSON null.
• JSON_TYPEOFReturn the type of the outermost JSON value as text.
• JSONB_AGGAggregate values from grouped rows into a single JSONB array.
• JSONB_ARRAY_LENGTHReturn the number of elements in a JSONB array.
• JSONB_BUILD_ARRAYBuild a JSONB array from a variadic list of values.
• JSONB_BUILD_OBJECTBuild a JSONB object from alternating key-value arguments.
• JSONB_EXTRACT_PATHExtract a JSONB sub-value by traversing a sequence of keys.
• JSONB_EXTRACT_PATH_TEXTExtract a JSONB sub-value at a path and return it as text.
• JSONB_OBJECT_KEYSReturn the top-level keys of a JSONB object as a set of text values.
• JSONB_PRETTYPretty-print a JSONB value as text with indentation and line breaks.
• JSONB_STRIP_NULLSRecursively remove object keys whose value is JSONB null.
• JSONB_TYPEOFReturn the type of the outermost JSONB value as text.
• ROW_TO_JSONConvert a row or record to a JSON object.
• TO_JSONConvert a SQL value to its JSON representation.
• TO_JSONBConvert a SQL value to its JSONB representation.

Math

• ABSReturn the absolute value of a numeric expression.
• ACOSCompute the inverse cosine (arccosine) of a value, returning an angle in radians.
• ACOSHCompute the inverse hyperbolic cosine of a real number at least 1.
• ASINCompute the inverse sine (arcsine) of a value, returning an angle in radians.
• ASINHCompute the inverse hyperbolic sine of a real number.
• ATANCompute the inverse tangent (arctangent) of a value, returning an angle in radians.
• ATAN2Compute the two-argument arctangent, returning the angle of the point (x, y) in radians.
• ATANHCompute the inverse hyperbolic tangent, defined on the open interval (-1, 1).
• BROUNDRound a numeric value using banker's rounding (round half to even).
• CBRTCompute the real cube root of a numeric expression, including negative values.
• CEILRound up to the smallest integer greater than or equal to the input.
• CEILINGRound up to the smallest integer greater than or equal to the input (alias for CEIL).
• COSCompute the cosine of an angle expressed in radians.
• COSHCompute the hyperbolic cosine of a real number.
• COTCompute the cotangent of an angle expressed in radians.
• CSCCompute the cosecant (reciprocal of sine) of an angle in radians.
• DEGREESConvert an angle from radians to degrees.
• EReturn the mathematical constant e (Euler's number, approximately 2.71828).
• EXPCompute e raised to the given power.
• EXPM1Compute e^x - 1 with high precision for small x.
• FLOORRound down to the largest integer less than or equal to the input.
• HYPOTCompute sqrt(x*x + y*y) without intermediate overflow or underflow.
• LNCompute the natural logarithm (base e) of a positive number.
• LOGCompute the logarithm with a specified base (defaults to natural logarithm).
• LOG10Compute the base-10 (common) logarithm of a positive number.
• LOG1PCompute ln(1 + x) with high precision for small x.
• LOG2Compute the base-2 (binary) logarithm of a positive number.
• MODReturn the remainder of dividing one number by another (truncated division, sign follows dividend).
• NEGATIVEReturn the arithmetic negation of a numeric expression.
• PIReturn the mathematical constant pi (approximately 3.14159265358979).
• PMODReturn the non-negative remainder of dividing one number by another.
• POSITIVEReturn the input unchanged (unary plus as a function form).
• POWRaise a base to the given exponent.
• POWERRaise a base to the given exponent (alias for POW).
• PYTHON_ROUNDRound a numeric value using Python / IEEE 754 semantics (round half to even).
• RADIANSConvert an angle from degrees to radians.
• RINTRound to the nearest integer, returning a DOUBLE, using banker's rounding.
• ROUNDRound a numeric value to a specified number of decimal places using half-up (away-from-zero) rounding.
• SECCompute the secant (reciprocal of cosine) of an angle in radians.
• SIGNReturn the sign of a number as -1, 0, or 1.
• SIGNUMReturn the sign of a number as a DOUBLE: -1.0, 0.0, or 1.0 (alias for SIGN).
• SINCompute the sine of an angle expressed in radians.
• SINHCompute the hyperbolic sine of a real number.
• SQRTCompute the non-negative square root of a non-negative number.
• TANCompute the tangent of an angle expressed in radians.
• TANHCompute the hyperbolic tangent, a smooth saturating function bounded by -1 and 1.

Misc

• BIT_ANDReturn the bitwise AND of two integer values.
• BIT_COUNTReturn the number of bits set to 1 in an integer (population count).
• BIT_NOTReturn the bitwise complement of an integer value.
• BIT_ORReturn the bitwise OR of two integer values.
• BIT_XORReturn the bitwise exclusive OR of two integer values.
• CLEAN_SPACESCollapse runs of whitespace into a single space and trim leading/trailing whitespace.
• CURRENT_CATALOGReturn the name of the catalog (top-level database) bound to the current session.
• CURRENT_DATABASEReturn the current database name (synonym for CURRENT_CATALOG).
• CURRENT_SCHEMAReturn the name of the schema (namespace) bound to the current session.
• CURRENT_SETTINGReturn the current value of a named configuration parameter.
• CURRENT_USERReturn the effective user name for the current session.
• EMPTYIFNULLReturn an empty string if the input is NULL, otherwise return the input unchanged.
• FORMAT_NUMBERFormat a numeric value as a thousands-separated string with a fixed number of decimal places.
• FORMAT_STRINGFormat a string using printf-style placeholders (alias for PRINTF).
• GEN_RANDOM_UUIDGenerate a new random UUID (version 4).
• GENERATE_SERIESReturn a table of values from start to stop, incremented by step.
• GET_BITReturn the value (0 or 1) of the bit at the given position in an integer or binary value.
• IBAN_CHECKReturn true if the input string is a valid International Bank Account Number.
• IFFReturn one of two values based on a boolean condition (alias for IIF).
• IIFReturn one of two values based on a boolean condition.
• INET_CLIENT_ADDRReturn the IP address of the client that opened the current session.
• INET_SERVER_ADDRReturn the IP address of the server that accepted the current session.
• INPUT_FILE_NAMEReturn the path of the underlying data file from which the current row was read.
• IS_DATEReturn true if the input string can be parsed as a valid calendar date.
• IS_DISTINCT_FROMReturn true if the two operands differ, treating NULL as equal to NULL.
• IS_EMAILReturn true if the input string is a syntactically valid email address.
• IS_INTEGERReturn true if the input string parses as a signed integer with no fractional part.
• IS_IPV4Return true if the input string is a valid dotted-decimal IPv4 address.
• IS_IPV6Return true if the input string is a valid IPv6 address.
• IS_NOT_DISTINCT_FROMReturn true if the two operands are equal, treating NULL as equal to NULL.
• IS_NUMERICReturn true if the input string parses as a signed integer or decimal number.
• IS_UUIDReturn true if the input string is a valid UUID.
• IS_VALID_JSONReturn true if the input string is a well-formed JSON value.
• LUHN_CHECKReturn true if the input digit string passes the Luhn (mod-10) checksum.
• MASK_CREDIT_CARDRedact a credit card number for display while preserving the last four digits.
• MASK_EMAILRedact an email address for display while preserving the domain.
• MASK_PHONERedact a phone number for display while preserving the last four digits.
• MASK_SSNRedact a Social Security Number for display while preserving the last four digits.
• MONOTONICALLY_INCREASING_IDGenerate a 64-bit integer id that is unique and strictly increasing within a single query execution.
• NULLIFEMPTYReturn NULL if the input string is empty, otherwise return the input unchanged.
• NULLIFZEROReturn NULL if the input is zero, otherwise return the input unchanged.
• NUM_NONNULLSReturn the count of non-NULL arguments in a variadic argument list.
• NUM_NULLSReturn the count of NULL arguments in a variadic argument list.
• PG_BACKEND_PIDReturn the process id of the backend serving the current session.
• PG_COLUMN_SIZEReturn the number of bytes used to store a value in its internal representation.
• PG_CONF_LOAD_TIMEReturn the timestamp when the server configuration was last loaded.
• PG_INPUT_IS_VALIDReturn true if the input string can be cast to the specified data type without error.
• PG_POSTMASTER_START_TIMEReturn the timestamp at which the server process started.
• PG_SIZE_PRETTYFormat a byte count as a human-readable size string (bytes, kB, MB, GB, TB).
• PG_SLEEPPause the current session for a given number of seconds.
• PG_TYPEOFReturn the PG-compat data type name of an expression.
• PRINTFFormat a string using printf-style placeholders (alias for FORMAT_STRING).
• RANGE_INTERSECTReturn the overlapping portion of two ranges, or an empty range if they do not overlap.
• RANGE_MERGEReturn the smallest range that spans both input ranges.
• REMOVE_ACCENTSStrip diacritical marks from the input string, producing plain ASCII where possible.
• ROW_NUMBER_GENERATEGenerate a dense, 1-based row number for each row in the result set.
• SESSION_USERReturn the original authenticated user name for the current session.
• SET_BITReturn a copy of the input value with the bit at the given position set to 0 or 1.
• SET_CONFIGSet a configuration parameter for the session or transaction and return the new value.
• SHA224Compute the SHA-224 hash of a string and return it as a 56-character lowercase hexadecimal string.
• SHA256Compute the SHA-256 hash of a string and return it as a 64-character lowercase hexadecimal string.
• SHA384Compute the SHA-384 hash of a string and return it as a 96-character lowercase hexadecimal string.
• SHA512Compute the SHA-512 hash of a string and return it as a 128-character lowercase hexadecimal string.
• SLUGIFYConvert the input string to a lowercase, hyphen-separated, URL-safe slug.
• SPARK_PARTITION_IDReturn the id of the physical partition that produced the current row.
• STACKReshape a flat list of values into n rows of equal-width tuples.
• TO_CAMEL_CASEConvert the input string to lowerCamelCase.
• TO_SNAKE_CASEConvert the input string to snake_case.
• TO_TITLE_CASEConvert the input string to Title Case.
• TRY_CAST_BOOLAttempt to cast a value to BOOLEAN, returning NULL on failure instead of raising an error.
• TRY_CAST_FLOATAttempt to cast a value to FLOAT (DOUBLE), returning NULL on failure instead of raising an error.
• TRY_CAST_INTAttempt to cast a value to INT, returning NULL on failure instead of raising an error.
• TXID_CURRENTReturn the identifier of the current transaction.
• TYPEOFReturn the dialect-neutral data type name of an expression.
• UUIDGenerate a new random UUID (version 4).
• UUID_GENERATE_V4Generate a new random UUID (version 4).
• VERSIONReturn a human-readable string describing the engine version.
• ZEROIFNULLReturn 0 if the input is NULL, otherwise return the input unchanged.

Numeric

• COSDCompute the cosine of an angle supplied in degrees, returning exact values at common angles.
• DIVPerform integer division, truncating the quotient toward zero.
• FACTORIALReturn the factorial of a non-negative integer.
• GCDReturn the greatest common divisor of two integers (non-negative, using the Euclidean algorithm).
• LCMReturn the least common multiple of two integers.
• MIN_SCALEReturn the minimum number of fractional digits required to represent a numeric value exactly.
• SCALEReturn the declared scale (number of fractional digits) of a numeric value.
• SETSEEDSet the seed for the pseudo-random number generator used by RANDOM().
• SINDCompute the sine of an angle supplied in degrees, returning exact values at common angles.
• TANDCompute the tangent of an angle supplied in degrees, with exact handling at common angles.
• TO_BINConvert an integer to its binary (base-2) string representation.
• TO_NUMBERParse a string into a DECIMAL value according to a format pattern.
• TO_OCTConvert an integer to its octal (base-8) string representation.
• TRIM_SCALERemove trailing zeros from a numeric value to reduce scale to the minimum needed.
• WIDTH_BUCKETAssign a value to a bucket in an equi-width histogram spanning a given range.

Regex

• REGEXP_COUNTReturn the number of non-overlapping matches of a regular expression in the input string.
• REGEXP_INSTRReturn the 1-based character position of a regex match within the input string.
• REGEXP_LIKEReturn true when the input string contains a match for the regular expression.
• REGEXP_MATCHReturn the captured groups (or the whole match) from the first regex match as an array.
• REGEXP_MATCHESReturn capturing groups from every regex match as a set of rows (set-returning function).
• REGEXP_REPLACEReplace substrings matching a regular expression with a replacement that may reference captured groups.
• REGEXP_SPLIT_TO_ARRAYSplit the input string at every match of a regular expression and return the parts as an array.
• REGEXP_SPLIT_TO_TABLESplit the input string at every regex match and return the parts as rows (set-returning function).
• REGEXP_SUBSTRReturn the substring that matches a regular expression (scalar result, unlike REGEXP_MATCH's array).

String

• ARRAY_TO_STRINGConcatenate array elements into a string with a delimiter, optionally replacing NULL elements.
• ASCIIReturn the Unicode code point of the first character of the input string.
• BIT_LENGTHReturn the number of bits in the input string's UTF-8 encoding.
• BTRIMStrip a custom character set from both ends of the input string, defaulting to whitespace.
• CHARReturn the character corresponding to the given Unicode code point.
• CHAR_LENGTHReturn the number of characters in the input string.
• CHARACTER_LENGTHReturn the number of characters in the input string (alias of CHAR_LENGTH).
• CHRReturn the character corresponding to the given Unicode code point (alias of CHAR).
• CONCAT_WSConcatenate arguments with a separator, skipping any NULL arguments.
• CONTAINSReturn true when the input string contains the given substring, false otherwise.
• DIFFERENCEReturn a 0-4 similarity score comparing the Soundex codes of two strings.
• ENDS_WITHReturn true when the input string ends with the given suffix, false otherwise.
• ENDSWITHReturn true when the input string ends with the given suffix, false otherwise.
• FORMATBuild a formatted string by substituting arguments into a format template.
• INITCAPReturn the input string with the first letter of each word capitalised and every other letter lowercased.
• INSTRReturn the 1-based position of the first occurrence of a substring, or 0 if not found.
• IS_NORMALIZEDReturn true when the input string is already in the specified Unicode normalization form.
• LEFTReturn the leftmost n characters of the input string.
• LENGTHReturn the number of characters in the input string.
• LEVENSHTEINCompute the minimum single-character edit distance between two strings.
• LOWERReturn the input string with every character converted to its lowercase equivalent.
• LPADPad the input string on the left with a repeating pad sequence until it reaches a target length.
• LTRIMRemove leading whitespace from the input string.
• NORMALIZEConvert the input string to the specified Unicode normalization form.
• OCTET_LENGTHReturn the number of bytes in the input string's UTF-8 encoding.
• OVERLAYReplace a range of characters in a string with a replacement substring (SQL-standard PLACING/FROM/FOR syntax).
• PARSE_IDENTSplit a dot-separated qualified SQL identifier into its component parts.
• POSITIONReturn the 1-based position of the first occurrence of a substring (SQL-standard IN syntax).
• QUOTE_IDENTWrap a string in double quotes so it is safe to use as a SQL identifier in dynamic SQL.
• QUOTE_LITERALWrap a string in single quotes so it is safe to use as a SQL string literal in dynamic SQL.
• QUOTE_NULLABLEQuote a string as a SQL literal, returning the unquoted keyword NULL when the input is NULL.
• REPEATReturn the input string repeated n times.
• REPLACESubstitute every literal occurrence of a search substring with a replacement string.
• REVERSEReverse the order of characters in a string, or the order of elements in an array.
• RIGHTReturn the rightmost n characters of the input string.
• RPADPad the input string on the right with a repeating pad sequence until it reaches a target length.
• RTRIMRemove trailing whitespace from the input string.
• SOUNDEXReturn the Soundex phonetic code for a string (one uppercase letter followed by three digits).
• SPACEReturn a string consisting of n space characters.
• SPLITSplit the input string by a literal delimiter and return the parts as an array.
• SPLIT_PARTSplit the input string by a literal delimiter and return the nth part.
• STARTS_WITHReturn true when the input string begins with the given prefix, false otherwise.
• STARTSWITHReturn true when the input string begins with the given prefix, false otherwise.
• STRING_AGGAggregate non-NULL values into a delimited string across grouped rows.
• STRING_TO_ARRAYSplit the input string by a literal delimiter, optionally mapping a sentinel to NULL.
• STRPOSReturn the 1-based position of the first occurrence of a substring, or 0 if not found.
• SUBSTRExtract a contiguous slice of characters from the input string (alias of SUBSTRING).
• SUBSTRINGExtract a contiguous slice of characters from the input string by 1-based position and length.
• SUBSTRING_INDEXReturn the portion of a string before or after the nth occurrence of a delimiter.
• TO_ASCIITransliterate non-ASCII characters to their closest ASCII equivalents.
• TO_HEXReturn the lowercase hexadecimal string representation of an integer.
• TRANSLATEReplace or delete individual characters in a string using a positional character mapping.
• TRIMRemove leading and trailing whitespace from the input string.
• UPPERReturn the input string with every character converted to its uppercase equivalent.

Time Travel

• DELETEDReturns rows that existed in a Delta table at one version but not at a later version, by diffing two time-travel snapshots. One of the DELETED /...
• INSERTEDReturns rows that exist in a Delta table at one version but did not exist at an earlier version, by diffing two time-travel snapshots. Symmetric...
• UPDATEDReturns the post-image of every row whose key exists at both versions but whose non-key column values differ, by diffing two time-travel snapshots...