Parameter	Description
`expr`	any expression to match against the list of `matches`
`matches`	a comma-delimited list of constants to match `expr` against; needs to be of the same data type as `expr` and have the same number of list items as the `values` list
`values`	a comma-delimited list of constants, one of which will be returned in the event that `expr` matches an item in the `matches` list; needs to be of the same data type as `value\_if\_no\_match` and have the same number of list items as the `matches` list
`value\_if\_no match`	any value to return in the event of a match; needs to be of the same data type as the list items in `values`

Examples:

Function Call	Result
`CASE(0, \{1, 2, 3}, \{'A', 'B', 'C'}, 'D')`	`D`
`CASE(1, \{1, 2, 3}, \{'A', 'B', 'C'}, 'D')`	`A`
`CASE(2, \{1, 2, 3}, \{'A', 'B', 'C'}, 'D')`	`B`
`CASE(3, \{1, 2, 3}, \{'A', 'B', 'C'}, 'D')`	`C`
`CASE(4, \{1, 2, 3}, \{'A', 'B', 'C'}, 'D')`	`D`

Evaluates `expr`: if *true*, returns `value_if_true`; otherwise, if *false* or *null*, returns `value_if_false`; see [Short-Circuiting](/content/concepts/expressions#short-circuiting) for error-checking details

Parameter	Description
`expr`	any true/false condition When an integer column is used directly, this function will interpret non-zero values as true and zero values as false.
`value\_if\_true`	any type; must be the same type as `value\_if\_false`
`value\_if\_false`	any type; must be the same type as `value\_if\_true`

Style	Format
`0`	`mon dd yyyy hh:mi\`
`21` `121`	`yyyy-mm-dd hh:mi:ss.mmm`

Examples:

Function Call	Result
`CONVERT('0.1', double)`	`0.1`
`CONVERT(DATETIME('2000-10-10 12:34:56'), string, 0)`	`Oct 10 2000 12:34PM`
`CONVERT(DATETIME(971181296789), string, 21)`	`2000-10-10 12:34:56.789`

Converts `expr` to date (`YYYY-MM-DD`) format Converts `expr` to datetime (`YYYY-MM-DD HH24:MI:SS.mmm`) format Converts the given `expr` to *decimal* type Converts the given `expr` to *double* type Converts the given `expr` to *float* type Converts the given `expr` to *int* type Converts the given `expr` to *long* type Converts `expr` to a string format appropriate for the `expr` type Converts `expr` to time (`HH:MI:SS.mmm`) format Converts `expr` to the number of milliseconds since the epoch Converts the given date/time `expr` to a string matching the given `format`. The returned string will be truncated at 32 characters. See [Date/Time Conversion Codes](/content/concepts/expressions#datetime-conversion-codes) for the list of format codes. Example:

Function Call	`TO\_CHAR(DATETIME(971181296000), '"Last login: "YYYY-MM-DD')`
Return	`Last login: 2000-10-10`

Constant	Description
`YEAR`	Year is modified by interval amount (not affected by leap year, etc.) overflow/underflow occurs
`QUARTER`	Month is modified by three times the interval amount, irrespective of the number of days in the months between; day adjusting performed the same as the `MONTH` description, but only on final month (e.g., Jan 31st + 1 quarter will be Apr 30th, not Apr 28th because of February)
`MONTH`	Month is modified by interval amount and date adjusted if overflow/underflow occurs; day adjusted to last day of calculated month if not a valid day for that month (e.g., Apr 31st -> Apr 30th)
`WEEK`	Day is modified by 7 times the interval amount (time not affected by daylight savings time, etc.); month & year are adjusted, if overflow/underflow occurs
`DAY`	Day is modified by interval amount (time not affected by daylight savings time, etc.); date is adjusted, if overflow/underflow occurs
`HOUR`	Hours are modified by interval amount (time not affected by daylight savings time, etc.); date is adjusted, if overflow/underflow occurs
`MINUTE`	Minutes are modified by interval amount; date/time are adjusted, if overflow/underflow occurs
`SECOND`	Seconds are modified by interval amount; date/time are adjusted, if overflow/underflow occurs
`MILLISECOND`	Milliseconds are modified by interval amount; date/time are adjusted, if overflow/underflow occurs

Any of these `unit` types can have a `SQL_TSI_` prefix prepended to them; e.g., both `DAY` and `SQL_TSI_DAY` are valid `unit` types for specifying a day interval. They may also be single-quoted or unquoted. Examples:

Function Call	Result (in string format)
`DATEADD(YEAR, 1, '2000-10-10')`	`2001-10-10 00:00:00.000`
`DATEADD(QUARTER, 1, '2000-11-30')`	`2001-02-28 00:00:00.000`
`DATEADD(MONTH, 1, '2000-01-31')`	`2000-02-29 00:00:00.000`
`DATEADD(WEEK, 53, '2000-01-01')`	`2001-01-06 00:00:00.000`
`DATEADD(DAY, 1, '2000-12-31')`	`2001-01-01 00:00:00.000`
`DATEADD(HOUR, 12, '2000-10-10 12:34:56')`	`2000-10-11 00:34:56.000`
`DATEADD(MINUTE, 1, '2000-10-10 12:34:56')`	`2000-10-10 12:35:56.000`
`DATEADD(SECOND, 1, '2000-12-31 23:59:59')`	`2001-01-01 00:00:00.000`
`DATEADD(MILLISECOND, 1, '2000-10-10 12:34:56')`	`2000-10-10 12:34:56.001`

Calculates the date range in which a given date `ds` falls, based on a set of fixed-width "buckets" with the given `width`, start-aligned `base` date, and `offset` from that `base` date The `width` is the number of days each bucket should span. The `offset` is the number of days after (positive `offset`) or number of days before (negative `offset`) the `base` date to which the buckets should be aligned. The default is no offset. The default `base` is `2000-01-03`. For example, given the following call: ``` DATE_BUCKET(7, ds, -3, '2023-02-21') ``` The call will be processed as follows: * The given date `ds` will be slotted into a date bucket * The bucket will span a range of `7` days * The baseline bucket will start at `2023-02-18` (`2023-02-21` offset by `-3` days) and continue through `2023-02-24` (`7` days, including `2023-02-18`) * Buckets will extend before & after the baseline bucket in contiguous, non-overlapping fashion * The result record will show the date at the beginning of the bucket's date range Calculates the difference between two date/time expressions, returning the result as an integral difference in the units specified; more precisely, how many whole date/time intervals of type `unit` need to be added to (or subtracted from) `begin` to equal `end` (or get as close as possible **without** going past it) using the unit types and the rules specified in `TIMESTAMPADD`. The default `unit` is `DAY`. Examples:

Function Call	Result
`DATEDIFF('2000-10-10', '2000-12-31')`	`82`
`DATEDIFF('2000-03-31', '2000-04-30')`	`30`
`DATEDIFF('2000-12-31', '2000-10-10')`	`-82`
`DATEDIFF('2000-10-10 12:34:56.789', 978222896678)`	`81`
`DATEDIFF(MONTH, '2000-10-10', '2000-12-31')`	`2`
`DATEDIFF(MONTH, '2000-03-31', '2000-04-30')`	`0`
`DATEDIFF(MONTH, '2000-12-31', '2000-10-10')`	`-2`
`DATEDIFF(HOUR, '2000-10-10 12:34:56.789', 978222896678)`	`1955`

Returns the requested `part` of the date/time `expr`. The `part` may be any of those specified in `DATE_TRUNC(part, expr)`, plus the following:

Constant	Description
`SECS\_SINCE\_EPOCH`	Return the number of seconds since the epoch (1970-01-01).
`MSECS\_SINCE\_EPOCH`	Return the number of milliseconds since the epoch.

Examples:

Function Call	Result
`DATEPART(YEAR, '2000-12-31')`	`2000`
`DATEPART(MONTH, '2000-03-31 12:34:56.000')`	`3`
`DATEPART(DAY, '2000-03-31 12:34:56.000')`	`31`
`DATEPART(SECOND, '2000-03-31 12:34:56.220')`	`56`
`DATEPART(MSECS\_SINCE\_EPOCH, '2000-03-31 12:34:56.220')`	`954506096220`

Returns the date/time `expr` after truncating it beyond the given date/time `part`. The following date/time constants are supported for `part`:

Constant	Description
`YEAR`	Return the first day of the year in which `expr` occurs
`QUARTER`	Return the first day of the quarter in which `expr` occurs
`MONTH`	Return the first day of the month in which `expr` occurs
`WEEK`	Return the first day of the week in which `expr` occurs
`DAY`	Return the date (at midnight) on which `expr` occurs
`HOUR`	Return the timestamp up to the hour in which `expr` occurs
`MINUTE`	Return the timestamp up to the minute in which `expr` occurs
`SECOND`	Return the timestamp up to the second in which `expr` occurs
`MILLISECOND`	Return the timestamp up to the millisecond in which `expr` occurs

Examples:

Function Call	Result
`DATE\_TRUNC(YEAR, '2008-09-10 12:34:56.789')`	`2008-01-01 00:00:00.000`
`DATE\_TRUNC(QUARTER, '2008-09-10 12:34:56.789')`	`2008-07-01 00:00:00.000`
`DATE\_TRUNC(MONTH, '2008-09-10 12:34:56.789')`	`2008-09-01 00:00:00.000`
`DATE\_TRUNC(WEEK, '2008-09-10 12:34:56.789')`	`2008-09-07 00:00:00.000`
`DATE\_TRUNC(DAY, '2008-09-10 12:34:56.789')`	`2008-09-10 00:00:00.000`
`DATE\_TRUNC(HOUR, '2008-09-10 12:34:56.789')`	`2008-09-10 12:00:00.000`
`DATE\_TRUNC(MINUTE, '2008-09-10 12:34:56.789')`	`2008-09-10 12:34:00.000`
`DATE\_TRUNC(SECOND, '2008-09-10 12:34:56.789')`	`2008-09-10 12:34:56.000`
`DATE\_TRUNC(MILLISECOND, '2008-09-10 12:34:56.789')`	`2008-09-10 12:34:56.789`

Alias for `DAYOFMONTH(expr)` Extracts the day of the week from `expr` and converts it to the corresponding day name \[`Sunday` - `Saturday` ] Extracts the day of the month from `expr` \[`1` - `31`] Extracts the day of the week from `expr` \[`1` - `7`]

Expression Value	Result
Date on Sunday	`1`
Date on Monday	`2`
Date on Tuesday	`3`
Date on Wednesday	`4`
Date on Thursday	`5`
Date on Friday	`6`
Date on Saturday	`7`

Alias for `DAYOFWEEK(expr)` Extracts the day of the year from `expr` \[`1` - `366`] Alias for `DAYOFYEAR(expr)` Converts `expr` milliseconds since the epoch to a date/time Example:

Function Call	`EPOCH\_MSECS\_TO\_DATETIME(971181296789)`
Return	`2000-10-10 12:34:56.789`

Converts `expr` seconds since the epoch to a date/time Example:

Function Call	`EPOCH\_SECS\_TO\_DATETIME(971181296)`
Return	`2000-10-10 12:34:56.000`

Extracts the hour of the day from `expr` \[`0` - `23`] Returns the date of the last day of the month in the given `expr` Extracts the minute of the day from `expr` \[`0` - `59`] Extracts the month of the year from `expr` \[`1` - `12`] Extracts the month of the year from `expr` and converts it to the corresponding month name \[ `January` - `December`] Extracts the millisecond of the second from `expr` \[`0` - `999`] Converts `expr` date/time to the number of milliseconds since the epoch Example:

Function Call	`MSECS\_SINCE\_EPOCH('2000-10-10 12:34:56.789')`
Return	`971181296789`

Returns the date of the next day of the week, provided as a day name in `expr`, that occurs after the given `date` Some examples, given that *2000-10-10* is a *Tuesday*:

Function Call	Result
`NEXT\_DAY('2000-10-10', 'Wednesday')`	`2000-10-11`
`NEXT\_DAY('2000-10-10', 'Friday')`	`2000-10-13`
`NEXT\_DAY('2000-10-10', 'Tuesday')`	`2000-10-17`

Alias for `CURRENT_DATETIME()` Extracts the quarter of the year from `expr` \[`1` - `4`]

Expression Value	Result
Date in January, February, or March	`1`
Date in April, May, or June	`2`
Date in July, August, or September	`3`
Date in October, November, or December	`4`

Alias for `SECOND(expr)` Extracts the seconds of the minute from `expr` \[ `0` - `59` ] Converts `expr` date/time to the number of seconds since the epoch Example:

Function Call	`SECS\_SINCE\_EPOCH('2000-10-10 12:34:56')`
Return	`971181296`

Pause execution for at least `expr` seconds, though system load may delay the return from this call for longer than the specified amount. Use a decimal for `expr` to pause for less than a second; e.g., `SLEEP(0.001)` will pause for at least 1 millisecond. `SLEEP` should be invoked without a table reference in the call to avoid being called for every record in a result set; e.g., in Python: ``` db.get_records_by_column('', 'SLEEP(0.001)') ``` Calculates the difference between two date/time expressions, returning the result as an integral difference in the units specified; more precisely, how many whole date/time intervals of type `unit` need to be added to (or subtracted from) `begin` to equal `end` *up to the precision of the* `unit` *specified*, using the unit types and rules specified in `TIMESTAMPADD`. For example, if `unit` were `MONTH`, only the year & month of `begin` and `end` would be used in the calculation; if `unit` were `DAY`, any time portion from `begin` & `end` would be dropped, and so on. This is unlike `TIMESTAMPDIFF`, which will consider the entirety of both `begin` & `end` in the calculation. This *is* symmetric with `TIMESTAMPADD` in all cases, as adding *1* `MONTH` to *Mar 31st* results in *Apr 30th*, and the `TIMEBOUNDARYDIFF` in `MONTH` units between those two dates is *1*. Examples:

Function Call	Result
`TIMEBOUNDARYDIFF(MONTH, DATE('2000-10-10'), DATE('2000-12-31'))`	`2`
`TIMEBOUNDARYDIFF(MONTH, DATE('2000-03-31'), DATE('2000-04-30'))`	`1`
`TIMEBOUNDARYDIFF(MONTH, DATE('2000-12-31'), DATE('2000-10-10'))`	`-2`
`TIMEBOUNDARYDIFF(HOUR, 978222896000, DATETIME('2000-10-10 12:34:56'))`	`-1956`

This function does not work with string literal date stamps (e.g., `2000-12-31 12:34:56`); to use string literals in this function, first cast them to the appropriate date/time type (e.g., `DATETIME('YYYY-MM-DD HH24:MI:SS')`) Calculates the date/time range in which a given timestamp `ts` falls, based on a set of fixed-width "buckets" with the given `width`, start-aligned `base` date/time, and `offset` from that `base` date/time The `width` is the number of milliseconds each bucket should span. The `offset` is the number of milliseconds after (positive `offset`) or number of milliseconds before (negative `offset`) the `base` date/time to which the buckets should be aligned. The default is no offset. The default `base` is `2000-01-03 00:00:00`. For example, given the following call: ``` TIME_BUCKET(5 * 60 * 1000, ts, -2.5 * 60 * 1000, '2023-02-28') ``` The call will be processed as follows: * The given timestamp `ts` will be slotted into a date/time bucket * The bucket will span a range of `5` minutes * The baseline bucket will start at `2023-02-27 23:57:30` (`2023-02-28` offset by `-2.5` minutes) and continue through `2023-02-28 00:02:30` (`5` minutes from `2023-02-27 23:57:30`) * Buckets will extend before & after the baseline bucket in contiguous, non-overlapping fashion * The result record will show the timestamp at the beginning of the bucket's range Adds the positive or negative integral `amount` of `unit` date/time intervals to the `date`, `datetime`, or `time` value in `expr` The following date/time intervals are supported for `unit`:

Constant	Description
`YEAR`	Year is modified by interval amount (not affected by leap year, etc.) overflow/underflow occurs
`QUARTER`	Month is modified by three times the interval amount, irrespective of the number of days in the months between; day adjusting performed the same as the `MONTH` description, but only on final month (e.g., Jan 31st + 1 quarter will be Apr 30th, not Apr 28th because of February)
`MONTH`	Month is modified by interval amount and date adjusted if overflow/underflow occurs; day adjusted to last day of calculated month if not a valid day for that month (e.g., Apr 31st -> Apr 30th)
`WEEK`	Day is modified by 7 times the interval amount (time not affected by daylight savings time, etc.); month & year are adjusted, if overflow/underflow occurs
`DAY`	Day is modified by interval amount (time not affected by daylight savings time, etc.); date is adjusted, if overflow/underflow occurs
`HOUR`	Hours are modified by interval amount (time not affected by daylight savings time, etc.); date is adjusted, if overflow/underflow occurs
`MINUTE`	Minutes are modified by interval amount; date/time are adjusted, if overflow/underflow occurs
`SECOND`	Seconds are modified by interval amount; date/time are adjusted, if overflow/underflow occurs
`MILLISECOND`	Milliseconds are modified by interval amount; date/time are adjusted, if overflow/underflow occurs

Function Call	Result (in string format)
`TIMESTAMPADD(YEAR, 1, '2000-10-10')`	`2001-10-10`
`TIMESTAMPADD(QUARTER, 1, '2000-11-30')`	`2001-02-28`
`TIMESTAMPADD(MONTH, 1, '2000-01-31')`	`2000-02-29`
`TIMESTAMPADD(WEEK, 53, '2000-01-01')`	`2001-01-06`
`TIMESTAMPADD(DAY, 1, '2000-12-31')`	`2001-01-01`
`TIMESTAMPADD(HOUR, 12, '2000-10-10 12:34:56')`	`2000-10-11 00:34:56.000`
`TIMESTAMPADD(MINUTE, 1, '2000-10-10 12:34:56')`	`2000-10-10 12:35:56.000`
`TIMESTAMPADD(SECOND, 1, '2000-12-31 23:59:59')`	`2001-01-01 00:00:00.000`
`TIMESTAMPADD(MILLISECOND, 1, '2000-10-10 12:34:56')`	`2000-10-10 12:34:56.001`
`TIMESTAMPADD(SECOND, 1, TIME('12:34:56'))`	`12:34:57.000`

Calculates the difference between two date/time expressions, returning the result as an integral difference in the units specified; more precisely, how many whole date/time intervals of type `unit` need to be added to (or subtracted from) `begin` to equal `end` (or get as close as possible **without** going past it) using the unit types and and rules specified in `TIMESTAMPADD`. Unlike `TIMEBOUNDARYDIFF`, all date/time components of both `begin` & `end` will be considered in the calculation, not just those that are up to the precision of `unit`. This *is not* symmetric with `TIMESTAMPADD` in all cases, as adding *1* `MONTH` to *Mar 31st* results in *Apr 30th*, but the `TIMESTAMPDIFF` in `MONTH` units between those two dates is *0*. Examples:

Function Call	Result
`TIMESTAMPDIFF(MONTH, DATETIME('2000-10-10 01:23:45.678'), DATETIME('2000-12-31 12:34:56.789'))`	`2`
`TIMESTAMPDIFF(MONTH, DATETIME('2000-03-31 01:23:45.678'), DATETIME('2000-04-30 12:34:56.789'))`	`0`
`TIMESTAMPDIFF(MONTH, DATETIME('2000-12-31 01:23:45.678'), DATETIME('2000-10-10 12:34:56.789'))`	`-2`
`TIMESTAMPDIFF(HOUR, DATETIME('2000-10-10 12:34:56.789'), TIMESTAMP(978222896678))`	`1955`

Alias for `DATE_TRUNC(part, expr)` Alias for `SECS_SINCE_EPOCH(expr)` Extracts the week of the year from `expr` \[`1` - `54`]; each full week starts on *Sunday* (A `1` is returned for the week containing *Jan 1st*) Extracts the year from `expr`; 4-digit year, A.D. #### Date/Time Complex Conversion Functions Converts the full date to the number of milliseconds since the epoch Example:

Function Call	`DATE\_TO\_EPOCH\_MSECS(2017, 06, 15, 09, 22, 15, 42)`
Return	`1497518535042`
Resolves To	Thursday, June 15, 2017 9:22:15.042 AM

Converts the full date to the number of seconds since the epoch Example:

Function Call	`DATE\_TO\_EPOCH\_SECS(2017, 06, 15, 09, 22, 15)`
Return	`1494926535`
Resolves To	Thursday, June 15, 2017 9:22:15.042 AM

Converts the given date and time to a composite timestamp in milliseconds since the epoch Example:

Function Call	`TIMESTAMP\_FROM\_DATE\_TIME('2017-06-15', '10:37:30')`
Return	`1497523050000`

Converts the year and week number to the number of milliseconds since the epoch; negative values are accepted Example:

Function Call	`WEEK\_TO\_EPOCH\_MSECS(2017,-32)`
Return	`1463270400000`
Resolves To	Sunday, May 15, 2016 12:00:00 AM

Converts the year and week number to the number of seconds since the epoch. Negative values are accepted. Each new week begins Sunday at midnight. Example:

Function Call	`WEEK\_TO\_EPOCH\_SECS(2017,-32)`
Return	`1463270400`
Resolves To	Sunday, May 15, 2016 12:00:00 AM

Function Call	`ST\_ISVALIDREASON('POLYGON((-1 0, 1 0, 1 1, -1 -1))')`
Return	`Self-intersection\[0 0]`

Returns the length of the geometry if it is a LINESTRING or MULTILINESTRING. Returns `0` if another type of geometry, e.g., POINT, MULTIPOINT, etc. GEOMETRYCOLLECTIONs are also supported but the aforementioned type limitation still applies; the collection will be recursively searched for LINESTRINGs and MULTILINESTRINGs and the summation of all supported geometry types is returned (unsupported types are ignored). Solution types available: * `0` (default) - 2D Euclidean length * `1` - length on a sphere in meters * `2` - length on a spheroid in meters Creates a LINESTRING from `geom` if it is a MULTIPOINT. Returns `null` if `geom` is not a MULTIPOINT Returns a POINT on the LINESTRING `geom` that is the `frac` fraction of the distance along the line. If `geom` is either empty or **not** a LINESTRING, `null` is returned Returns the location of the closest point in the given `linestring` to the given `point` as a value between `0` and `1`. The return value is a fraction of the total `linestring` length. Returns a LINESTRING or MULTILINESTRING from a given `geom`. If `geom` is a MULTILINESTRING comprising LINESTRINGs with shared endpoints, a contiguous LINESTRING is returned. If `geom` is a LINESTRING or a MULTILINESTRING comprising LINESTRINGS without shared endpoints, `geom` is returned If `geom` is an empty (MULTI)LINESTRING or a (MULTI)POINT or (MULTI)POLYGON, an empty GEOMETRYCOLLECTION is returned. Returns the fraction of a given `geom` LINESTRING from the point that is the `start_frac` fraction of the distance along the line to the point that is the `end_frac` fraction of the distance along the line. For example, given `LINESTRING(1 1, 2 2, 3 3)` a `start_fraction` of `0` and an `end_fraction` of `0.25` would yield the first quarter of the given LINESTRING, or `LINESTRING(1 1, 1.5 1.5)`. Returns `null` in the following cases: * input geometry is (MULTI)POINT, MULTILINESTRING, or (MULTI)POLYGON * `start_frac` is greater than `end_frac` * `start_frac` or `end_frac` are not between `0` & `1`, inclusive Returns the LINESTRING that represents the longest line of points between the two geometries. If multiple longest lines are found, only the first line found is returned. If `geom1` or `geom2` is empty, `null` is returned. Solution types available: * `0` (default) - Euclidean; uses degrees to calculate the longest line * `1` - Sphere; uses meters to calculate the longest line * `2` - Spheroid; uses meters to calculate the longest line, more accurate than sphere but slower performance Creates a rectangular POLYGON from the given min and max parameters Creates a LINESTRING from `geom` if it is a MULTIPOINT. If `geom` is a POINT, there must be at least one other POINT to construct a LINESTRING. If `geom` is a LINESTRING, it must have at least two points. Returns `null` if `geom` is not a POINT, MULTIPOINT, or LINESTRING This function can be rather costly in terms of performance Creates a POINT at the given coordinate This function can be rather costly in terms of performance Creates a POLYGON from `geom`. Inputs must be closed LINESTRINGs This function can be rather costly in terms of performance Creates a closed 2-D POLYGON with three vertices Creates a closed 3-D POLYGON with three vertices Attempts to convert `geom` into a valid geometry when it is malformed, as determined by `ST_ISVALID(geom)`. Returns `geom` if it is a valid geometry already. The method used to convert invalid geometries into valid ones can be specified in `options` as a space-separated string of `x=y` key/value pairs. The keys and corresponding values are as follows: * `method` - the algorithm used to convert invalid geometries into valid ones; either: * `linework` (default) - build geometry from lines extracted from `geom` * `structure` - build geometry from interior & exterior rings extracted from `geom` * `keepcollapsed` - if using the `method` of `structure`, whether to drop portions of the converted geometry that collapse to lower dimensions: * `true` (default) - keep portions of geometry that collapse to lower dimensions * `false` - don't keep portions of geometry that collapse to lower dimensions Example using default *linework* method:

Function Call	`ST\_MAKEVALID('POLYGON((-1 0, 1 0, 1 1, -1 -1))')`
Return	`MULTIPOLYGON (((-1 -1, -1 0, 0 0, -1 -1)), ((1 0, 0 0, 1 1, 1 0)))`

Example using the *structure* method without dropping collapsible parts of the converted geometry:

Function Call	`ST\_MAKEVALID('POLYGON((0 0, 0 0, 0 0, 0 0))', 'method=structure keepcollapsed=true')`
Return	`POINT (0 0)`

Example using the *structure* method with dropping collapsible parts of the converted geometry:

Function Call	`ST\_MAKEVALID('POLYGON((0 0, 0 0, 0 0, 0 0))', 'method=structure keepcollapsed=false')`
Return	`POLYGON EMPTY`

Returns the maximum distance between the given `geom1` and `geom2` geometries using the specified solution type. If `geom1` or `geom2` is empty, `null` is returned. Solution types available: * `0` (default) - returns maximum 2-D Euclidean distance * `1` - Sphere; returns maximum distance in meters * `2` - Spheroid; returns maximum distance in meters, more accurate than sphere but slower performance Returns the maximum x coordinate of a bounding box for the given `geom` geometry. This function works for 2-D and 3-D geometries. Returns the maximum y coordinate of a bounding box for the given `geom` geometry. This function works for 2-D and 3-D geometries. Returns the maximum z coordinate of a bounding box for the given `geom` geometry. This function works for 2-D and 3-D geometries. Returns the minimum x coordinate of a bounding box for the given `geom` geometry. This function works for 2-D and 3-D geometries. Returns the minimum y coordinate of a bounding box for the given `geom` geometry. This function works for 2-D and 3-D geometries. Returns the minimum z coordinate of a bounding box for the given `geom` geometry. This function works for 2-D and 3-D geometries. Returns `geom` as a MULTI- geometry, e.g., a POINT would return a MULTIPOINT. Creates multiple buffers at specified `distance` around the given `geom` geometry. Multiple distances are specified as comma-separated values in an array, e.g., `[10,20,30]`. Valid values for `outside` are: * `FULL` - indicates that buffers will overlap or cover the given `geom` geometry. This is the default. * `OUTSIDE_ONLY` - indicates that buffers will be rings around the given `geom` geometry. Returns the number of dimensions in `geom`. For X,Y data, this will return 2; if a Z component is present, it will return 3. Returns the portion of `geom2` that is closest to `geom1`. If `geom2` is a singular geometry object (e.g., POINT, LINESTRING, POLYGON), `geom2` will be returned. If `geom2` a multi-geometry, e.g., MULTIPOINT, MULTILINESTRING, etc., the nearest singular geometry in `geom2` will be returned. Returns `geom` in its normalized (canonical) form, which may rearrange the points in lexicographical order. Returns the number of points (vertices) in `geom`. Returns the total number of rings (including interior rings) in `geom`. For non-polygonal geometries, it will return 0. For MULTIPOLYGONs, it will return the total number of rings across all components. If `geom` is a collection or MULTI- geometry, returns the number of geometries. If `geom` is a single geometry, returns 1. Returns the number of interior rings if `geom` is a POLYGON. Returns `null` if `geom` is anything else. Returns the number of points in the `geom` LINESTRING. Returns `null` if `geom` is not a LINESTRING. Returns `1` (true) if given geometries `geom1` and `geom2` share space. If `geom1` and/or `geom2` are a GEOMETRYCOLLECTION, a `0` is returned regardless of if the two geometries overlap Returns a MULTIPOLYGON representing the given `geom` partitioned into a number of POLYGONs with a maximum number of vertices equal to the given `threshold`. Minimum value for `threshold` is `10`; default value is `10000`. If `geom` is not a POLYGON or MULTIPOLYGON, `geom` is returned. If the number of vertices in `geom` is less than the `threshold`, `geom` is returned. Returns the perimeter of the geometry if it is a POLYGON or MULTIPOLYGON. Returns `0` if another type of geometry, e.g., POINT, MULTIPOINT, LINESTRING, or MULTILINESTRING. GEOMETRYCOLLECTIONs are also supported but the aforementioned type limitation still applies; the collection will be recursively searched for POLYGONs and MULTIPOLYGONs and the summation of all supported geometry types is returned (unsupported types are ignored). Solution types available: * `0` (default) - 2D Euclidean length * `1` - length on a sphere in meters * `2` - length on a spheroid in meters Returns a POINT with the given `x` and `y` coordinates. Returns a POINT using the given `geohash` with a precision set by the integer `precision`. If `precision` is specified, the function will use as many characters in the hash equal to `precision` to create the geometry. If no `precision` is specified, the full length of the `geohash` is used. The POINT returned represents the center of the bounding box of the geohash Creates a MULTIPOLYGON containing a square-shaped grid of points between given minimum and maximum points of a bounding box. The minimum point cannot be greater than or equal to the maximum point. The distance between the points (in meters) is determined by `cell_side`. The `cell_side` cannot be greater than the width or height of the bounding box. The maximum number of cells that can be produced is determined by `limit`, a positive integer. Supported values for `limit`: * `-1` - No limit to the number of cells generated (effectively limited by system memory) * `0` (default) - 100 million cells * `` - Custom limit of `n` cells If the custom limit request specifies more cells (based on the bounding box and the `cell_side`) than the system limit, a `null` is returned. Returns the `n`-th point in LINESTRING `geom`. Negative values are valid, but note that they are counted backwards from the end of `geom`. A `null` is returned if `geom` is not a LINESTRING. Returns a MULTIPOINT containing all of the coordinates of `geom`. Returns a POINT projected from a start point `geom` along a geodesic calculated using `distance` and `azimuth`. If `geom` is **not** a POINT, `null` is returned. Remove a point from LINESTRING `geom` using `offset` to skip over POINTs in the LINESTRING. The `offset` is 0-based. Removes points from `geom` if the point's vertices are greater than or equal to the `tolerance` of the previous point in the geometry's list. If `geom` is not a MULTIPOINT, MULTILINESTRING, or a MULTIPOLYGON, no points will be removed. Return the geometry with its coordinate order reversed. Rotates `geom` counter-clockwise by `radians` radians. Optionally, the rotation origin can be provided as a WKT POINT WKT POINT (`wkt`). If not provided, `geom` will be rotated around *(0, 0)*. Rotates `geom` counter-clockwise by `radians` radians. Optionally, the rotation origin can be provided as a coordinate pair (`x` & `y`). If not provided, `geom` will be rotated around *(0, 0)*. Scales `geom` by multiplying its respective vertices by the corresponding *x*, *y* values in the given WKT POINT.

Function Call	`ST\_SCALE('POLYGON((1 2, -2 1, -1 -2, 2 -1, 1 2))', GEOMETRY('POINT(3 5)'))`
Return	`POLYGON ((3 10, -6 5, -3 -10, 6 -5, 3 10))`

Scales `geom` by multiplying its respective vertices by the given `x` & `y` values.

Function Call	`ST\_SCALE('POLYGON((1 2, -2 1, -1 -2, 2 -1, 1 2))', 3, 5)`
Return	`POLYGON ((3 10, -6 5, -3 -10, 6 -5, 3 10))`

Returns the given `geom`, but segmentized *n* number of times depending on how the `max_segment_size` distance (in units based on the `solution` type) divides up the original geometry. The new `geom` is guaranteed to have segments that are smaller than the given `max_segment_size`. Note that POINTs are not able to be segmentized. Collection geometries (GEOMETRYCOLLECTION, MULTILINESTRING, MULTIPOINT, etc.) can be segmentized, but only the individual parts will be segmentized, not the collection as a whole. Solution types available: * `0` - Euclidean; uses degrees to calculate distance * `1` (default) - Sphere; uses meters to calculate distance Replace a point of LINESTRING `geom1` with POINT `geom2` at `position` (base 0). Negative values are valid, but note that they are counted backwards from the end of `geom`. Returns a collection containing paths shared by `geom1` and `geom2`. Returns the 2-D LINESTRING that represents the shortest line of points between the two geometries. If multiple shortest lines are found, only the first line found is returned. If `geom1` or `geom2` is empty, `null` is returned Returns a simplified version of the given `geom` using an algorithm to reduce the number of points comprising a given geometry while attempting to best retain the original shape. The given `tolerance` determines how much to simplify the geometry. The higher the `tolerance`, the more simplified the returned geometry. Some holes might be removed and some invalid polygons (e.g., self-intersecting, etc.) might be present in the returned geometry. Only (MULTI)LINESTRINGs and (MULTI)POLYGONs can be simplified, including those found within GEOMETRYCOLLECTIONs; any other geometry objects will be returned unsimplified. The `tolerance` should be provided in the same units as the data. As a rule of thumb, a `tolerance` of `0.00001` would correspond to about one meter. Returns a simplified version of the given `geom` using an algorithm to reduce the number of points comprising a given geometry while attempting to best retain the original shape. The given `tolerance` determines how much to simplify the geometry. The higher the `tolerance`, the more simplified the returned geometry. No holes will be removed and no invalid polygons (e.g., self-intersecting, etc.) will be present in the returned geometry. Only (MULTI)LINESTRINGs and (MULTI)POLYGONs can be simplified, including those found within GEOMETRYCOLLECTIONs; any other geometry objects will be returned unsimplified. The `tolerance` should be provided in the same units as the data. As a rule of thumb, a `tolerance` of `0.00001` would correspond to about one meter. Snaps `geom1` to `geom2` within the given `tolerance`. If the `tolerance` causes `geom1` to not snap, the geometries will be returned unchanged. Returns a collection of geometries resulting from the split between `geom1` and `geom2` geometries. Creates a MULTIPOLYGON containing a grid of squares between given minimum and maximum points of a bounding box. The minimum point cannot be greater than or equal to the maximum point. The size (in meters) of the individual squares' sides is determined by `cell_side`. The `cell_side` cannot be greater than the width or height of the bounding box. The maximum number of cells that can be produced is determined by `limit`, a positive integer. Supported values for `limit`: * `-1` - No limit to the number of cells generated (effectively limited by system memory) * `0` (default) - 100 million cells * `` - Custom limit of `n` cells If the custom limit request specifies more cells (based on the bounding box and the `cell_side`) than the system limit, a `null` is returned. Returns the first point of LINESTRING `geom` as a POINT. Returns `null` if `geom` is not a LINESTRING. Returns a geometry that represents the portions of `geom1` and `geom2` geometries that do not intersect. Returns `1` (true) if the given geometries, `geom1` and `geom2`, have at least one point in common but their interiors do not intersect. If `geom1` and/or `geom2` are a GEOMETRYCOLLECTION, a `0` is returned regardless of if the two geometries touch Translate `geom` by given offsets `deltax` and `deltay`. A z-coordinate offset can be applied using `deltaz`. intersect. Creates a MULTIPOLYGON containing a grid of triangles between given minimum and maximum points of a bounding box. The minimum point cannot be greater than or equal to the maximum point. The size (in meters) of the individual triangles' sides is determined by `cell_side`. The `cell_side` cannot be greater than the width or height of the bounding box. The maximum number of cells that can be produced is determined by `limit`, a positive integer. Supported values for `limit`: * `-1` - No limit to the number of cells generated (effectively limited by system memory) * `0` (default) - 100 million cells * `` - Custom limit of `n` cells If the custom limit request specifies more cells (based on the bounding box and the `cell_side`) than the system limit, a `null` is returned. Returns a geometry that represents the point set union of the two given geometries, `geom1` and `geom2`. Returns a geometry that represents the point set union of a single given geometry `geom`. Returns a geometry that is `geom1` geometry updated by `geom2` geometry Returns a GEOMETRYCOLLECTION containing Voronoi polygons (regions consisting of points closer to a vertex in `geom` than any other vertices in `geom`) calculated from the vertices in `geom` and the given `tolerance`. The `tolerance` determines the distance at which points will be considered the same. An empty GEOMETRYCOLLECTION is returned if `geom` is an empty geometry, a single POINT, or a LINESTRING or POLYGON composed of equivalent vertices (e.g., `POLYGON((0 0, 0 0, 0 0, 0 0))`, `LINESTRING(0 0, 0 0)`). If no `tolerance` is specified, no vertices will be considered the same; each will have its own polygon. The bounding box for the result POLYGONs extends past the four edges of the input `geom` bounding box by an amount that is the greater of the input bounding box's height and width. For instance, an input `geom` with a *3* x *4* bounding box will result in Voronoi polygons filling a space that is *11* x *12*. Returns `1` (true) if the `geom1` geometry is inside the `geom2` geometry. Note that as long as at least one point is inside of `geom2`, `geom1` is considered within `geom2` even if the rest of the `geom1` lies along the boundary of `geom2` Returns the text form (WKT) of a geometry from the given byte form (WKB) Returns the byte form (WKB) of a geometry from the given text form (WKT) Returns the X coordinate of the POINT `geom`; if the coordinate is not available, `null` is returned. `geom` must be a POINT. Alias for `ST_MAXX()` Alias for `ST_MINX()` Returns the Y coordinate of the POINT `geom`; if the coordinate is not available, `null` is returned. `geom` must be a POINT. Alias for `ST_MAXY()` Alias for `ST_MINY()` Alias for `ST_MAXZ()` Alias for `ST_MINZ()` #### Aggregation Functions The following functions can be used on geospatial/geometry columns within [aggregations](/content/concepts/expressions#aggregate-expressions-label). Alias for `ST_COLLECT_AGGREGATE()` Alias for `ST_INTERSECTION_AGGREGATE()` Returns a GEOMETRYCOLLECTION comprising all geometries found in the `geom` set. Any MULTI\* geometries will be divided into separate singular geometries, e.g., MULTIPOINT((0 0), (1 1)) would be divided into POINT(0 0) and POINT(1 1) in the results; the same is true for elements of a GEOMETRYCOLLECTION found in `geom`, where a GEOMETRYCOLLECTION within the provided `geom` set will also be parsed, effectively flattening it and adding the individual geometries to the resulting GEOMETRYCOLLECTION. Any empty geometries in `geom` are ignored even if they are part of a GEOMETRYCOLLECTION. Any duplicate WKTs will be retained. Dissolves all geometries within a given set into a single geometry. Note that the resulting single geometry can still be a group of noncontiguous geometries but represented as a single group, e.g., a GEOMETRYCOLLECTION. Best performance when used in conjunction with adjacent geometries. Dissolves all geometries within a given set into a single geometry. Note that the resulting single geometry can still be a group of noncontiguous geometries but represented as a single group, e.g., a GEOMETRYCOLLECTION. Best performance when used in conjunction with overlapping geometries. Returns a POLYGON or MULTIPOLYGON comprising the shared portion between all geometries found in the `geom` set. Returns an empty GEOMETRYCOLLECTION if there is no shared portion between all geometries. Functionally equivalent to `ST_INTERSECTION(ST_INTERSECTION(geom1, geom2), ... geomN)`. Returns a LINESTRING that represents a "track" of the given points (`x`, `y`) ordered by the given sort column `t` (e.g., a timestamp or sequence number). If any of the values in the specified columns are `null`, the null "point" will be left out of the resulting LINESTRING. If there's only one non-null "point" in the source table, a POINT is returned. If there are no non-null "points" in the source table, a `null` is returned. Returns a LINESTRING that represents a "track" of the given 3D points (`x`, `y`, `z`) ordered by the given sort column `t` (e.g., a timestamp or sequence number). If any of the values in the specified columns are `null`, the null "point" will be left out of the resulting LINESTRING. If there's only one non-null "point" in the source table, a POINT is returned. If there are no non-null "points" in the source table, a `null` is returned. Returns a GEOMETRYCOLLECTION containing POLYGONs comprising the provided (MULTI)LINESTRING(s). (MULTI)POINT and (MULTI)POLYGON geometries are ignored when calculating the resulting GEOMETRYCOLLECTION. If a valid POLYGON cannot be constructed from the provided (MULTI)LINESTRING(s), an empty GEOMETRYCOLLECTION will be returned. #### Track Functions The following functions are available in both SQL and the native API. Returns the total time, in the given `unit`, spanned by timestamp values in column `t`. Grouping by *track* ID will return the duration per *track*. The duration can be returned in any of the following date/time units: * `YEAR` * `MONTH` * `DAY` * `HOUR` * `MINUTE` * `SECOND` * `MILLISECOND` The default `unit` is `MILLISECOND`. Returns the total length of the track whose position values are specified by `lat` & `lon` and whose ordering is determined by an ascending sort on the timestamp `t`. Length can be returned with any of the following solution types:

Type	Description
`0`	2D Euclidean length in degrees
`1`	(default) Length on a sphere in meters
`2`	Length on a spheroid in meters

ST\_TRACK\_DWITHIN

The `ST_TRACK_DWITHIN` table function finds tracks that are related, within spatial or temporal bounds (or both) to the given track(s). The track(s) to use as the filter criteria will be specified by the `SEARCH_*` parameters. The `TRACK_*` parameters specify the set of tracks to search through for a match. This function is only available in SQL or in the native API via [/execute/sql](/content/api/rest/execute_sql_rest). The basic form of the `ST_TRACK_DWITHIN` function follows. ```sql ST_TRACK_DWITHIN Table Function Syntax theme={null} SELECT * FROM TABLE ( ST_TRACK_DWITHIN ( TRACK_TABLE => INPUT_TABLE(), TRACK_ID_COLUMN => < '' | >, TRACK_X_COLUMN => < '' | >, TRACK_Y_COLUMN => < '' | >, TRACK_ORDER_COLUMN => < '' | >, SEARCH_TABLE => INPUT_TABLE(

), SEARCH_ID_COLUMN => < '' | >, SEARCH_X_COLUMN => < '' | >, SEARCH_Y_COLUMN => < '' | >, SEARCH_ORDER_COLUMN => < '' | >, [ SEARCH_XY_DISTANCE => '', SPATIAL_SOLUTION_TYPE => , SEARCH_TIME_DISTANCE => '' ] ) ) ``` Name of the table to search for tracks matching the track(s) specified in the `SEARCH_*` data set. To perform a search on the *flights* table, pass the name of the table to `INPUT_TABLE`: ``` INPUT_TABLE(flights) ``` To perform a search on the result of a query, pass the query to `INPUT_TABLE`: ``` INPUT_TABLE ( SELECT * FROM flights_west UNION SELECT * FROM flights_east ) ``` Table to search track column, containing the unique identifier for the track to which each track point belongs. Table to search track column, containing the longitude value of each track point. Table to search track column, containing the latitude value of each track point. Table to search track column, by which the searched track points will be sorted in ascending order. Name of the search criteria track table, containing the track(s) to be used as the filter criteria when searching for matching tracks in the `TRACK_*` data set. To match tracks from the *flights\_of\_interest* table, pass the name of the table to `INPUT_TABLE`: ``` INPUT_TABLE(flights_of_interest) ``` To match tracks from the result of a query, pass the query to `INPUT_TABLE`: ``` INPUT_TABLE ( SELECT * FROM flights_of_interest_west UNION SELECT * FROM flights_of_interest_east ) ``` Search criteria track column, containing the unique identifier for the track to which each track point belongs. Search criteria track column, containing the longitude value of each track point. Search criteria track column, containing the latitude value of each track point. Search criteria track column, by which the filter track points will be sorted in ascending order. The radius around the given tracks to search for matching tracks. This parameter is not applicable when using a `SPATIAL_SOLUTION_TYPE` of `0`.

Unit	Description
`f`	Feet
`ki`	Kilometers
`m`	(default) Meters
`mi`	Miles

Spatial match solution type; any of the following:

Type	Description
`0`	(default) 2D Euclidean length in degrees
`1`	Length on a sphere, returned in units specified by `SEARCH\_XY\_DISTANCE`
`2`	Length on a spheroid, returned in units specified by `SEARCH\_XY\_DISTANCE`

The maximum allowable time difference between a search criteria track's point and a matched track's points. The time can use any of the following suffices for units:

Unit	Description
`ms`	Milliseconds
`s`	(default) Seconds
`m`	Minutes
`h`	Hours
`d`	Days
`w`	Weeks
`months`	Months
`y`	Years

To see the matches between a set of flights and a given set of flights of interest: ```sql ST_TRACK_DWITHIN Example theme={null} SELECT * FROM TABLE ( ST_TRACK_DWITHIN ( TRACK_TABLE => INPUT_TABLE(example_geospatial.flights), TRACK_ID_COLUMN => 'id', TRACK_X_COLUMN => 'lon', TRACK_Y_COLUMN => 'lat', TRACK_ORDER_COLUMN => 'flight_time', SEARCH_TABLE => INPUT_TABLE ( SELECT id, lon, lat, flight_time FROM example_geospatial.flights_northwest UNION SELECT id, lon, lat, flight_time FROM example_geospatial.flights_northeast ), SEARCH_ID_COLUMN => 1, SEARCH_X_COLUMN => 2, SEARCH_Y_COLUMN => 3, SEARCH_ORDER_COLUMN => 4, SEARCH_TIME_DISTANCE => '5m', SEARCH_XY_DISTANCE => '1km', SPATIAL_SOLUTION_TYPE => 1 ) ) ```

ST\_TRACKINTERSECTS

The `ST_TRACKINTERSECTS` table function finds tracks pass through the given geofence(s). The geofence(s) use as the filter will be specified by the `GEOFENCE_*` parameters. The `TRACK_*` parameters specify the set of tracks to search through for any intersecting the geofence(s). The result will include a record for each intersecting track & geofence pair, with: * a `LINESTRING` representing the full track intersecting a geofence * a `LINESTRING` representing the geofence it intersected This function is only available in SQL or in the native API via [/execute/sql](/content/api/rest/execute_sql_rest). The basic form of the `ST_TRACKINTERSECTS` function follows. ```sql ST_TRACKINTERSECTS Table Function Syntax theme={null} SELECT * FROM TABLE ( ST_TRACKINTERSECTS ( TRACK_TABLE => INPUT_TABLE(), TRACK_ID_COLUMN => < '' | >, TRACK_X_COLUMN => < '' | >, TRACK_Y_COLUMN => < '' | >, TRACK_ORDER_COLUMN => < '' | >, GEOFENCE_TABLE => INPUT_TABLE(

Function Call	Result
`JSON('\[1, 2, "C"]')`	`\[1,2,"C"]`
`JSON('\{"A": "B", "C": \{"D": 5, "F": 7}}')`	`\{"A":"B","C":\{"D":5,"F":7}}`
`JSON(null)`	null

Creates a JSON array out of the given list of values. An optional `` parameter can be added to the end of the list to specify handling of *null* values, though, at present, this only exists for compatibility with other databases' syntax--the option will be ignored and all *null* values will be added to the array as *null*. Accepted null handling options: * `NULL ON NULL` This function is only available in SQL or in the native API via [/execute/sql](/content/api/rest/execute_sql_rest). Examples:

Function Call	Result
`JSON\_ARRAY(1, 2, 'C')`	`\[1,2,"C"]`
`JSON\_ARRAY(JSON('\{"A": "B"}'), JSON('\{"C": 4}'))`	`\[\{"A":"B"},\{"C":4}]`
`JSON\_ARRAY(1, null, 'C' NULL ON NULL)`	`\[1,null,"C"]`

Appends the given `value` to the given array `arr` of type string, array, or JSON Examples:

Function Call	Result
`JSON\_ARRAY\_APPEND('\[1, 2]', 'C')`	`\[1,2,"C"]`
`JSON\_ARRAY\_APPEND('\{"A": "B"}', JSON('\{"C": 4}'))`	`\[\{"A":"B"},\{"C":4}]`

Returns whether the given JSON `json` contains the primitive value `val` at the JSON query path, `path` Examples:

Function Call	Result
`JSON\_ARRAY\_CONTAINS('\[1, 2]', '\$', 2)`	`true`
`JSON\_ARRAY\_CONTAINS('\[1, 2]', '\$', 'C')`	`false`
`JSON\_ARRAY\_CONTAINS('\{"A": \[2, 3]}', '\$.A', 2)`	`true`
`JSON\_ARRAY\_CONTAINS('\{"A": \{"B": \[3, 4]}}', '\$\["A"]\["B"]', 4)`	`true`

Alias for `JSON_LENGTH` Returns whether the given JSON `json` contains the JSON query path, `path` Examples:

Function Call	Result
`JSON\_EXISTS('\[1, 2]', '\$')`	`true`
`JSON\_EXISTS('\[1, 2]', '\$.A')`	`false`
`JSON\_EXISTS('\{"A": \[2, 3]}', '\$.A')`	`true`
`JSON\_EXISTS('\{"A": \{"B": 3, "D": 5}}', '\$\["A"]\["D"]')`	`true`

Returns the set of keys of the JSON object at the given `path` in `json`; null, if the path doesn't exist or contains a non-object (non-map) value Examples:

Function Call	Result
`JSON\_KEYS('\{}', '\$')`	`\[]`
`JSON\_KEYS('\[1, 2]', '\$.A')`	null
`JSON\_KEYS('\{"A": \[2, 3]}', '\$')`	`\["A"]`
`JSON\_KEYS('\{"A": \{"B": 3, "D": 5}}', '\$\["A"]')`	`\["B","D"]`
`JSON\_KEYS('\{"A": \{"B": 3, "D": 5}}', '\$\["A"]\["D"]')`	null
`JSON\_KEYS('\{"A": \{"B": 3, "D": \{}}}', '\$\["A"]\["D"]')`	`\[]`

Returns the number of items in the top-level object of the given `json`, or at the level of the optional JSON `path` Examples:

Function Call	Result
`JSON\_LENGTH('\{}')`	`0`
`JSON\_LENGTH('\[]')`	`0`
`JSON\_LENGTH('\[1, 2]')`	`2`
`JSON\_LENGTH('\{"A": \[2, 3]}')`	`1`
`JSON\_LENGTH('\{"A": \{"B": 3}, "D": "E"}')`	`2`
`JSON\_LENGTH('\{"A": \[2, 3]}', '\$.A')`	`2`
`JSON\_LENGTH('\{"A": \{"B": 3}, "D": "E"}', '\$.A')`	`1`

Creates a JSON array out of the given `value` Examples:

Function Call	Result
`JSON\_MAKE\_ARRAY(1)`	`\[1]`
`JSON\_MAKE\_ARRAY(JSON\_ARRAY(1,2,3))`	`\[\[1,2,3]]`
`JSON\_MAKE\_ARRAY(JSON('\{"A": "B"}'))`	`\[\{"A":"B"}]`

Creates a JSON map out of the given `value` set An optional `` parameter can be added to the end of the list to specify handling of *null* values, though, at present, this only exists for compatibility with other databases' syntax--the option will be ignored and all *null* values will be added to the array as *null*. Accepted null handling options: * `NULL ON NULL` This function is only available in SQL or in the native API via [/execute/sql](/content/api/rest/execute_sql_rest). Examples:

Function Call	Result
`JSON\_OBJECT()`	`\{}`
`JSON\_OBJECT('A': 2)`	`\{"A":2}`
`JSON\_OBJECT('A' VALUE 2)`	`\{"A":2}`
`JSON\_OBJECT(KEY 'A' VALUE 2)`	`\{"A":2}`
`JSON\_OBJECT('A': 'B', 'C': null NULL ON NULL)`	`\{"A":"B","C":null}`

Returns a formatted version of the given `json` object Examples:

Function Call	`JSON\_PRETTY('\[1,2,"C"]')`
Return	`\[ 1, 2, "C" ]`

Function Call	`JSON\_PRETTY('\{"A": "B", "C": \{"D": 5, "F": 7}}')`
Return	`\{ "A": "B", "C": \{ "D": 5, "F": 7 } ]`

Returns the JSON object at the given `path` in `json`; null, if the path doesn't exist or contains a primitive (non-object) value An optional `return` clause can be added to the end of the path to specify the return type of the object found, as well as the error handling for the object lookup. The format of the clause is: ``` [ WRAPPER] [ ON EMPTY] [ ON ERROR] ``` Return clause is only available in SQL or in the native API via [/execute/sql](/content/api/rest/execute_sql_rest).

Parameter	Description
`\`	Whether the returned object(s) should be wrapped in an array before being returned Valid wrapping schemes include: `WITHOUT \[ARRAY]` - no wrapper is applied (default) `WITH \[UNCONDITIONAL] \[ARRAY]` - return the object(s) in an array `WITH CONDITIONAL \[ARRAY]` - if returning a single object, don't wrap it; if returning multiple objects, wrap them in an array
`\`	The scheme to use for handling empty returned objects Valid schemes include: `NULL` - return null (default) `ERROR` - throw an error `EMPTY ARRAY` - return an empty array `EMPTY OBJECT` - return an empty object
`\`	The scheme to use for handling errors in looking up the object Valid schemes include: `NULL` - return null (default) `ERROR` - throw an error `EMPTY ARRAY` - return an empty array `EMPTY OBJECT` - return an empty object

Examples:

Function Call	Result
`JSON\_QUERY('\[1, 2]', '\$')`	`\[1,2]`
`JSON\_QUERY('\[1, 2]', '\$.A')`	null
`JSON\_QUERY('\{"A": \[2, 3]}', '\$.A')`	`\[2,3]`
`JSON\_QUERY('\{"A": \{"B": 3, "D": 5}}', '\$\["A"]')`	`\{"B":3,"D":5}`
`JSON\_QUERY('\{"A": \{"B": 3, "D": 5}}', '\$\["A"]\["D"]')`	null
`JSON\_QUERY('\{"A": \{"B": 3, "D": \[5, 6]}}', '\$\["A"]\["D"]')`	`\[5,6]`
`JSON\_QUERY('\{"A": \[]}', '\$.B' EMPTY ARRAY ON EMPTY)`	`\[]`
`JSON\_QUERY('\{"A": 0}', '\$.A' EMPTY OBJECT ON ERROR)`	`\{}`
`JSON\_QUERY('\{"A": 2, "C": 4}', '\$.\*')`	null
`JSON\_QUERY('\{"A": 2, "C": 4}', '\$.\*' WITH WRAPPER)`	`\[2,4]`

Returns the given `json` with the value at the JSON path `path` replaced with the replacement value `repl` Examples:

Function Call	Result
`JSON\_REPLACE('\[1, 2]', '\$', '\[3, 4]')`	`\[3,4]`
`JSON\_REPLACE('\[1, 2]', '\$.A', '"B"')`	`\[1,2]`
`JSON\_REPLACE('\{"A": \[2, 3]}', '\$.A', '\{"D": 5}')`	`\{"A":\{"D":5}}`
`JSON\_REPLACE('\{"A": \{"B": 3}}', '\$\["A"]', '"D"')`	`\{"A":"D"}`
`JSON\_REPLACE('\{"A": \{"B": \[3, 4]}}', '\$\["A"]\["B"]', 5)`	`\{"A":\{"B":5}}`

Returns the JSON type of the given `expr`

Expression Type	JSON Type
`BOOLEAN`	`BOOLEAN`
`TINYINT` `SMALLINT` `INTEGER` `BIGINT` `UNSIGNED BIGINT`	`LONG`
`REAL` `DOUBLE`	`DOUBLE`
`JSON` `VARCHAR`	`BOOLEAN` - if `true` or `false` `LONG` - if an integer value `DOUBLE` - if a floating-point value `STRING` - if a double-quoted literal `OBJECT` - if a JSON map `ARRAY` - if a JSON array
`ARRAY` (any)	`ARRAY`

Returns the JSON value at the given `path` in `json`; null, if the path doesn't exist or contains an object (non-primitive) value An optional `return` clause can be added to the end of the path to specify the return type of the value found, as well as the error handling for the value lookup. The format of the clause is: ``` [RETURNING ] [ ON EMPTY] [ ON ERROR] ``` Return clause is only available in SQL or in the native API via [/execute/sql](/content/api/rest/execute_sql_rest).

Parameter	Description
`\`	The JSON type of the value to return; if the value is not already of that type, it will be cast to that type, if possible Valid types include: `BOOLEAN` `LONG` `DOUBLE` `STRING`
`\`	The scheme to use for handling empty returned values Valid schemes include: `NULL` - return null (default) `ERROR` - throw an error `DEFAULT \` - return the given expression `expr`
`\`	The scheme to use for handling errors in looking up the value Valid schemes include: `NULL` - return null (default) `ERROR` - throw an error `DEFAULT \` - return the given expression `expr`

Examples:

Function Call	Result
`JSON\_VALUE('1', '\$')`	`1`
`JSON\_VALUE('\[1, 2]', '\$.A')`	null
`JSON\_VALUE('\["A", "B"]', '\$.1')`	`B`
`JSON\_VALUE('\{"A": \[2, 3]}', '\$\["A"]\["1"]')`	`3`
`JSON\_VALUE('\{"A": \{"B": 3, "D": 5}}', '\$\["A"]\["D"]')`	`5`
`JSON\_VALUE('\{"A": \{"B": 3, "D": \[5, 6]}}', '\$.A.D.1')`	`6`
`JSON\_VALUE('1', '\$' RETURNING DOUBLE)`	`1.0`
`JSON\_VALUE('\{"A": \[]}', '\$.B' DEFAULT '\' ON EMPTY)`	`\`
`JSON\_VALUE('\{"A": \[]}', '\$.A' ERROR ON ERROR)`	error returned

Function Call	Result
`IF\_ERROR((double(10)/0), 1)`	`1.0`
`IF\_ERROR(log(-1), 1)`	`1.0`

Evaluates the given *double* or *float* `expr`, and if it resolves to infinity, return `val` Example:

Function Call	Result
`IF\_INF((double(10)/0), 999)`	`999.0`
`IF\_INF(log(-1), 999)`	`NaN`

Alias for `IF_INF(expr, val)` Evaluates the given *double* or *float* `expr`, and if it resolves to `NaN`, return `val` Example:

Function Call	Result
`IF\_NAN((double(10)/0), -1)`	`Infinity`
`IF\_NAN(log(-1), -1)`	`-1.0`

Returns `1` (true) if `expr` is infinity by IEEE standard; otherwise, returns `0` (false) Alias for `ISINFINITY` Returns `1` (true) if `expr` is not a number by IEEE standard; otherwise, returns `0` (false) Alias for `ISNAN` Returns `1` (true) if `expr` is a number by IEEE standard; otherwise, returns `0` (false) Alias for `ISNUMERIC` Returns the value of `x` \* 2^exp as a *double* Returns the value of `x` \* 2^exp as a *float* Returns whichever of `expr_a` and `expr_b` has the smaller value, based on typed comparison Returns the natural logarithm of `expr` as a *double* Returns the natural logarithm of `expr` as a *float* Alias for `LN` Returns the *base-10* logarithm of `expr` as a *double* Returns the *base-10* logarithm of `expr` as a *float* Returns the natural logarithm of one plus `expr` as a *double* Returns the natural logarithm of one plus `expr` as a *float* Returns the binary (*base-2*) logarithm of `expr` as a *double* Returns the binary (*base-2*) logarithm of `expr` as a *float* Alias for `LNF` Calculates the length of the longest series of consecutive `1` bits in the integer `expr` Calculates the remainder after integer division of `dividend` by `divisor` Returns the next representable floating-point value adjacent to `expr` in the direction of `target`, incrementing `expr` if `target` is greater, decrementing if lesser. The default value of `target` is `MAX_DOUBLE` (always increments when omitted). The return type matches the input (*float* or *double*). Returns the next representable floating-point value less than `expr`. The return type matches the input (*float* or *double*). Returns the value of *pi* Alias for `POWER` Alias for `POWERF` Returns `base` raised to the power of `exponent` as a *double* Returns `base` raised to the power of `exponent` as a *float* Returns the conversion of `expr` (in degrees) to radians as a *double* Returns the conversion of `expr` (in degrees) to radians as a *float* Returns a random floating-point value, with an optional `seed` Returns NULL if `y` is NULL; otherwise, returns `x` Returns NULL if `x` is NULL; otherwise, returns `y` Rounds `expr` to the nearest decimal number with `scale` decimal places when `scale` is a positive number; rounds to the nearest number such that the result has `-(scale)` zeros to the left of the decimal point when `scale` is negative; use `scale` of `0` to round to the nearest integer. The default value of `scale` is `0`. Examples:

Function Call	Result
`ROUND(12345.678)`	`12346`
`ROUND(12345.678, 2)`	`12345.68`
`ROUND(12345.678, 0)`	`12346`
`ROUND(12345.678, -2)`	`12300`

Determines whether a number is positive, negative, or zero; returns one of the following three values:

Expression Value	Result
positive	`1`
zero	`0`
negative	`-1`

Returns the sine of `expr` as a *double* Returns the sine of `expr` as a *float* Returns the hyperbolic sine of `expr` as a *double* Returns the hyperbolic sine of `expr` as a *float* Returns the square root of `expr` as a *double* Returns the square root of `expr` as a *float* Returns the tangent of `expr` as a *double* Returns the tangent of `expr` as a *float* Returns the hyperbolic tangent of `expr` as a *double* Returns the hyperbolic tangent of `expr` as a *float* Rounds `expr` down to the nearest decimal number with `scale` decimal places when `scale` is a positive number; rounds down to the nearest number such that the result has `-(scale)` zeros to the left of the decimal point when `scale` is negative; use `scale` of `0` to round down to the nearest integer. The default value of `scale` is `0`. Examples:

Function Call	Result
`TRUNCATE(12345.678)`	`12345`
`TRUNCATE(12345.678, 2)`	`12345.67`
`TRUNCATE(12345.678, 0)`	`12345`
`TRUNCATE(12345.678, -2)`	`12300`

Defines a set of `count` equal intervals (buckets) within the range of `min` & `max`, and puts the value of `expr` into one of those buckets, where the value is greater than or equal to the minimum value of the bucket and less than the maximum value of the bucket. Returns the 1-based number of the bucket into which the value of `expr` fell. For values smaller than `min`, `0` is returned; for values greater than or equal to `max`, `count + 1` is returned. Examples: In the following examples, a set of *5* equal buckets are defined between *0* and *10* (*0-2*, *2-4*, *4-6*, *6-8*, & *8-10*), and various values are bucketed using that set.

Function Call	Result
`WIDTH\_BUCKET(-1, 0, 10, 5)`	`0`
`WIDTH\_BUCKET(0, 0, 10, 5)`	`1`
`WIDTH\_BUCKET(5, 0, 10, 5)`	`3`
`WIDTH\_BUCKET(10, 0, 10, 5)`	`6`
`WIDTH\_BUCKET(11, 0, 10, 5)`	`6`

Function Call	Result
`BIN(42)`	`'101010'`
`BIN(42, 8)`	`'00101010'`

The character represented by the standard ASCII code `expr` in the range \[ `0` - `127` ] Performs a string concatenation of `expr_a` & `expr_b`; use nested `CONCAT` calls to concatenate more than two strings The resulting field size of any `CONCAT` will be a string field big enough to hold the concatenated fields, e.g., concatenating a *char32* column and a *char64* column will result in a *char128* column. Concatenations longer than 256 characters will use an unrestricted-width string type. Returns the concatenation of `expr_a` and `expr_b`, truncated at the maximum size of the larger of `expr_a` and `expr_b`. For data columns, the size is explicit; for string constants, the size will be the smallest *charN* type that can hold the constant string. `CONCAT_TRUNCATE` will not work if any parameter is an unrestricted-width string. Examples:

Function Call	Result
`CONCAT\_TRUNCATE('ABC123','!')`	`ABC123!`
`CONCAT\_TRUNCATE('AB','CDE')`	`ABCD` (char4 is the minimum size required to hold the `CDE` value, so the result is truncated at 4 characters)
`CONCAT\_TRUNCATE('ABCD1234','DEFG')`	`ABCD1234` (an 8-character string is the minimum size required to hold the `ABCD1234` value, so no characters can be appended)

Returns `1` if `expr` contains `match` by string-literal comparison; otherwise, returns `0` Convert the value represented by the `expr` string from one numeric base to another. Base parameters may be \[`2` - `36`]. Binary, octal and hexadecimal strings may have an optional prefix. Examples:

Function Call	Result
`CONV('42', 10, 16)`	`'2A'`
`CONV('2a', 16, 10)`	`'42'`
`CONV('11', 2, 10)`	`'3'`
`CONV('0o52', 8, 2)`	`'101010'`

Alias for `HEX` Returns a value between `0` and `4` that represents the difference between the sounds of `expr_a` and `expr_b` based on the `SOUNDEX()` value of the strings--a value of `4` is the best possible sound match Returns the Levenshtein edit distance between `expr_a` and `expr_b`; the lower the value, the more similar the two strings are Returns `1` if `expr` ends with `match` by string-literal comparison; otherwise, returns `0` Alias for `UNHEX` Convert the value represented by `expr` into a hexadecimal string representation. Use optional `minimum_digits` to add leading 0s when needed; defaults to 1. Examples:

Function Call	Result
`HEX(42)`	`'2A'`
`HEX(42, 8)`	`'0000002A'`

Alias for `UNHEX` Returns whether `expr` matches the given `match`. The match is a string literal one with the following exceptions: * `%` matches any string of 0 or more characters * `_` matches any single character * `[character_set]` matches any single character listed in the set. The `character_set` may contain ranges using a dash (`-`). A A `character_set` starting with `^` will match any character not in the specified set. By default, the escape character used to match special characters, such as `%`, in the `expr` literally is `\`. The `esc_char`, if given, will override this default escape character. `ILIKE` is case-insensitive. For a case-sensitive match, see `LIKE`. The match expression has to match the reference expression completely--it will not return *true* for partial matches. Add `%` before and/or after the match for partial matches. Examples of successful matches: | Function Call | | ---------------------------------- | | `ILIKE('Brook', 'BROOK')` | | `ILIKE('Brooke', 'BROOK_')` | | `ILIKE('Brooklyn', 'BROOK%')` | | `ILIKE('50_50', '50\\_50')` | | `ILIKE('100%', '100\\%')` | | `ILIKE('50\\50', '50\\\\50')` | | `ILIKE('abqd', 'AB[A-Z]D')` | | `ILIKE('abqd', 'AB[^0-9]D')` | | `ILIKE('ab-d', 'AB[123~-]D', '~')` | | `ILIKE('a_c', 'A~_C', '~')` | | `ILIKE('100%', '100~%', '~')` | Returns `expr` with the first letter of each word in uppercase Returns the octet of the IP address given in `expr` at the position specified by `part_num`. Valid `part_num` values are constants from `1` to `4`. Examples:

Function Call	Result
`IPV4\_PART('12.34.56.78', 1)`	`12`
`IPV4\_PART('12.34.56.78', 4)`	`78`

Returns `1` if `expr` is an IPV4 address; returns `0` otherwise Alias for `IS_IPV4` Converts `expr` to lowercase Returns the leftmost `num_bytes` bytes from `expr` Alias for `LENGTH` Returns the number of characters in `expr` Returns whether `expr` matches the given `match`. The match is a string literal one with the following exceptions: * `%` matches any string of 0 or more characters * `_` matches any single character * `[character_set]` matches any single character listed in the set. The `character_set` may contain ranges using a dash (`-`). A A `character_set` starting with `^` will match any character not in the specified set. By default, the escape character used to match special characters, such as `%`, in the `expr` literally is `\`. The `esc_char`, if given, will override this default escape character. `LIKE` is case-sensitive. For a case-insensitive match, see `ILIKE`. The match expression has to match the reference expression completely--it will not return *true* for partial matches. Add `%` before and/or after the match for partial matches. Examples of successful matches: | Function Call | | --------------------------------- | | `LIKE('Brook', 'Brook')` | | `LIKE('Brooke', 'Brook_')` | | `LIKE('Brooklyn', 'Brook%')` | | `LIKE('50_50', '50\\_50')` | | `LIKE('100%', '100\\%')` | | `LIKE('50\\50', '50\\\\50')` | | `LIKE('abqd', 'ab[a-z]d')` | | `LIKE('abqd', 'ab[^0-9]d')` | | `LIKE('ab-d', 'ab[123~-]d', '~')` | | `LIKE('a_c', 'a~_c', '~')` | | `LIKE('100%', '100~%', '~')` | Returns the starting position of the first match of `match` in `expr`, starting from position 1 or `start_pos` (if specified). If `match` can't be found or `start_pos` is outside the range of letters in `expr`, a `0` is returned. Alias for `LCASE` Left pads the given `expr` string with the `pad` string to the given `length` of bytes. If `expr` is longer than `length`, the return value is shortened to `length` bytes. If `length` is larger than 256, it will be truncated to 256 bytes. The default padding character is a space. The use of multi-byte characters in this function may have unexpected results. Examples:

Function Call	Result
`LPAD('test', 9, 'pad')`	`padpatest`
`LPAD('test', 3, 'pad')`	`tes`

Removes whitespace from the left side of `expr` Convert the value represented by `expr` into an octal (base 8) string representation. Use optional `minimum_digits` to add leading 0s when needed; defaults to 1. Examples:

Function Call	Result
`OCT(42)`	`'52'`
`OCT(42, 8)`	`'00000052'`

Alias for `LOCATE` Returns a count of the number of times the `regex` pattern is matched in `expr`. Matches do not overlap, so the start of a future match must start after the end of the previous match. The `regex` parameter is the regular expression to try to match. It must be a string-literal with 256 characters or fewer. Generally, POSIX-compliant regular expressions are supported for `regex`. The escape character used to match wildcards in the `expr` literally is `\`. See `REGEXP_LIKE` for more regular expression examples. The optional `position` parameter specifies where to start searching in `expr` for the first match. The first character in the string has a `position` of `1` (the default). The optional `mode` parameter is a string which can be empty (the default) for the default behavior. See `REGEXP_LIKE` for the list of supported mode flags. Examples of `REGEXP_COUNT`:

Function Call	Result
`REGEXP\_COUNT('abababab', 'abab')`	2
`REGEXP\_COUNT('abababab', 'abab', 2)`	1
`REGEXP\_COUNT('don''t', 'DON''T', 1, 'i')`	1

Returns the starting position (1-based) in `expr` where a `regex` match is found. If no match is found, `0` is returned. The `regex` parameter is the regular expression to try to match. It must be a string-literal with 256 characters or fewer. Generally, POSIX-compliant regular expressions are supported for `regex`. The escape character used to match wildcards in the `expr` literally is `\`. See `REGEXP_LIKE` for more regular expression examples. The optional `position` parameter specifies where to start searching in `expr` for the first match. The first character in the string has a `position` of `1` (the default). The optional `occurrence` parameter specifies which occurrence of the `regex` match is desired. For example, `2` would return the second occurrence of `regex` in `expr`. Matches do not overlap, so the start of a future match must start after the end of the previous match. The optional `begin_end` parameter specifies if the beginning or ending position is desired. Use `0` (the default) for the beginning of the match and use `1` for the position after the end of the match. The optional `mode` parameter is a string which can be empty (the default) for the default behavior. See `REGEXP_LIKE` for the list of supported mode flags. The optional `group` parameter specifies which regular expression group's (i.e., parentheses inside `regex`) beginning/ending position to return. The default of `0` uses the entire matched expression, while a `group` of `1` through `9` corresponds to the 1st group up through the 9th group of the match. Examples of `REGEXP_INSTR`:

Function Call	Result
`REGEXP\_INSTR('abcdefg', 'bc')`	2
`REGEXP\_INSTR('abcdefg', 'ab', 2)`	0
`REGEXP\_INSTR('abcabc', 'a', 1, 2)`	4
`REGEXP\_INSTR('abcabc', 'abc', 1, 1, 1)`	4
`REGEXP\_INSTR('abcabc', 'B C', 1, 1, 0, 'ix')`	2
`REGEXP\_INSTR('abcdefg', '(C(.(.)))', 1, 1, 0, 'i', 3)`	5

Returns whether `expr` matches the given `regex`. Generally, POSIX-compliant regular expressions are supported. The optional `mode` parameter is a string which can be empty (the default) for the default behavior. It can contain the following letters for the associated optional modified behaviors:

Letter	Meaning
`i`	Case-insensitive matches
`m`	Treat input as multiple lines so `^` and `\$` match around newlines and not just the beginning and ending of the string
`n`	Allows `.` to also match a newline character
`x`	Ignore any whitespace (e.g., spaces) in `regex`

The escape character used to match wildcards in the `expr` literally is `\`. The `regex` can match the `expr` partially. To perform full matches, `^` and `$` can be used to match the start and end of `expr`, respectively. Examples of successful matches:

Function Call	Match Type
`REGEXP\_LIKE('partial', 'part')`	Partial
`REGEXP\_LIKE('Case', 'cAsE', 'i')`	Case-insensitive
`REGEXP\_LIKE('dot', 'd.t')`	Any character
`REGEXP\_LIKE('range', 'ra\[a-z]ge')`	Character range
`REGEXP\_LIKE('zeroorone', 'z?zer(oor)?one')`	0 or 1 token
`REGEXP\_LIKE('zeroormore', 'z\zer(\[om]or)\e')`	0 or more tokens
`REGEXP\_LIKE('oneormore', 'o+n(\[em]or)+e')`	1 or more tokens
`REGEXP\_LIKE('A to Z', '^A.\*Z\$')`	Begin/End
`REGEXP\_LIKE('41ph4Num', '^\[\[:alnum:]]+\$')`	Character class
`REGEXP\_LIKE('Escape?', 'Escape?')`	Escape wildcard

Alias for `REGEXP_LIKE` Returns the `expr` string after replacing `regex` matches with the `replace` string parameter. The `regex` parameter is the regular expression to try to match. It must be a string-literal with 256 characters or fewer. Generally, POSIX-compliant regular expressions are supported for `regex`. The escape character used to match wildcards in the `expr` literally is `\`. See `REGEXP_LIKE` for more regular expression examples. The `replace` parameter is the optional text with which to replace each match of `regex` in `expr`. The default is an empty string, which will simply remove the specified occurrences of `regex` in `expr`. A `replace` of `\0` will insert the entire matched expression, while a `replace` of `\1` through `\9` will use the corresponding matched grouping (parentheses inside `regex`) as the replacement text. The optional `position` parameter specifies where to start searching in `expr` for the first match. The first character in the string has a `position` of `1` (the default). The optional `occurrence` parameter specifies which occurrence of the `regex` match to replace. For example, `2` would only replace the second occurrence of `regex` in `expr`. Matches do not overlap, so the start of a future match must start after the end of the previous match. Use `0` (the default) to replace all occurrences. The optional `mode` parameter is a string which can be empty (the default) for the default behavior. See `REGEXP_LIKE` for the list of supported mode flags. Examples of `REGEXP_REPLACE`:

Function Call	Result
`REGEXP\_REPLACE('abc', 'b')`	ac
`REGEXP\_REPLACE('abc', 'b.', 'x')`	ax
`REGEXP\_REPLACE('abcd', '.', 'x', 3, 1)`	abxd
`REGEXP\_REPLACE('abcd', '(b(.))', '-\2-')`	a-c-d

Returns the portion of the `expr` string that matched `regex`. An empty string is returned if no match is found. The `regex` parameter is the regular expression to try to match. It must be a string-literal with 256 characters or fewer. Generally, POSIX-compliant regular expressions are supported for `regex`. The escape character used to match wildcards in the `expr` literally is `\`. See `REGEXP_LIKE` for more regular expression examples. The optional `position` parameter specifies where to start searching in `expr` for the first match. The first character in the string has a `position` of `1` (the default). The optional `occurrence` parameter specifies which occurrence of the `regex` match is desired. For example, `2` would return the second occurrence of `regex` in `expr`. Matches do not overlap, so the start of a future match must start after the end of the previous match. The optional `mode` parameter is a string which can be empty (the default) for the default behavior. See `REGEXP_LIKE` for the list of supported mode flags. The optional `group` parameter specifies which regular expression grouping (i.e., parentheses inside `regex`) to use. The default of `0` uses the entire matched expression, while a `group` of `1` through `9` corresponds to the 1st group up through the 9th group of the match. Examples of `REGEXP_SUBSTR`:

Function Call	Result
`REGEXP\_SUBSTR('abcdefg', 'b.')`	bc
`REGEXP\_SUBSTR('abcadeafghij', 'a...', 2, 1)`	adea
`REGEXP\_SUBSTR('abcadeafghij', 'a...', 1, 2)`	afgh
`REGEXP\_SUBSTR('abcdefg', '(C(.(.)))', 1, 1, 'i', 3)`	e

Replaces every occurrence of `match` in `expr` with `repl` Replaces every occurrence of the single-byte character `match` in `expr` with the single-byte character `repl` Replaces every occurrence of `match` in `expr` with `repl`, and then truncates the resulting string at 256 bytes if it is longer than that `REPLACE_TRUNCATE` will not work if any parameter is an unrestricted-width string. The use of multi-byte characters in this function may have unexpected results. Returns `expr` with the order of bytes reversed. The use of multi-byte characters in this function may have unexpected results. Examples:

Function Call	Result
`REVERSE('Reverse')`	`esreveR`
`REVERSE('Was it a bat I saw?')`	`?was I tab a ti saW`

Returns the rightmost `num_bytes` bytes from `expr` Right pads the given `expr` string with the `pad` string to the given `length` of bytes. If `expr` is longer than `length`, the return value is shortened to `length` bytes. If `length` is larger than 256, it will be truncated to 256 bytes. The default padding character is a space. The use of multi-byte characters in this function may have unexpected results. Examples:

Function Call	Result
`RPAD('test', 9, 'pad')`	`testpadpa`
`RPAD('test', 3, 'pad')`	`tes`

Removes whitespace from the right side of `expr` Returns a soundex value from `expr`. Only the first word in the string will be considered in the calculation. This is the algorithm used by most programming languages. Returns a string consisting of `n` space characters. The value of `n` can only be within the range of 0-256. Splits `expr` into groups delimited by the `delim` single-byte character and returns the `group_num` split group. If `group_num` is positive, groups will be counted from the beginning of `expr`; if negative, groups will be counted from the end of `expr` going backwards. Two consecutive delimiters will result in an empty string being added to the list of selectable groups. If no instances of `delim` exist in `expr`, the entire string is available at group `1` (and `-1`). Group `0` returns nothing. Examples:

Function Call	Result
`SPLIT('apple', 'p', 1)`	`a`
`SPLIT('apple', 'p', 2)`	\
`SPLIT('apple', 'p', -1)`	`le`

Returns `1` if `expr` starts with `match` by string-literal comparison; otherwise, returns `0` Compares `expr_a` to `expr_b` in a lexicographical sort

Situation	Result
`expr\_a` and `expr\_b` are the same	`0`
`expr\_a` comes before `expr\_b`, lexicographically	`-1`
`expr\_a` comes after `expr\_b`, lexicographically	`1`

Alias for `SUBSTRING` Returns `num_bytes` bytes from the `expr`, starting at the 1-based `start_pos` byte. If `num_bytes` is not specified, all bytes after `start_pos` will be returned. The use of multi-byte characters in this function may have unexpected results. Examples:

Function Call	Result
`SUBSTRING('banana', 3)`	`nana`
`SUBSTRING('banana', 3, 2)`	`na`

Alias for `HEX` Removes whitespace from both sides of `expr` Converts `expr` to uppercase Convert the hexadecimal string `expr` into a (decimal) number. Examples:

Function Call	Result
`UNHEX('42')`	`66`
`UNHEX('1A')`	`26`
`UNHEX('1a')`	`26`

Alias for `UCASE` #### Aggregate Functions The following functions can be used on *string* columns within [aggregations](/content/concepts/expressions#aggregate-expressions-label). These functions can be used to convert string column values into delimited lists of those values. Combines column values within a group into a single delimited *string* of those values. The default delimiter is a comma. Combines column values within a group into a single delimited *string* of those values, removing any duplicates. The default delimiter is a comma. ### User/Security Functions Returns the default schema of the current user Alias for `USER` Returns a non-negative integer representing an obfuscated version of `column`, using the given `seed`; default `seed` is `0` Returns whether the current user (or the given `user/role`, if specified) has been assigned the given `role`, either directly or indirectly:

Situation	Result
Current user (or given `user/role`) has been granted `role`	`true`
Current user (or given `user/role`) has not been granted `role`	`false`
Role `role` does not exist	null

Alias for `IS_MEMBER` Masks `length` bytes of `expr`, beginning at the byte position identified by `start`, with `*` characters (or the single-byte character specified in `char`): The use of multi-byte characters in this function may have unexpected results.

Function Call	Result
`MASK('Characters', 4, 5)`	`Cha\\\\\*rs`
`MASK('Characters', 5, 2, '#')`	`Char##ters`

Returns a randomly-generated UUID Alias for `HASH` Returns the hex digits of the SHA-256 hash of the given value `expr` as a `char64` string. Alias for `USER` Returns the username of the current user ## Column Expressions Many of the functions above accept expressions as inputs in place of column names for selecting data from [tables](/content/concepts/tables#table) e.g. [/aggregate/minmax](/content/api/rest/aggregate_minmax_rest). Given below are some examples of column expressions: ``` (x + y) (2 * col1) + col2 ``` ## Filter Expressions Data can be filtered with the use of *filter expressions* within many endpoints; e.g., [/filter](/content/api/rest/filter_rest). These expressions may contain *column expressions* as well as tests for equality/inequality for selecting records from the database. A *filter expression* cannot contain aggregation functions and should evaluate to a logical value ( *true* or *false* ). When the result of an expression evaluation is a numerical value, the result is converted to a logical value as follows: `0` is considered *false* and any other value is considered as *true*. Some examples of *filter expressions* are given below: ``` (x > y) (a != b) or (c = d) (timestamp > 1456749296789) and (x <= 10.0) ABS(timestamp - 1456749296789) < 60 * 60 * 1000 QUARTER(timestamp) = 1 and MOD(YEAR(timestamp), 4) = 0 msg_id == 'MSGID1' (x = 5) and y in (10,20,30) ``` ## Aggregate Expressions Some endpoints accept *aggregation expressions* as inputs for selecting data from [tables](/content/concepts/tables#table), e.g., [/aggregate/groupby](/content/api/rest/aggregate_groupby_rest). Such expressions can only contain aggregation functions and non-nested functions of aggregation functions. Aggregation functions include: * [Array Aggregation Functions](/content/concepts/expressions#expression-functions-array-agg) * [Geometry Aggregation Functions](/content/concepts/expressions#expression-functions-geo-aggregation) * [JSON Aggregation Functions](/content/concepts/expressions#expression-functions-json-agg) * [Lookup/Grouping Aggregation Functions](/content/concepts/expressions#expression-functions-lookup-agg) * [Math Aggregation Functions](/content/concepts/expressions#expression-functions-math-agg) * [String Aggregation Functions](/content/concepts/expressions#expression-functions-string-agg) ### Lookup/Grouping Functions The value of `ret_expr` where `agg_expr` is the maximum value (e.g. `ARG_MAX(cost, product_id)` returns the product ID of the highest cost product). `ARG_MAX(a, b)` is equivalent to `LAST(b, a)`. The value of `ret_expr` where `agg_expr` is the minimum value (e.g. `ARG_MIN(cost, product_id)` returns the product ID of the lowest cost product). `ARG_MIN(a, b)` is equivalent to `FIRST(b, a)`. The value of `ret_expr` where `agg_expr` is the minimum value (e.g. `FIRST(product_id, cost)` returns the product ID of the lowest cost product). `FIRST(a, b)` is equivalent to `ARG_MIN(b, a)`. Used primarily with [Rollup](/content/concepts/rollup), [Cube](/content/concepts/cube), and [Grouping Sets](/content/concepts/grouping_sets), to distinguish the source of *null* values in an aggregated result set, returns whether `expr` is part of the aggregation set used to calculate the values in a given result set row. Returns `0` if `expr` is part of the row's aggregation set, `1` if `expr` is not (meaning that aggregation took place across all `expr` values). For example, in a `ROLLUP(A)` operation, there will be two potential rows with *null* in the result set for column `A`. One row will contain *null* values of `A` aggregated together, and the other will contain *null*, but be an aggregation over the entire table, irrespective of `A` values. In this case, `GROUPING(A)` will return `0` for the *null* values of `A` aggregated together (as well as all other grouped `A` values) and `1` for the row resulting from aggregating across all `A` values. The value of `ret_expr` where `agg_expr` is the maximum value (e.g. `LAST(product_id, cost)` returns the product ID of the highest cost product). `LAST(a, b)` is equivalent to `ARG_MAX(b, a)`. ## Short-Circuiting The logical operators `and` & `or` implement a version of boolean short-circuiting where if either side of the expression encounters an error (divide-by-zero, etc.) but the other side evaluates to a value that alone would determine the overall value of the logical expression, the error will be ignored and the logical value returned: * If one side of an `and` evaluates to *false* and the other side evaluates to an error, *false* will be returned. * If one side of an `or` evaluates to *true* and the other side evaluates to an error, *true* will be returned. In the normal case, both sides of either of these operators would be evaluated simultaneously. However, to allow these operators to be used to check for error conditions, if an error is encountered on either side but the other side is enough information to determine the overall value of the expression, the error will be ignored. This same behavior applies to the evaluation branches of the following functions: * `IF` * `NVL` * `NVL2` It also applies to [SQL conditional functions](/content/sql/query#sql-conditional-functions).

Function Call	`DATE\_TO\_EPOCH\_MSECS(2017, 06, 15, 09, 22, 15, 42)`
Return	`1497518535042`
Resolves To	Thursday, June 15, 2017 9:22:15.042 AM

Function Call	`DATE\_TO\_EPOCH\_SECS(2017, 06, 15, 09, 22, 15)`
Return	`1494926535`
Resolves To	Thursday, June 15, 2017 9:22:15.042 AM

Function Call	`WEEK\_TO\_EPOCH\_MSECS(2017,-32)`
Return	`1463270400000`
Resolves To	Sunday, May 15, 2016 12:00:00 AM

Function Call	`WEEK\_TO\_EPOCH\_SECS(2017,-32)`
Return	`1463270400`
Resolves To	Sunday, May 15, 2016 12:00:00 AM