Functions available in SQL statements

Mathematical functions

function namePurposeparameterReturned value
absAbsolute valueOperandabsolute value
cosCosineOperandCosine value
coshHyperbolic cosineOperandHyperbolic cosine value
acosInverse cosineOperandInverse cosine value
acoshInverse hyperbolic cosineOperandInverse hyperbolic cosine value
sinSineOperandSine value
sinhHyperbolic sineOperandHyperbolic sine value
asinArcsineOperandArcsine value
asinhinverse hyperbolic sineOperandinverse hyperbolic sine value
tantangentOperandtangent value
tanhHyperbolic tangentOperandHyperbolic tangent value
atanArc tangentOperandArc tangent value
atanhInverse hyperbolic tangentOperandInverse hyperbolic tangent value
ceilRound upOperandInteger value
floorRound downOperandInteger value
roundroundingOperandInteger value
expExponentiationOperandX power of e
powerExponential operation1. Left operand x
2. Right operand y
Y power of X
sqrtSquare root operationOperandSquare root
fmodFloating point modulus function1. left Operand
2.right Operand
module
logLogarithm to eOperandvalue
log10Logarithm to 10Operandvalue
log2Logarithm to 2Operandvalue
  1. abs(-12) = 12
  2. cos(1.5) = 0.0707372016677029
  3. cosh(1.5) = 2.352409615243247
  4. acos(0.0707372016677029) = 1.5
  5. acosh(2.352409615243247) = 1.5
  6. sin(0.5) = 0.479425538604203
  7. sinh(0.5) = 0.5210953054937474
  8. asin(0.479425538604203) = 0.5
  9. asinh(0.5210953054937474) = 0.5
  10. tan(1.4) = 5.797883715482887
  11. tanh(1.4) = 0.8853516482022625
  12. atan(5.797883715482887) = 1.4
  13. atanh(0.8853516482022625) = 1.4000000000000001
  14. ceil(1.34) = 2
  15. floor(1.34) = 1
  16. round(1.34) = 1
  17. round(1.54) = 2
  18. exp(10) = 22026.465794806718
  19. power(2, 10) = 1024
  20. sqrt(2) = 1.4142135623730951
  21. fmod(-32, 5) = -2
  22. log10(1000) = 3
  23. log2(1024) = 10

Data type judgment function

Function namePurposeparameterReturned value
is_nullJudge if the variable is nullDataBoolean data.if it is empty (undefined), return true, otherwise return false
is_not_nullJudge if the variable is not nullDataBoolean data.if it is empty (undefined), return false, otherwise return true
is_strJudge whether the variable is String typeDataBoolean data.
is_boolJudge if the variable is Boolean typeDataBoolean data.
is_intJudge whether the variable is Integer typeDataBoolean data.
is_floatJudge whether the variable is Float typeDataBoolean data.
is_numJudge whether the variable is a numeric type, including Integer and Float typesDataBoolean data.
is_mapJudge whether the variable is Map typeDataBoolean data.
is_arrayJudge whether the variable is Array typeDataBoolean data.
  1. is_null(undefined) = true
  2. is_not_null(1) = true
  3. is_str(1) = false
  4. is_str('val') = true
  5. is_bool(true) = true
  6. is_int(1) = true
  7. is_float(1) = false
  8. is_float(1.234) = true
  9. is_num(2.3) = true
  10. is_num('val') = false

Data type conversion function

function namepurposeparameterreturned value
strConvert data to String typeDataData of type String. Failure to convert will cause SQL matching to fail
str_utf8Convert data to UTF-8 String typeDataUTF-8 String type data. Failure to convert will cause SQL matching to fail
boolConvert data to Boolean typeDataBoolean data. Failure to convert will cause SQL matching to fail
intConvert data to integer typeDataInteger type data. Failure to convert will cause SQL matching to fail
floatConvert data to floating typeDataFloating type data. Failure to convert will cause SQL matching to fail
float2strConvert a float to string using the given precision1. Float Number 2. PrecisionString
mapConvert data to Map typeDataMap type data. Failure to convert will cause SQL matching to fail
  1. str(1234) = '1234'
  2. str_utf8(1234) = '1234'
  3. bool('true') = true
  4. int('1234') = 1234
  5. float('3.14') = 3.14
  6. float2str(20.2, 10) = '20.2'
  7. float2str(20.2, 17) = '20.19999999999999928'

String functions

Function namePurposeparameterreturned value
lowerconvert to lowercaseinput stringLowercase string
upperconvert to uppercaseinput stringuppercase string
trimRemove left and right spaceinput stringoutput string
ltrimRemove the left spaceinput stringoutput string
rtrimRemove the right spaceinput stringoutput string
reverseString inversioninput stringoutput string
strlenstring lengthinput stringInteger value
substrTake a substring of characters1. input string
2. Start position. Note: Subscripts start at 1
substring
substringTake a substring of characters1. input string
2. Start position
3. End position. Note: Subscripts start at 1
substring
splitString splitting1. input string
2. split string
Split string array
splitString splitting1. input string
2. split string
3. Find the first separator on the left or right, optional value is ‘leading’ or ‘trailing’
Split string array
splitsplit string1. input string
2. split string
3. Find the first separator on the left or right, optional value is ‘leading’ or ‘trailing’
Split string array
  1. lower('AbC') = 'abc'
  2. lower('abc') = 'abc'
  3. upper('AbC') = 'ABC'` `lower('ABC') = 'ABC'
  4. trim(' hello ') = 'hello'
  5. ltrim(' hello ') = 'hello '
  6. rtrim(' hello ') = ' hello'
  7. reverse('hello') = 'olleh'
  8. strlen('hello') = 5
  9. substr('abcdef', 2) = 'cdef'
  10. substr('abcdef', 2, 3) = 'cde'
  11. split('a/b/ c', '/') = ['a', 'b', ' c']
  12. split('a/b/ c', '/', 'leading') = ['a', 'b/ c']
  13. split('a/b/ c', '/', 'trailing') = ['a/b', ' c']

Map function

function namepurposeparameterreturned value
map_getTake the value of a Key in the Map, or return a null value if failed1. Key
2. Map
The value of a Key in the Map. Support nested keys, such as “a.b.c”
map_getTake the value of a Key in the Map, if failed, return the specified default value1. Key
2. Map
3. Default Value
The value of a Key in the Map. Support nested keys, such as “a.b.c”
map_putInsert value into Map1. Key
2. Value
3. Map
The inserted Map. Support nested keys, such as “a.b.c”
  1. map_get('a', json_decode( '{ "a" : 1 }' )) = 1
  2. map_get('b', json_decode( '{ "a" : 1 }' ), 2) = 2
  3. map_get('a', map_put('a', 2, json_decode( '{ "a" : 1 }' ))) = 2

Array function

function namepurposeparameterreturned value
nthTake the nth element, and subscripts start at 1Original arrayNth element
lengthGet the length of an arrayOriginal arraythe length of an array
sublistTake a sub-array of length len starting from the first element. Subscripts start at 11. length len
2. Original array
sub-array
sublistTake a sub-array of length len starting from the nth element. Subscripts start at 11. start position n
2. length len
3. Original array
sub-array
firstTake the first element. Subscripts start at 1Original array1st element
lasttake the last elementOriginal arraythe last element
containsDetermine whether the data is in the array1. data
2. Original array
Boolean value
  1. nth(2, [1,2,3,4]) = 2
  2. length([1,2,3,4]) = 4
  3. sublist(3, [1,2,3,4]) = [1,2,3,4]
  4. sublist(1,2,[1,2,3,4]) = [1, 2]
  5. first([1,2,3,4]) = 1
  6. last([1,2,3,4]) = 4
  7. contains(2, [1,2,3,4]) = true

Hash function

function namepurposeparameterreturned value
md5evaluate MD5dataMD5 value
shaevaluate SHAdataSHA value
sha256evaluate SHA256dataSHA256 value
  1. md5('some val') = '1b68352b3e9c2de52ffd322e30bffcc4'
  2. sha('some val') = 'f85ba28ff5ea84a0cbfa118319acb0c5e58ee2b9'
  3. sha256('some val') = '67f97635d8a0e064f60ba6e8846a0ac0be664f18f0c1dc6445cd3542d2b71993'

Compresses and Uncompresses functions

FunctionPurposeParametersReturned value
gzipCompresses data with gz headers and checksum.Raw binary dataCompressed binary data
gunzipUncompresses data with gz headers and checksum.Compressed binary dataRaw binary data
zipCompresses data without zlib headers and checksum.Raw binary dataCompressed binary data
unzipUncompresses data without zlib headers and checksum.Compressed binary dataRaw binary data
zip_compressCompresses data with zlib headers and checksum.Raw binary dataCompressed binary data
zip_uncompressUncompresses data with zlib headers and checksum.Compressed binary dataRaw binary data
  1. bin2hexstr(gzip('hello world')) = '1F8B0800000000000003CB48CDC9C95728CF2FCA49010085114A0D0B000000'
  2. gunzip(hexstr2bin('1F8B0800000000000003CB48CDC9C95728CF2FCA49010085114A0D0B000000')) = 'hello world'
  3. bin2hexstr(zip('hello world')) = 'CB48CDC9C95728CF2FCA490100'
  4. unzip(hexstr2bin('CB48CDC9C95728CF2FCA490100')) = 'hello world'
  5. bin2hexstr(zip_compress('hello world')) = '789CCB48CDC9C95728CF2FCA4901001A0B045D'
  6. zip_uncompress(hexstr2bin('789CCB48CDC9C95728CF2FCA4901001A0B045D')) = 'hello world'

Bit functions

FunctionPurposeParametersReturned value
subbitsGet a given length of bits from the beginning of a binary, and then convert it to an unsigned integer (big-endian).1. The binary
2. The length of bits to get
The unsigned integer
subbitsGet a given length of bits start from the specified offset of a binary, and then convert it to an unsigned integer (big-endian). Offsets are start from 1.1. The binary
2. The offset
3. The length of bits to get
The unsigned integer
subbitsGet a given length of bits start from the specified offset of a binary, and then convert it to a data type according to the arguments provided. Offsets are start from 1.1. The binary
2. The offset
3. The length of bits to get
4. Data Type, can be one of ‘integer’, ‘float’, ‘bits’
5. Signedness, only works for integers, can be one of ‘unsigned’, ‘signed’,
6. Endianness, only works for integers, can be one of ‘big’, ‘little’
The data got from the binary
  1. subbits('abc', 8) = 97
  2. subbits('abc', 9, 8) = 98
  3. subbits('abc', 17, 8) = 99
  4. subbits('abc', 9, 16, 'integer', 'signed', 'big') = 25187
  5. subbits('abc', 9, 16, 'integer', 'signed', 'little') = 25442

Decoding and encoding functions

FunctionPurposeParametersReturned value
base64_encodeBASE64 encodeThe binary to be encodedThe encoded base64-formatted string
base64_decodeBASE64 decodeThe base64-formatted string to be decodedThe decoded binary
json_encodeJSON encodeThe data to be encodedThe JSON string
json_decodeJSON decodeThe JSON string to be decodedThe decoded data
bin2hexstrBinary to Hex StringThe binaryThe hex string
hexstr2binBinary to Hex StringThe hex stringThe binary
  1. base64_encode('some val') = 'c29tZSB2YWw='
  2. base64_decode('c29tZSB2YWw=') = 'some val'
  3. json_encode(json_decode( '{ "a" : 1 }' )) = '{"a":1}'
  4. bin2hexstr(hexstr2bin('ABEF123')) = 'ABEF123'
FunctionPurposeParametersReturned value
schema_encodeEncode according to schema. The schema should be created before using this function1. The Schema ID defined by schema registry 2. The data to be encoded 3..N. The remaining arguments according to the schema typeThe encoded data
schema_decodeDecode according to schema. The schema should be created before using this function1. The Schema ID defined by schema registry 2. The data to be decoded 3..N. The remaining arguments according to the schema typeThe decoded data

For examples of schema_encode() and schema_decode(), see schema registry {% endemqxee %}

Time and date functions

FunctionPurposeParametersReturned value
now_timestampReturn the unix epoch of now in second-The unix epoch
now_timestampReturn the unix epoch of now, in given time unit1. The time unitThe unix epoch
now_rfc3339Create a RFC3339 time string of now in second-The time string of format RFC3339
now_rfc3339Create a RFC3339 time string of now, in given time unit1. The time unitThe time string of format RFC3339
unix_ts_to_rfc3339Convert an unix epoch (in second) to RFC3339 time string1. The unix epoch in secondThe time string of format RFC3339
unix_ts_to_rfc3339Convert an unix epoch to RFC3339 time string, using the given time unit1. The unix epoch
2. The time unit
The time string of format RFC3339
rfc3339_to_unix_tsConvert a RFC3339 time string (in second) to unix epoch1. The time string of format RFC3339The unix epoch
rfc3339_to_unix_tsConvert a RFC3339 time string to unix epoch, using the given time unit1. The time string of format RFC3339
2. The time unit
The unix epoch
format_dateTimestamp to formatted time1. The time unit (refer to The time unit)
2. The time offset (refer to time offset definition)
3. The date format (refer to time string codec format)
4. The timestamp (optional parameter, default is current time)
Formatted time
date_to_unix_tsFormatted time to timestamp1. The time unit (refer to the following table for definition)
2. The time offset (optional, when not filled, use the time offset in the formatted time string, refer to the time offset definition)
3. The date format (refer to time string codec format)
4. The formatted time string
The unix epoch

The time unit

NamePrecisionExample
secondsecond1653557821
millisecondmillisecond1653557852982
microsecondmicrosecond1653557892926417
nanosecondnanosecond1653557916474793000

Time string format

PlaceholderDefinitionRange
%Yyear0000 - 9999
%mmonth01 - 12
%dday01 - 31
%Hhour00 - 12
%Mminute00 - 59
%Ssecond01 - 59
%Nnanosecond000000000 - 999999999
%3Nmillisecond000000 - 999999
%6Nmicrosecond000 - 000
%ztime offset [+|-]HHMM-1159 to +1159
%:ztime offset [+|-]HH:MM-11:59 to +11:59
%::ztime offset [+|-]HH:MM:SS-11:59:59 to +11:59:59

The time offset

OffsetDefinitionExamples
zUTC Zulu Time+00:00
ZUTC Zulu Time. Same as z+00:00
localSystem TimeAutomatic
Beijing +08:00
Zulu +00:00
Stockholm, Sweden +02:00
Los Angeles -08:00
[+|-]HHMM%zBeijing +0800
Zulu +0000
Stockholm, Sweden +0200
Los Angeles -0800
[+|-]HH:MM%:zBeijing +08:00
Zulu +00:00
Stockholm, Sweden +02:00
Los Angeles -08:00
[+|-]HH:MM:SS%::zBeijing +08:00:00
Zulu +00:00:00
Stockholm, Sweden +02:00:00
Los Angeles -08:00:00
integer()SecondsBeijing 28800
Zulu 0
Stockholm, Sweden 7200
Los Angeles -28800
  1. now_timestamp() = 1650874276
  2. now_timestamp('millisecond') = 1650874318331
  3. now_rfc3339() = '2022-04-25T16:08:41+08:00'
  4. now_rfc3339('millisecond') = '2022-04-25T16:10:10.652+08:00'
  5. unix_ts_to_rfc3339(1650874276) = '2022-04-25T16:11:16+08:00'
  6. unix_ts_to_rfc3339(1650874318331, 'millisecond') = '2022-04-25T16:11:58.331+08:00'
  7. rfc3339_to_unix_ts('2022-04-25T16:11:16+08:00') = 1650874276
  8. rfc3339_to_unix_ts('2022-04-25T16:11:58.331+08:00', 'millisecond') = 1650874318331
  9. format_date('second', '+0800', '%Y-%m-%d %H:%M:%S%:z', 1653561612) = '2022-05-26 18:40:12+08:00'
  10. format_date('second', 'local', '%Y-%m-%d %H:%M:%S%:z') = "2022-05-26 18:48:01+08:00"
  11. format_date('second', 0, '%Y-%m-%d %H:%M:%S%:z') = '2022-05-26 10:42:41+00:00'
  12. date_to_unix_ts('second', '%Y-%m-%d %H:%M:%S%:z', '2022-05-26 18:40:12+08:00') = 1653561612
  13. date_to_unix_ts('second', 'local', '%Y-%m-%d %H-%M-%S', '2022-05-26 18:40:12') = 1653561612
  14. date_to_unix_ts('second', '%Y-%m-%d %H-%M-%S', '2022-05-26 10:40:12') = 1653561612
FunctionPurposeParametersReturned value
mongo_dateCreate a mongodb ISODate type of now-the ISODate
mongo_dateCreate a mongodb ISODate type from the given unix epoch in millisecond1. unix epoch in millisecondthe ISODate
mongo_dateCreate a mongodb ISODate type from the given unix epoch in given time unit1. unix epoch 2. time unit, can be one of ‘second’, ‘millisecond’, ‘microsecond’ or ‘nanosecond’the ISODate

The time unit can be one of ‘second’, ‘millisecond’, ‘microsecond’ or ‘nanosecond’.

  1. mongo_date() = 'ISODate("2012-12-19T06:01:17.171Z")'
  2. mongo_date(timestamp) = 'ISODate("2012-12-19T06:01:17.171Z")'
  3. mongo_date(timestamp, 'millisecond') = 'ISODate("2012-12-19T06:01:17.171Z")'