Scalar Functions in ksqlDB for Confluent Platform¶
Numeric functions¶
ACOS
¶
- Since: 0.28.0
ACOS(col1)
Returns the inverse (arc) cosine of col1
, in radians. Use the
DEGREES function to convert
the output to degrees.
This function returns NaN
for any input outside [-1, 1].
ASIN
¶
- Since: 0.28.0
ASIN(col1)
Returns the inverse (arc) sine of col1
, in radians. Use the
DEGREES function to convert
the output to degrees.
This function returns NaN
for any input outside [-1, 1].
AS_VALUE
¶
- Since: 0.9.0
AS_VALUE(keyColumn)``
Copies a row’s key column into the row’s value.
- Example
CREATE TABLE AGG AS SELECT ID, -- this is the grouping column, which is stored in the message key. AS_VALUE(ID) AS ID2 -- this creates a copy of ID, named ID2, which is stored in the message value. COUNT(*) AS COUNT FROM S GROUP BY ID;
ATAN
¶
- Since: 0.28.0
ATAN(col1)
Returns the inverse (arc) tangent of col1
, in radians. Use the
DEGREES function to convert
the output to degrees.
ATAN2
¶
- Since: 0.28.0
ATAN2(y, x)
Returns the inverse (arc) tangent of y / x
. This is equivalent to
the angle theta when Cartesian coordinates (x, y) are converted to
polar coordinates (radius, theta). The returned value is in radians.
Use the DEGREES function to convert the output to degrees.
If x
is zero, y / x
is undefined, and this function returns the
approximate value of a multiple of π/2.
CAST
¶
- Since: 0.1.0
CAST(COL0 AS BIGINT)
Converts one type to another. The following casts are supported:
From | To | Notes |
---|---|---|
any except BYTES |
STRING |
Converts the type to its string representation. |
VARCHAR |
BOOLEAN |
Any string that exactly
matches true ,
case-insensitive, is
converted to true . Any
other value is converted
to false . |
VARCHAR |
INT ,
BIGINT ,
DECIMAL ,
DOUBLE |
Converts string representation of numbers to number types. Conversion will fail if text does not contain a number or the number does not fit in the indicated type. |
VARCHAR |
TIME |
Converts time strings to
TIME . Conversion fails
if text is not in
HH:mm:ss format. |
VARCHAR |
DATE |
Converts date strings to
DATE . Conversion fails
if text is not in
yyyy-MM-dd format. |
VARCHAR |
TIMESTAMP |
Converts datestrings to
TIMESTAMP . Conversion
fails if text is not in
ISO-8601 format. |
TIMESTAMP |
TIME ,
DATE |
Converts a TIMESTAMP
to TIME or DATE by
extracting the time or
date portion of the
TIMESTAMP . |
DATE |
TIMESTAMP |
Converts a DATE to
TIMESTAMP by setting
the time portion to
00:00:00.000 |
INT , BIGINT ,
DECIMAL ,
DOUBLE |
INT ,
BIGINT ,
DECIMAL ,
DOUBLE |
Convert between numeric types. Conversion can result in rounding |
ARRAY |
ARRAY |
(Since 0.14) Convert between arrays of different element types |
MAP |
MAP |
(Since 0.14) Convert between maps of different key and value types |
STRUCT |
STRUCT |
(Since 0.14) Convert
between structs of
different field types.
Only fields that exist in
the target STRUCT type are
copied across. Any fields
in the target type that
don’t exist in the source
are set to NULL . Field
name matching is
case-sensitive. |
CEIL
¶
- Since: 0.1.0
CEIL(col1)
Returns the the smallest integer value that’s greater than or equal to
col1
.
COS
¶
Since: 0.28.0
COS(col1)
Returns the cosine of col1
. col1
is in radians. Use the
RADIANS function
to convert the input to radians, if necessary.
COSH
¶
- Since: 0.28.0
COSH(col1)
Returns the hyperbolic cosine of col1
. col1
is in radians. Use
the RADIANS function to
convert the input to radians, if necessary.
COT
¶
- Since: 0.28.0
COT(col1)``
Returns the cotangent of col1
. col1
is in radians. Use the
RADIANS function to convert
the input to radians, if necessary.
This implementation returns a large value approaching positive or
negative infinity near the asymptotes, because 2π and similar values
cannot be represented exactly. At 0, it returns Infinity
with the
same sign as the input.
ENTRIES
¶
- Since: 0.6.0
ENTRIES(map MAP, sorted BOOLEAN)
Creates an array of structs from the entries in a map. Each struct has a
field named K
containing the key, which is a string, and a field
named V
, which holds the value.
If sorted
is true, the entries are sorted by key.
EXP
¶
- Since: 0.6.0
EXP(col1)
Returns the exponential of col1
, which is e raised to the power of
col1
.
FLOOR
¶
- Since: 0.1.0
FLOOR(col1)
Returns the largest integer value that’s less than or equal to col1
.
GENERATE_SERIES
¶
- Since: 0.6.0
GENERATE_SERIES(start, end) GENERATE_SERIES(start, end, step)
Constructs an array of values between start
and end
, inclusive.
Parameters start
and end
can be an INT
or BIGINT
.
step
, if supplied, specifies the step size. The step can be positive
or negative. If not supplied, step
defaults to 1
. Parameter
step
must be an INT
.
GEO_DISTANCE
¶
- Since: 0.6.0
GEO_DISTANCE(lat1, lon1, lat2, lon2, unit)
The great-circle distance between two lat-long points, both specified in
decimal degrees. An optional final parameter specifies KM
(the
default) or miles
.
GREATEST
¶
- Since: 0.20.0
GREATEST(col1, col2, …)
Returns the largest non-null value from a variable number of comparable columns.
If comparing columns of different numerical types, first use the CAST function to cast them to be of the same type.
LEAST
¶
- Since: 0.20.0
LEAST(col1, col2, …)
Returns the smallest non-null value from a variable number of comparable columns.
If comparing columns of different numerical types, first use the CAST function to cast them to be of the same type.
LN
¶
- Since: 0.6.0
LN(col1)
Returns the natural logarithm of col1
, which is .
The value of col1
must be greater than 0.
LOG
¶
- Since: 0.29.0
LOG(value)
LOG(base, value)
The single-parameter version of this method returns the base 10
logarithm of the value
. The two-parameter version returns the
logarithm with the given base
of the value
.
This function returns -Infinity
for any base
when the value
is 0
. It returns NaN
when the value
is negative, when the
base
is negative, when the base
is 0
, or when the base
is 1
.
POWER
¶
- Since: 0.29.0
POWER(base, exponent)``
Calculates the value of the base
raised to the exponent
.
This function returns Infinity
when the result overflows the
DOUBLE
type.
ROUND
¶
- Since: 0.1.0
ROUND(col1)
ROUND(col1, scale)
Rounds a value to the number of decimal places specified by scale
.
If scale
is negative, the value is rounded to the right of the
decimal point.
Numbers equidistant to the nearest value are rounded up, in the positive direction.
If the number of decimal places is not provided, it defaults to zero.
SIGN
¶
- Since: 0.6.0
SIGN(col1)
Returns the sign of col1
as an INTEGER
:
- -1 if the argument is negative
- 0 if the argument is zero
- 1 if the argument is positive
null
argument isnull
SIN
¶
Since: 0.28.0
SIN(col1)
Returns the sine of col1
. col1
is in radians. Use the
RADIANS function to convert
the input to radians, if necessary.
SINH
¶
- Since: 0.28.0
SINH(col1)
Returns the hyperbolic sine of col1
. col1
is in radians. Use the
RADIANS function to convert
the input to radians, if necessary.
TAN
¶
- Since: 0.28.0
TAN(col1)
Returns the tangent of col1
. col1
is in radians. Use the
RADIANS function to convert
the input to radians, if necessary.
This implementation returns a large value approaching positive or negative infinity near the asymptotes, because π/2 and similar values cannot be represented exactly.
TANH
¶
- Since: 0.28.0
TANH(col1)
Returns the hyperbolic tangent of col1
. col1
is in radians. Use
the RADIANS function to
convert the input to radians, if necessary.
TRUNC
¶
- Since: 0.29.0
TRUNC(col1)
TRUNC(col1, scale)
Truncates (rounds toward zero) a value to the number of decimal places
specified by scale
.
If scale
is negative, the value is truncated to the left of the
decimal point. For example, TRUNC(12345.67, -3)
returns 12000
.
If the number of decimal places is not provided, it defaults to zero.
Collections¶
ARRAY
¶
- Since: 0.7.0
ARRAY[exp1, exp2, …]
Constructs an array from a variable number of inputs.
All elements must be coercible to a common SQL type. For more information, see Implicit Type Coercion.
ARRAY_CONCAT
¶
- Since: 0.21.0
ARRAY_CONCAT(array1, array2)
Returns an array representing the concatenation of both input arrays.
Returns NULL
if both input arrays are NULL
. If only one argument
is NULL
, the result is the other argument.
- Example
-- returns [1, 2, 3, 1, 2, 4, 1] SELECT ARRAY_CONCAT(ARRAY[1, 2, 3, 1, 2], [4, 1]); -- returns ['apple', 'apple', NULL, 'cherry', 'cherry'] SELECT ARRAY_CONCAT(ARRAY['apple', 'apple', NULL, 'cherry'], ARRAY['cherry']);
ARRAY_CONTAINS
¶
- Since: 0.6.0
ARRAY_CONTAINS(ARRAY[1, 2, 3], 3)
Given an array, checks if a search value is contained in the array.
Accepts any ARRAY
type. The type of the second param must match the
element type of the ARRAY
.
ARRAY_DISTINCT
¶
- Since: 0.10.0
ARRAY_DISTINCT([1, 2, 3])
Returns an array of all the distinct values, including NULL
if
present, from the input array.
The output array elements are in order of their first occurrence in the input.
Returns NULL
if the input array is NULL
.
- Examples
-- returns [1, 2, 3] SELECT ARRAY_DISTINCT(ARRAY[1, 1, 2, 3, 1, 2]); -- returns ['apple', NULL, 'cherry'] SELECT ARRAY_DISTINCT(ARRAY['apple', 'apple', NULL, 'cherry']);
ARRAY_EXCEPT
¶
- Since: 0.10.0
ARRAY_EXCEPT(array1, array2)
Returns an array of all the distinct elements from an array, except for those also present in a second array.
The order of entries in the first array is preserved but duplicates are removed.
Returns NULL
if either input is NULL
.
- Examples
-- returns [1] SELECT ARRAY_EXCEPT(ARRAY[1, 2, 3, 1, 2], [2, 3]); -- returns ['apple', NULL] SELECT ARRAY_EXCEPT(ARRAY['apple', 'apple', NULL, 'cherry'], ARRAY['cherry']);
ARRAY_INTERSECT
¶
- Since: 0.10.0
ARRAY_INTERSECT(array1, array2)
Returns an array of all the distinct elements from the intersection of both input arrays.
The order of entries in the output is the same as in the first input array.
Returns NULL
if either input array is NULL
.
- Examples
-- returns [1, 2] SELECT ARRAY_INTERSECT(ARRAY[1, 2, 3, 1, 2], [2, 1]); -- returns ['apple'] SELECT ARRAY_INTERSECT(ARRAY['apple', 'apple', NULL, 'cherry'], ARRAY['apple']);
ARRAY_JOIN
¶
- Since: 0.10.0
ARRAY_JOIN(col1, delimiter)
Creates a flat string representation of all the elements contained in an array.
The elements in the resulting string are separated by the chosen
delimiter
, which is an optional parameter. The default is the comma
character, ,
.
Array elements are limited to primitive ksqlDB types only.
ARRAY_LENGTH
¶
- Since: 0.8.0
ARRAY_LENGTH(ARRAY[1, 2, 3])
Returns the number of elements in an array.
If the supplied parameter is NULL
, the method returns NULL
.
ARRAY_MAX
¶
- Since: 0.10.0
ARRAY_MAX(['foo', 'bar', 'baz'])
Returns the maximum value from an array of primitive elements.
Arrays of other arrays, arrays of maps, arrays of structs, or combinations of these types aren’t supported.
If the array field is NULL
, or contains only NULL
values,
NULL
is returned.
Array entries are compared according to their natural sort order, which sorts the various data types as shown in the following examples.
- Examples
-- returns 2 SELECT ARRAY_MAX[-1, 2, NULL, 0]; -- returns true SELECT ARRAY_MAX[false, NULL, true]; -- returns 'baz' -- lower-case characters are "greater" than upper-case characters SELECT ARRAY_MAX['Foo', 'Bar', NULL, 'baz'];
ARRAY_MIN
¶
- Since: 0.10.0
ARRAY_MIN(['foo', 'bar', 'baz'])
Returns the minimum value from an array of primitive elements.
Arrays of other arrays, arrays of maps, arrays of structs, or combinations of these types aren’t supported.
If the array field is NULL
, or contains only NULL
values,
NULL
is returned.
Array entries are compared according to their natural sort order, which sorts the various data types as shown in the following examples.
- Examples
-- returns -1 SELECT ARRAY_MIN[-1, 2, NULL, 0]; -- returns false SELECT ARRAY_MIN[false, NULL, true]; -- returns 'Bar' SELECT ARRAY_MIN['Foo', 'Bar', NULL, 'baz'];
ARRAY_REMOVE
¶
- Since: 0.11.0
ARRAY_REMOVE(array, element)
Removes all elements from array
that are equal to element
.
If the array
field is NULL
, NULL
is returned.
- Examples
-- returns [1, 3, 1] SELECT ARRAY_REMOVE([1, 2, 3, 2, 1], 2); -- returns [NULL, true, true] SELECT ARRAY_REMOVE([false, NULL, true, true], false); -- returns ['Foo', 'Bar', 'baz'] SELECT ARRAY_REMOVE(['Foo', 'Bar', NULL, 'baz'], null);
ARRAY_SORT
¶
- Since: 0.10.0
ARRAY_SORT(['foo', 'bar', 'baz'], 'ASC|DESC')
Given an array of primitive elements, returns an array of the same elements sorted according to their natural sort order.
Arrays of other arrays, arrays of maps, arrays of structs, or combinations of these types aren’t supported.
Any NULL
values in the array are moved to the end.
If the array field is NULL
, NULL
is returned.
The optional second parameter specifies whether to sort the elements in
ascending (ASC
) or descending (DESC
) order. If neither is
specified, the default is ascending order.
- Examples
-- returns [-1, 0, 2, NULL] SELECT ARRAY_SORT[-1, 2, NULL, 0]; -- returns [false, true, NULL] SELECT ARRAY_SORT[false, NULL, true]; -- returns ['Bar', 'Foo', 'baz', NULL] SELECT ARRAY_SORT['Foo', 'Bar', NULL, 'baz'];
ARRAY_UNION
¶
- Since: 0.10.0
ARRAY_UNION(array1, array2)
Returns an array of all the distinct elements from both input arrays, in the order they’re encountered.
Returns NULL
if either input array is NULL
.
- Examples
-- returns [1, 2, 3, 4] SELECT ARRAY_UNION(ARRAY[1, 2, 3, 1, 2], [4, 1]); -- returns ['apple', NULL, 'cherry'] SELECT ARRAY_UNION(ARRAY['apple', 'apple', NULL, 'cherry'], ARRAY['cherry']);
ELT
¶
- Since: 0.6.0
ELT(n INTEGER, args VARCHAR[])
Returns element n
in the args
list of strings, or NULL
if
n
is less than 1 or greater than the number of arguments.
The ELT
function is 1-indexed.
ELT
is the complement to the FIELD
function.
FIELD
¶
- Since: 0.6.0
FIELD(str VARCHAR, args VARCHAR[])
Returns the 1-indexed position of str
in args
, or 0 if not
found.
If str
is NULL
, the return value is 0, because NULL
isn’t
considered to be equal to any value.
FIELD
is the complement to the ELT
function.
JSON_ARRAY_CONTAINS
¶
- Since: 0.6.0
JSON_ARRAY_CONTAINS('[1, 2, 3]', 3)
Given a STRING
containing a JSON array, checks if a search value is
contained in the array.
Returns false
if the first parameter doesn’t contain a JSON array.
MAP
¶
- Since: 0.7.0
MAP(key VARCHAR := value, …)
Constructs a map from specific key-value tuples.
All values must be coercible to a common SQL type.
For more information, see Implicit Type Coercion.
MAP_KEYS
¶
- Since: 0.10.0
MAP_KEYS(a_map)
Returns an array that contains all keys from the specified map.
Returns NULL
if the input map is NULL
.
- Example
-- returns ['apple', 'banana'] SELECT MAP_KEYS( MAP('apple' := 10, 'banana' := 20) );
MAP_VALUES
¶
- Since: 0.10.0
MAP_VALUES(a_map)
Returns an array that contains all values from the specified map.
Returns NULL
if the input map is NULL
.
- Example
-- returns [10, 20] SELECT MAP_VALUES( MAP('apple' := 10, 'banana' := 20) );
MAP_UNION
¶
- Since: 0.10.0
MAP_UNION(map1, map2)
Returns a new map containing the union of all entries from both input maps.
If a key is present in both input maps, the corresponding value from map2 is returned.
Returns NULL
if all input maps are NULL
.
- Examples
-- returns ['apple': 10, 'banana': 20, 'cherry': 99] SELECT MAP_UNION( MAP('apple' := 10, 'banana' := 20), MAP('cherry' := 99) ); -- returns ['apple': 50, 'banana': 20] SELECT MAP_UNION( MAP('apple' := 10, 'banana' := 20), MAP('apple' := 50) );
SLICE
¶
- Since: 0.6.0
SLICE(col1, from, to)
Slices a list based on the supplied indices.
The indices start at 1 and include both endpoints.
Invocation Functions¶
Apply lambda functions to collections.
FILTER
¶
- Since: 0.17.0
FILTER(array, x => …)
FILTER(map, (k,v) => …)
Filters a collection with a lambda function.
If the collection is an array, the lambda function must have one input argument.
If the collection is a map, the lambda function must have two input arguments.
REDUCE
¶
- Since: 0.17.0
REDUCE(array, state, (s, x) => …)
REDUCE(map, state, (s, k, v) => …)
Reduces a collection starting from an initial state.
If the collection is an array, the lambda function must have two input arguments.
If the collection is a map, the lambda function must have three input arguments.
If the state is NULL
, the result is NULL
.
TRANSFORM
¶
- Since: 0.17.0
TRANSFORM(array, x => …)
TRANSFORM(map, (k,v) => …, (k,v) => …)
Transforms a collection by using a lambda function.
If the collection is an array, the lambda function must have one input argument.
If the collection is a map, two lambda functions must be provided, and both lambdas must have two arguments: a map entry key and a map entry value.
Strings¶
CHR
¶
- Since: 0.10.0
CHR(decimal_code | utf_string)
Returns a single-character string representing the Unicode code-point described by the input.
The input parameter can be either a decimal character code or a string representation of a UTF code.
Returns NULL
if the input is NULL
or doesn’t represent a valid
code-point.
Commonly used to insert control characters such as Tab
(9),
Line Feed
(10), or Carriage Return
(13) into strings.
- Examples
-- returns 'K' SELECT CHR(75); -- returns 'K' SELECT CHR('\u004b'); -- returns '好' SELECT CHR(22909); -- returns '好' SELECT CHR('\u597d');
CONCAT
¶
- Since: 0.1.0
CONCAT(col1, col2, 'hello', …, col-n)
CONCAT(bytes1, bytes2, …, bytes-n)
Concatenates two or more string or bytes expressions.
Any inputs which evaluate to NULL
are replaced with an empty string
or bytes in the output.
CONCAT_WS
¶
- Since: 0.10.0
CONCAT_WS(separator, expr1, expr2, …)
Concatenates two or more string or bytes expressions, inserting a separator string or bytes between each.
If the separator is NULL
, this function returns NULL
.
Any expressions which evaluate to NULL
are skipped.
- Example
-- returns 'apple, banana, date' SELECT CONCAT_WS(', ', 'apple', 'banana', NULL, 'date');
ENCODE
¶
- Since: 0.10.0
ENCODE(col1, input_encoding, output_encoding)
Given a STRING that is encoded as input_encoding
, encode it using
the output_encoding
.
The accepted input and output encodings are:
hex
utf8
ascii
base64
Throws an exception if the provided encodings are not supported.
The following example encodes a hex
representation of a string to a
utf8
representation.
- Example
ENCODE(string, 'hex', 'utf8')
EXTRACTJSONFIELD
¶
- Since: 0.11.0
EXTRACTJSONFIELD(message, '$.log.cloud')
Given a STRING
that contains JSON data, extracts the value at the
specified JSONPath.
For example, given a STRING containing the following JSON:
{
"log": {
"cloud": "gcp836Csd",
"app": "ksProcessor",
"instance": 4
}
}
EXTRACTJSONFIELD(message, '$.log.cloud')
returns the STRING
gcp836Csd
.
If the requested JSONPath does not exist, the function returns NULL
.
The result of EXTRACTJSONFIELD
is always a STRING
. Use CAST
to convert the result to another type.
For example,
CAST(EXTRACTJSONFIELD(message, '$.log.instance') AS INT)
extracts
the instance number from the previous JSON object as a INT
.
The return type of EXTRACTJSONFIELD
is STRING
, so JSONPaths that
select multiple elements, like those containing wildcards, aren’t
supported.
Note
EXTRACTJSONFIELD
is useful for extracting data from JSON when either the
schema of the JSON data isn’t static or the JSON data is embedded in a row
that’s encoded using a different format, for example, a JSON field within an
Avro-encoded message.
If the whole row is encoded as JSON with a known schema or structure, use the JSON format and define the structure as the source’s columns.
For example, a stream of JSON objects similar to the previous example could be defined using a statement similar to the following:
CREATE STREAM LOGS (LOG STRUCT<CLOUD STRING, APP STRING, INSTANCE INT>, …)
WITH (VALUE_FORMAT='JSON', …)
FROM_BYTES
¶
- Since: 0.21.0
FROM_BYTES(bytes, encoding)
Converts a BYTES
column to a STRING
in the specified encoding
type.
The following list shows the supported encoding types.
hex
utf8
ascii
base64
IS_JSON_STRING
¶
- Since: 0.24.0
-- returns a Boolean
IS_JSON_STRING(json_string)
Returns true
if json_string
can be parsed as a valid JSON value;
otherwise, false
.
- Examples
-- returns true SELECT IS_JSON_STRING('[1, 2, 3]'); -- returns true SELECT IS_JSON_STRING('{}'); -- returns true SELECT IS_JSON_STRING('1'); -- returns true SELECT IS_JSON_STRING('\"abc\"'); -- returns true SELECT IS_JSON_STRING('null'); -- returns false SELECT IS_JSON_STRING(''); -- returns false SELECT IS_JSON_STRING('abc'); -- returns false SELECT IS_JSON_STRING(NULL);
JSON_ARRAY_LENGTH
¶
- Since: 0.24.0
-- returns an Integer or NULL
JSON_ARRAY_LENGTH(json_string)
Parses json_string
as a JSON value and returns the length of the
top-level array.
Returns NULL
if the string can’t be interpreted as a JSON array, for
example, when the string is NULL
or it doesn’t contain valid JSON,
or the JSON value is not an array.
- Examples
-- returns 3 SELECT JSON_ARRAY_LENGTH('[1, 2, 3]'); -- returns 3 SELECT JSON_ARRAY_LENGTH('[1, [1, [2]], 3]'); -- returns 0 SELECT JSON_ARRAY_LENGTH('[]'); -- returns NULL SELECT JSON_ARRAY_LENGTH('{}'); -- returns NULL SELECT JSON_ARRAY_LENGTH('123'); -- returns NULL SELECT JSON_ARRAY_LENGTH(NULL); -- returns NULL and logs an "Invalid JSON format" exception in the server log SELECT JSON_ARRAY_LENGTH('abc');
JSON_CONCAT
¶
- Since: 0.24.0
-- returns a String
JSON_CONCAT(json_string1, json_string2, ...)
Given N strings, parses them as JSON values and returns a string representing their concatenation.
Concatenation rules are identical to PostgreSQL’s || operator:
- If all strings deserialize into JSON objects, return an object with a union of the input keys. If there are duplicate objects, take values from the last object.
- If all strings deserialize into JSON arrays, return the result of array concatenation.
- If at least one of the deserialized values is not an object, convert non-array inputs to a single-element array and return the result of array concatenation.
- If at least one of the input strings is
NULL
or can’t be deserialized as JSON, returnNULL
.
Similar to PostgreSQL’s ||
operator, this function merges only
top-level object keys or arrays.
- Examples
-- returns '{"a":1,"b":2}' SELECT JSON_CONCAT('{\"a\": 1}', '{\"b\": 2}'); -- returns '{"a":{"3":4}}' SELECT JSON_CONCAT('{\"a\": {\"5\": 6}}', '{\"a\": {\"3\": 4}}'); -- returns '{}' SELECT JSON_CONCAT('{}', '{}'); -- returns '[1,2,3,4]' SELECT JSON_CONCAT('[1, 2]', '[3, 4]'); -- returns '[ 1, [2], [[3]], [[[4]]] ]' SELECT JSON_CONCAT('[1, [2]]', '[[[3]], [[[4]]]]'); -- returns '[null, null]' SELECT JSON_CONCAT('null', 'null'); -- returns '[1,2,{"a":1}]' SELECT JSON_CONCAT('[1, 2]', '{\"a\": 1}'); -- returns '[1, 2, 3]' SELECT JSON_CONCAT('[1, 2]', '3'); -- returns '[1, 2]' SELECT JSON_CONCAT('1', '2'); -- returns '[]' SELECT JSON_CONCAT('[]', '[]'); -- returns NULL SELECT JSON_CONCAT('abc', '[1]'); -- returns NULL SELECT JSON_CONCAT(NULL, '[1]');
JSON_KEYS
¶
- Since: 0.24.0
-- returns Array<String>
JSON_KEYS(json_string)
Parses json_string
as a JSON object and returns an array of strings
representing the top-level keys.
Returns NULL
if the string can’t be interpreted as a JSON object,
for example, when the string is NULL
or it does not contain valid
JSON, or the JSON value is not an object.
- Examples
-- returns ['a', 'b', 'd'] SELECT JSON_KEYS('{\"a\": \"abc\", \"b\": { \"c\": \"a\" }, \"d\": 1}'); -- returns [] SELECT JSON_KEYS('{}'); -- returns NULL SELECT JSON_KEYS('[]'); -- returns NULL SELECT JSON_KEYS('123') => NULL JSON_KEYS(NULL); -- returns NULL SELECT JSON_KEYS('');
JSON_RECORDS
¶
- Since: 0.24.0
-- returns Map<String, String>
JSON_RECORDS(json_string)
Parses json_string
as a JSON object and returns a map representing
the top-level keys and values.
Returns NULL
if the string can’t be interpreted as a JSON object,
for example, when the string is NULL
or it does not contain valid
JSON, or the JSON value is not an object.
- Examples
-- returns {d=1, a="abc", b={"c":"a"}} SELECT JSON_RECORDS('{\"a\": \"abc\", \"b\": { \"c\": \"a\" }, \"d\": 1}'); -- returns {} SELECT JSON_RECORDS('{}'); -- returns NULL SELECT JSON_RECORDS('[]'); -- returns NULL SELECT JSON_RECORDS('123'); -- returns NULL SELECT JSON_RECORDS(NULL); -- returns NULL SELECT JSON_RECORDS('abc');
JSON_ITEMS
¶
- Since: 0.29.0
-- returns Array<String>
JSON_ITEMS(json_string)
Given a string with JSON array, converts it to a ksqlDB array of JSON strings.
Returns NULL
if the string can’t be interpreted as a JSON array, for
example, when the string is NULL
or it does not contain valid JSON,
or the JSON value is not an array.
- Examples
-- returns ["{\"type\": \"A\", \"ts\": \"2022-01-27\"}", "{\"type\": \"B\", \"ts\": \"2022-05-18\"}"] SELECT JSON_ITEMS('[{\"type\": \"A\", \"ts\": \"2022-01-27\"}, {\"type\": \"B\", \"ts\": \"2022-05-18\"}]'); -- returns [] SELECT JSON_ITEMS('[]'); -- returns ["1","2","3"] SELECT JSON_ITEMS('[1, 2, 3]'); -- returns NULL SELECT JSON_ITEMS(NULL); -- returns NULL SELECT JSON_ITEMS('abc');
TO_JSON_STRING
¶
- Since: 0.24.0
-- returns a String
TO_JSON_STRING(val)
Given any ksqlDB type, returns the equivalent JSON string.
- Examples for primitives types
-- returns '1' SELECT TO_JSON_STRING(1); -- returns '15.3' SELECT TO_JSON_STRING(15.3); -- returns '"abc"' SELECT TO_JSON_STRING('abc'); -- returns 'true' SELECT TO_JSON_STRING(true); -- returns '"2021-10-11"' SELECT TO_JSON_STRING(PARSE_DATE('2021-10-11', 'yyyy-MM-dd')); -- returns '"13:25"' SELECT TO_JSON_STRING(PARSE_TIME('13:25', 'HH:mm')); -- returns '"2021-06-30T12:18:39.446"' SELECT TO_JSON_STRING(PARSE_TIMESTAMP('2021-06-31 12:18:39.446', 'yyyy-MM-dd HH:mm:ss.SSS')); -- returns 'null' SELECT TO_JSON_STRING(NULL);
- Examples for compound types
-- returns '[1, 2, 3]' SELECT TO_JSON_STRING(Array[1, 2, 3]); -- returns '{"ID":1,"NAME":"A"}' SELECT TO_JSON_STRING(Struct(id := 1, name := 'A')); -- returns '{"c": 2, "d": 4}' SELECT TO_JSON_STRING(Map('c' := 2, 'd' := 4)); -- returns '[{"JSON_KEY": 1, "JSON_VALUE": {"c": 2, "d": 3}}]' SELECT TO_JSON_STRING(Array[Struct(json_key := 1, json_value := Map('c' := 2, 'd' := 3))]);
INITCAP
¶
- Since: 0.6.0
INITCAP(col1)
Capitalizes the first letter in each word and converts all other letters to lowercase.
Words are delimited by whitespace.
INSTR
¶
- Since: 0.10.0
INSTR(string, substring, [position], [occurrence])
Returns the position of substring
in string
.
The first character is at position 1.
If position
is provided, search starts from the specified position.
A negative value for position
causes the search to work from the end
to the start of string
.
If occurrence
is provided, the position of the n-th occurrence is
returned.
If substring
is not found, the return value is 0.
- Examples
-- returns 2 SELECT INSTR('CORPORATE FLOOR', 'OR'); -- returns 5 SELECT INSTR('CORPORATE FLOOR', 'OR', 3); -- returns 14 SELECT INSTR('CORPORATE FLOOR', 'OR', 3, 2); -- returns 5 SELECT INSTR('CORPORATE FLOOR', 'OR', -3); -- returns 2b SELECT INSTR('CORPORATE FLOOR', 'OR', -3, 2); -- returns 0 SELECT INSTR('CORPORATE FLOOR', 'MISSING');
LEN
¶
- Since: 0.1.0
LEN(string)
LEN(bytes)
Returns the length of a STRING
or the number of bytes in a BYTES
value.
LPAD
¶
- Since: 0.10.0
LPAD(input, length, padding)
Pads the input string or bytes, beginning from the left, with the specified padding of the same type, until the target length is reached.
If the input is longer than length
, it is truncated.
If the padding string or byte array is empty or NULL
, or the target
length is negative, NULL
is returned.
- Examples
-- returns 'BarBFoo' SELECT LPAD('Foo', 7, 'Bar'); -- returns 'Fo' SELECT LPAD('Foo', 2, 'Bar'); -- returns 'Ba' SELECT LPAD('', 2, 'Bar'); -- returns '00123' SELECT LPAD('123', 5, '0');
MASK
¶
- Since: 0.6.0
MASK(col1, 'X', 'x', 'n', '-')
Convert a string to a masked or obfuscated version of itself.
The optional arguments following the input string to be masked are the characters to be substituted for upper-case, lower-case, numeric, and other characters of the input, respectively.
If the mask characters are omitted, the default values are applied, as shown in the following example.
-- returns "Xx-Xxxx--nnn"
MASK("My Test $123");
Set a given mask character to NULL
to prevent any masking of that
character type.
-- returns "*y *est $111"
MASK("My Test $123", '*', NULL, '1', NULL);
MASK_KEEP_LEFT
¶
- Since: 0.6.0
MASK_KEEP_LEFT(col1, numChars, 'X', 'x', 'n', '-')
Similar to the MASK
function, except that the first or left-most
numChars
characters aren’t masked in any way.
- Example
-- returns "My Txxx--nnn" SELECT MASK_KEEP_LEFT("My Test $123", 4);
MASK_KEEP_RIGHT
¶
- Since: 0.6.0
MASK_KEEP_RIGHT(col1, numChars, 'X', 'x', 'n', '-')
Similar to the MASK
function, except that the last or right-most
numChars
characters aren’t masked in any way.
- Example
-- returns "Xx-Xxxx-$123" SELECT MASK_KEEP_RIGHT("My Test $123", 4);
MASK_LEFT
¶
- Since: 0.6.0
MASK_LEFT(col1, numChars, 'X', 'x', 'n', '-')
Similar to the MASK
function, except that only the first or
left-most numChars
characters have any masking applied to them.
- Example
-- returns "Xx-Xest $123" SELECT MASK_LEFT("My Test $123", 4);
MASK_RIGHT
¶
- Since: 0.6.0
MASK_RIGHT(col1, numChars, 'X', 'x', 'n', '-')
Similar to the MASK
function, except that only the last or
right-most numChars
characters have any masking applied to them.
- Example
-- returns "My Test -nnn" SELECT MASK_RIGHT("My Test $123", 4);
REPLACE
¶
- Since: 0.6.0
REPLACE(col1, 'foo', 'bar')
Replaces all instances of a substring in a string with a new string.
REGEXP_EXTRACT
¶
- Since: 0.8.0
REGEXP_EXTRACT('.*', col1)
REGEXP_EXTRACT('(([AEIOU]).)', col1, 2)``
Extracts the first substring matched by the regular expression pattern from the input.
You can specify a capturing group number to return that specific group. If a number isn’t specified, the entire substring is returned by default.
- Example
-- returns "there" SELECT REGEXP_EXTRACT("(.*) (.*)", 'hello there', 2);
REGEXP_EXTRACT_ALL
¶
- Since: 0.10.0
REGEXP_EXTRACT_ALL('.*', col1)
REGEXP_EXTRACT_ALL('(([AEIOU]).)', col1, 2)``
Extracts all subtrings matched by the regular expression pattern from the input.
You can specify a capturing group number to return that specific group. If a number isn’t specified, the entire substring is returned by default.
- Example
-- returns ["there", "day"] SELECT REGEXP_EXTRACT("(\\w+) (\\w+)", "hello there nice day", 2);
REGEXP_REPLACE
¶
- Since: 0.10.0
REGEXP_REPLACE(col1, 'a.b+', 'bar')
Replaces all matches of a regular expression in an input string with a new string.
If either the input string, the regular expression, or the new string is
NULL
, the result is NULL
.
REGEXP_SPLIT_TO_ARRAY
¶
- Since: 0.10.0
REGEXP_SPLIT_TO_ARRAY(col1, 'a.b+')
Splits a string into an array of substrings based on a regular expression.
If there is no match, the original string is returned as the only element in the array.
If the regular expression is empty, all characters in the string are split.
If either the string or the regular expression is NULL
, a NULL
value is returned.
If the regular expression is found at the beginning or end of the string, or there are contiguous matches, an empty element is added to the array.
RPAD
¶
- Since: 0.10.0
RPAD(input, length, padding)
Pads the input string or bytes, starting from the end, with the specified padding of the same type, until the target length is reached.
If the input is longer than the specified target length, it is truncated.
If the padding string or byte array is empty or NULL
, or the target
length is negative, NULL
is returned.
- Examples
-- returns 'FooBarB' SELECT RPAD('Foo', 7, 'Bar'); -- returns 'Fo' SELECT RPAD('Foo', 2, 'Bar'); -- returns 'Ba' SELECT RPAD('', 2, 'Bar');
SPLIT
¶
- Since: 0.6.0
SPLIT(col1, delimiter)
Splits a string into an array of substrings, or bytes into an array of subarrays, based on a delimiter.
If the delimiter isn’t found, the original string or byte array is returned as the only element in the array.
If the delimiter is empty, every character in the string or byte in the array is split.
If the delimiter is found at the beginning or end of the string or bytes, or there are contiguous delimiters, an empty space is added to the array.
Returns NULL
if either parameter is NULL
.
SPLIT_TO_MAP
¶
- Since: 0.10.0
SPLIT_TO_MAP(input, entryDelimiter, kvDelimiter)
Splits a string into key-value pairs and creates a map from them.
The entryDelimiter
splits the string into key-value pairs which are
then split by kvDelimiter
.
If the same key is present multiple times in the input, the latest value for the key is returned.
Returns NULL
if the input text is NULL
.
Returns NULL
if either of the delimiters is NULL
or an empty
string.
- Example
-- returns { 'apple':'green', 'cherry':'red'} SELECT SPLIT_TO_MAP('apple':='green'/'cherry':='red', '/', ':=');
SUBSTRING
¶
Since: 0.1.0
SUBSTRING(str, pos, [len])
SUBSTRING(bytes, pos, [len])
Returns the portion of str
or bytes
that starts at pos
and
has length len
, or continues to the end of the string or bytes.
The first character or byte is at position 1.
- Example
-- returns "stre" SUBSTRING("stream", 1, 4);
TO_BYTES
¶
- Since: 0.21.0
TO_BYTES(string, encoding)
Converts a STRING
column in the specified encoding type to a
BYTES
column.
The following list shows the supported encoding types.
hex
utf8
ascii
base64
UUID
¶
- Since: 0.10.0
UUID()
UUID(bytes)
Creates a Universally Unique Identifier (UUID) generated according to RFC 4122.
A call to UUID() returns a value conforming to UUID version 4, sometimes called “random UUID”, as described in RFC 4122.
A call to UUID(bytes) returns a value conforming to UUID.
The value is a 128-bit number represented as a string of five
hexadecimal numbers, aaaaaaaa-bbbb-cccc-dddd-eeeeeeeeeeee, for
example, 237e9877-e79b-12d4-a765-321741963000
.
Bytes¶
BIGINT_FROM_BYTES
¶
- Since: 0.23.1
BIGINT_FROM_BYTES(col1, [byteOrder])
Converts a BYTES
value to a BIGINT
value according to the
specified byte order.
BYTES
must be 8 bytes long, or a NULL
value is returned.
Byte order values must be BIG_ENDIAN
or LITTLE_ENDIAN
. If
omitted, BIG_ENDIAN
is used.
A NULL
value is returned if an invalid byte order value is provided.
Example, where b
is a BYTES
value represented as a base64 string
AAAAASoF8gA=
:
-- returns 5000000000
SELECT BIGINT_FROM_BYTES(b, 'BIG_ENDIAN');
DOUBLE_FROM_BYTES
¶
- Since: 0.23.1
DOUBLE_FROM_BYTES(col1, [byteOrder])
Converts a BYTES
value to a DOUBLE
value according to the
specified byte order.
BYTES
must be 8 bytes long, or a NULL
value is returned.
Byte order values must be BIG_ENDIAN
or LITTLE_ENDIAN
. If
omitted, BIG_ENDIAN
is used.
A NULL
value is returned if an invalid byte order value is provided.
Example, where b
is a BYTES
value represented as a base64 string
QICm/ZvJ9YI=
:
-- returns 532.8738323
SELECT DOUBLE_FROM_BYTES(b, 'BIG_ENDIAN');
INT_FROM_BYTES
¶
- Since: 0.23.1
INT_FROM_BYTES(col1, [byteOrder])
Converts a BYTES
value to an INT
value according to the
specified byte order.
BYTES
must be 4 bytes long, or a NULL
value is returned.
Byte order values must be BIG_ENDIAN
or LITTLE_ENDIAN
. If
omitted, BIG_ENDIAN
is used.
A NULL
value is returned if an invalid byte order value is provided.
Example, where b_big
is a BYTES
value represented as a base64
string AAAH5Q==
:
-- returns 2021
SELECT INT_FROM_BYTES(b, 'BIG_ENDIAN');
TO_BYTES
¶
- Since: 0.21.0
TO_BYTES(col1, encoding)
Converts a STRING
value in the specified encoding to BYTES
.
The following list shows the supported encoding types.
hex
utf8
ascii
base64
Nulls¶
COALESCE
¶
- Since: 0.9.0
COALESCE(a, b, c, d)
Returns the first parameter that is not NULL
. All parameters must be
of the same type.
If the parameter is a complex type, for example, ARRAY
or
STRUCT
, the contents of the complex type are not inspected. The
behaviour is the same: the first NOT NULL
element is returned.
IFNULL
¶
- Since: 0.9.0
IFNULL(expression, altValue)
If expression
is NULL
, returns altValue
; otherwise, returns
expression
.
If expression
evaluates to a complex type, for example, ARRAY
or
STRUCT
, the contents of the complex type are not inspected.
NULLIF
¶
- Since: 0.19.0
NULLIF(expression1, expression2)
Returns NULL
if expression1
is equal to expression2
;
otherwise, returns expression1
.
If expression
evaluates to a complex type, for example, ARRAY
or
STRUCT
, the contents of the complex type are not inspected.
Date and time¶
CONVERT_TZ
¶
- Since: 0.17.0
CONVERT_TZ(col1, 'from_timezone', 'to_timezone')
Converts a TIMESTAMP
value from from_timezone
to
to_timezone
.
The from_timezone
and to_timezone
parameters are
java.util.TimeZone
ID formats, for example:
- “UTC”
- “America/Los_Angeles”
- “PDT”
- “Europe/London”
For more information on timestamp formats, see DateTimeFormatter.
DATEADD
¶
- Since: 0.20.0
DATEADD(unit, interval, col0)
Adds an interval to a date.
Intervals are defined by an integer value and a supported time unit.
DATESUB
¶
- Since: 0.20.0
DATESUB(unit, interval, col0)
Subtracts an interval from a date.
Intervals are defined by an integer value and a supported time unit.
FORMAT_DATE
¶
- Since: 0.20.0
FORMAT_DATE(date, 'yyyy-MM-dd')
Converts a DATE
value into a string that represents the date in the
specified format.
You can escape single-quote characters in the timestamp format by using
two successive single quotes, ''
, for example:
'yyyy-MM-dd''T'''
.
FORMAT_TIME
¶
- Since: 0.20.0
FORMAT_TIME(time, 'HH:mm:ss.SSS')
Converts a TIME
value into the string representation of the time in
the given format.
You can escape single-quote characters in the time format by using two
successive single quotes, ''
, for example: '''T''HH:mm:ssX'
.
For more information on time formats, see DateTimeFormatter.
FORMAT_TIMESTAMP
¶
- Since: 0.17.0
FORMAT_TIMESTAMP(timestamp, 'yyyy-MM-dd HH:mm:ss.SSS' [, TIMEZONE])
Converts a TIMESTAMP
value into the string representation of the
timestamp in the specified format.
You can escape single-quote characters in the timestamp format by using
two successive single quotes, ''
, for example:
'yyyy-MM-dd''T''HH:mm:ssX'
.
The optional TIMEZONE
parameter is a java.util.TimeZone
ID
format, for example:
- “UTC”
- “America/Los_Angeles”
- “PDT”
- “Europe/London”
Note
To use the FORMAT_TIMESTAMP
function with a BIGINT millisecond timestamp
parameter, convert the millisecond value to a TIMESTAMP
by using the
FROM_UNIXTIME
function, for example:
FORMAT_TIMESTAMP(FROM_UNIXTIME(unix_timestamp))
For more information on timestamp formats, see DateTimeFormatter.
FROM_DAYS
¶
- Since: 0.20.0
FROM_DAYS(days)
Converts an INT
number of days since epoch to a DATE
value.
FROM_UNIXTIME
¶
- Since: 0.17.0
FROM_UNIXTIME(milliseconds)
Converts a BIGINT
millisecond timestamp value into a TIMESTAMP
value.
PARSE_DATE
¶
- Since: 0.20.0
PARSE_DATE(col1, 'yyyy-MM-dd')
Converts a string representation of a date in the specified format into
a DATE
value.
You can escape single-quote characters in the timestamp format by using
two successive single quotes, ''
, for example:
'yyyy-MM-dd''T'''
.
PARSE_TIME
¶
- Since: 0.20.0
PARSE_TIME(col1, 'HH:mm:ss.SSS')
Converts a string value in the specified format into a TIME
value.
You can escape single-quote characters in the time format by using
successive single quotes, ''
, for example: '''T''HH:mm:ssX'
.
For more information on time formats, see DateTimeFormatter.
PARSE_TIMESTAMP
¶
- Since: 0.17.0
PARSE_TIMESTAMP(col1, 'yyyy-MM-dd HH:mm:ss.SSS' [, TIMEZONE])
Converts a string value in the given format into the TIMESTAMP
value.
You can escape single-quote characters in the timestamp format by using
successive single quotes, ''
, for example:
'yyyy-MM-dd''T''HH:mm:ssX'
.
The optional TIMEZONE
parameter is a java.util.TimeZone
ID
format, for example:
- “UTC”
- “America/Los_Angeles”
- “PDT”
- “Europe/London”
TIMEADD
¶
- Since: 0.20.0
TIMEADD(unit, interval, COL0)
Adds an interval to a TIME
.
Intervals are defined by an integer value and a supported time unit.
TIMESUB
¶
- Since: 0.20.0
TIMESUB(unit, interval, COL0)
Subtracts an interval from a TIME
.
Intervals are defined by an integer value and a supported time unit.
TIMESTAMPADD
¶
- Since: 0.17.0
TIMESTAMPADD(unit, interval, COL0)
Adds an interval to a TIMESTAMP
.
Intervals are defined by an integer value and a supported time unit.
TIMESTAMPSUB
¶
- Since: 0.17.0
TIMESTAMPSUB(unit, interval, COL0)
Subtracts an interval from a TIMESTAMP
.
Intervals are defined by an integer value and a supported time unit.
UNIX_DATE
¶
- Since: 0.6.0
UNIX_DATE([date])
If UNIX_DATE
is called with the date parameter, the function returns
the DATE
value as an INTEGER
value representing the number of
days since 1970-01-01
.
If the date
parameter is not provided, the function returns an
integer representing days since 1970-01-01
.
Important
The returned integer may differ depending on the local time of different ksqlDB Server instances.
UNIX_TIMESTAMP
¶
- Since: 0.6.0
UNIX_TIMESTAMP([timestamp])
If UNIX_TIMESTAMP
is called with the timestamp parameter, the
function returns the TIMESTAMP
value as a BIGINT
value
representing the number of milliseconds since
1970-01-01T00:00:00 UTC
.
If the timestamp
parameter is not provided, the function returns the
current UNIX timestamp in milliseconds, represented as a BIGINT
.
Important
The returned BIGINT
may differ depending on the local time of different
ksqlDB Server instances.
URLs¶
All ksqlDB URL functions assume URI syntax defined in RFC 39386. For more information on the structure of a URI, including definitions of the various components, see Section 3 of the RFC.
For encoding and decoding, ksqlDB uses the
application/x-www-form-urlencoded
convention.
URL_DECODE_PARAM
¶
- Since: 0.6.0
URL_DECODE_PARAM(col1)
Unescapes the URL-param-encoded
value in col1
.
This is the inverse of the URL_ENCODE_PARAM
function.
- Example
-- returns "url encoded" SELECT URL_DECODE_PARAM("url%20encoded");
URL_ENCODE_PARAM
¶
- Since: 0.6.0
URL_ENCODE_PARAM(col1)
Escapes the value of col1
such that it can safely be used in URL
query parameters.
Note
URL_ENCODE_PARAM
is not the same as encoding a value for use in the
path portion of a URL.
- Example
-- returns "url%20encoded" SELECT URL_ENCODE_PARAM("url encoded");
URL_EXTRACT_FRAGMENT
¶
- Since: 0.6.0
URL_EXTRACT_FRAGMENT(url)
Extracts the fragment portion of the specified value.
Returns NULL
if url
is not a valid URL or if the fragment
doesn’t exist.
All encoded values are decoded.
- Examples
-- returns "frag" SELECT URL_EXTRACT_FRAGMENT("http://test.com#frag"); -- returns "frag space" SELECT URL_EXTRACT_FRAGMENT("http://test.com#frag%20space");
URL_EXTRACT_HOST
¶
- Since: 0.6.0
URL_EXTRACT_HOST(url)
Extracts the host-name portion of the specified value.
Returns NULL
if url
is not a valid URI according to RFC-2396.
- Example
-- returns "test.com" SELECT URL_EXTRACT_HOST("http://test.com:8080/path");
URL_EXTRACT_PARAMETER
¶
- Since: 0.6.0
URL_EXTRACT_PARAMETER(url, parameter_name)
Extracts the value of the requested parameter from the query-string of
url
.
Returns NULL
if the parameter is not present, has no value specified
for it in the query string, or url
is not a valid URI.
The function encodes the parameter and decodes the output.
To get all parameter values from a URL as a single string, use
URL_EXTRACT_QUERY.
- Examples
-- returns "c d" SELECT URL_EXTRACT_PARAMETER("http://test.com?a%20b=c%20d", "a b"); -- returns "bar" SELECT URL_EXTRACT_PARAMETER("http://test.com?a=foo&b=bar", "b");
URL_EXTRACT_PATH
¶
- Since: 0.6.0
URL_EXTRACT_PATH(url)
Extracts the path from url
.
Returns NULL
if url
is not a valid URI but returns an empty
string if the path is empty.
- Examples
-- returns "path/to" SELECT URL_EXTRACT_PATH("http://test.com/path/to#a");
URL_EXTRACT_PORT
¶
- Since: 0.6.0
URL_EXTRACT_PORT(url)
Extracts the port number from url
.
Returns NULL
if url
is not a valid URI or does not contain an
explicit port number.
- Example
-- returns "8080" SELECT URL_EXTRACT_PORT("http://localhost:8080/path");
URL_EXTRACT_PROTOCOL
¶
- Since: 0.6.0
URL_EXTRACT_PROTOCOL(url)
Extracts the protocol from url
.
Returns NULL
if url
is an invalid URI or has no protocol.
- Example
-- returns "http" SELECT URL_EXTRACT_PROTOCOL("http://test.com?a=foo&b=bar");
URL_EXTRACT_QUERY
¶
- Since: 0.6.0
URL_EXTRACT_QUERY(url)
Extracts the decoded query-string portion of url
.
Returns NULL
if no query-string is present or url
is not a valid
URI.
- Example
-- returns "a=foo bar&b=baz" SELECT URL_EXTRACT_QUERY("http://test.com?a=foo%20bar&b=baz");
Deprecated¶
DATETOSTRING
¶
- Since: 0.7.1
Deprecated since 0.20.0 (use FORMAT_DATE)
DATETOSTRING(START_DATE, 'yyyy-MM-dd')
Converts an integer representation of a date into a string representing
the date in the given format. Single quotes in the timestamp format can
be escaped with two successive single quotes, ''
, for example:
'yyyy-MM-dd''T'''
. The integer represents days since epoch matching
the encoding used by Connect dates.
STRINGTODATE
¶
- Since: 0.7.1
Deprecated since 0.20.0 (use PARSE_DATE)
STRINGTODATE(col1, 'yyyy-MM-dd')
Converts a string representation of a date in the given format into an
integer representing days since epoch. Single quotes in the timestamp
format can be escaped with two successive single quotes, ''
, for
example: 'yyyy-MM-dd''T'''
.
STRINGTOTIMESTAMP
¶
- Since: 0.7.1
Deprecated since 0.17.0 (use PARSE_TIMESTAMP)
STRINGTOTIMESTAMP(col1, 'yyyy-MM-dd HH:mm:ss.SSS' [, TIMEZONE])
Converts a string value in the given format into the BIGINT value
that represents the millisecond timestamp. Single quotes in the
timestamp format can be escaped with two successive single quotes,
''
, for example: 'yyyy-MM-dd''T''HH:mm:ssX'
.
TIMEZONE is an optional parameter and it is a java.util.TimeZone
ID
format, for example: “UTC”, “America/Los_Angeles”, “PDT”,
“Europe/London”. For more information on timestamp formats, see
DateTimeFormatter.
TIMESTAMPTOSTRING
¶
- Since: 0.7.1
Deprecated since 0.17.0 (use FORMAT_TIMESTAMP)
TIMESTAMPTOSTRING(ROWTIME, 'yyyy-MM-dd HH:mm:ss.SSS' [, TIMEZONE])
Converts a BIGINT millisecond timestamp value into the string
representation of the timestamp in the given format. Single quotes in
the timestamp format can be escaped with two successive single quotes,
''
, for example: 'yyyy-MM-dd''T''HH:mm:ssX'
.
TIMEZONE is an optional parameter, and it is a java.util.TimeZone
ID
format, for example, “UTC”, “America/Los_Angeles”, “PDT”, or
“Europe/London”. For more information on timestamp formats, see
DateTimeFormatter.
Note
To use the `FORMAT_TIMESTAMP
<#format_timestamp>`__ function with a
BIGINT millisecond timestamp parameter, convert the millisecond value to a
TIMESTAMP
by using the FROM_UNIXTIME
function, for example:
FORMAT_TIMESTAMP(FROM_UNIXTIME(unix_timestamp))