# 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
abs(-12) = 12
cos(1.5) = 0.0707372016677029
cosh(1.5) = 2.352409615243247
acos(0.0707372016677029) = 1.5
acosh(2.352409615243247) = 1.5
sin(0.5) = 0.479425538604203
sinh(0.5) = 0.5210953054937474
asin(0.479425538604203) = 0.5
asinh(0.5210953054937474) = 0.5
tan(1.4) = 5.797883715482887
tanh(1.4) = 0.8853516482022625
atan(5.797883715482887) = 1.4
atanh(0.8853516482022625) = 1.4000000000000001
ceil(1.34) = 2
floor(1.34) = 1
round(1.34) = 1
round(1.54) = 2
exp(10) = 22026.465794806718
power(2, 10) = 1024
sqrt(2) = 1.4142135623730951
fmod(-32, 5) = -2
log10(1000) = 3
log2(1024) = 10
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23

# 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.
is_null(undefined) = true
is_not_null(1) = true
is_str(1) = false
is_str('val') = true
is_bool(true) = true
is_int(1) = true
is_float(1) = false
is_float(1.234) = true
is_num(2.3) = true
is_num('val') = false
1
2
3
4
5
6
7
8
9
10

# 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
mapConvert data to Map typeDataMap type data. Failure to convert will cause SQL matching to fail
str(1234) = '1234'
str_utf8(1234) = '1234'
bool('true') = true
int('1234') = 1234
float('3.14') = 3.14
1
2
3
4
5

# 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
lower('AbC') = 'abc'
lower('abc') = 'abc'

upper('AbC') = 'ABC'` `lower('ABC') = 'ABC'

trim(' hello  ') = 'hello'

ltrim(' hello  ') = 'hello  '

rtrim(' hello  ') = ' hello'

reverse('hello') = 'olleh'

strlen('hello') = 5

substr('abcdef', 2) = 'cdef'
substr('abcdef', 2, 3) = 'cde'

split('a/b/ c', '/') = ['a', 'b', ' c']
split('a/b/ c', '/', 'leading') = ['a', 'b/ c']
split('a/b/ c', '/', 'trailing') = ['a/b', ' c']

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22

# 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"
map_get('a', json_decode( '{ "a" : 1 }' )) = 1
map_get('b', json_decode( '{ "a" : 1 }' ), 2) = 2
map_get('a', map_put('a', 2, json_decode( '{ "a" : 1 }' ))) = 2
1
2
3

# 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
nth(2, [1,2,3,4]) = 2
length([1,2,3,4]) = 4
sublist(3, [1,2,3,4]) = [1,2,3,4]
sublist(1,2,[1,2,3,4]) = [1, 2]
first([1,2,3,4]) = 1
last([1,2,3,4]) = 4
contains(2, [1,2,3,4]) = true
1
2
3
4
5
6
7

# Hash function

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

# 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
bin2hexstr(gzip('hello world')) = '1F8B0800000000000003CB48CDC9C95728CF2FCA49010085114A0D0B000000'
gunzip(hexstr2bin('1F8B0800000000000003CB48CDC9C95728CF2FCA49010085114A0D0B000000')) = 'hello world'

bin2hexstr(zip('hello world')) = 'CB48CDC9C95728CF2FCA490100'
unzip(hexstr2bin('CB48CDC9C95728CF2FCA490100')) = 'hello world'

bin2hexstr(zip_compress('hello world')) = '789CCB48CDC9C95728CF2FCA4901001A0B045D'
zip_uncompress(hexstr2bin('789CCB48CDC9C95728CF2FCA4901001A0B045D')) = 'hello world'
1
2
3
4
5
6
7
8

# 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
subbits('abc', 8) = 97
subbits('abc', 9, 8) = 98
subbits('abc', 17, 8) = 99
subbits('abc', 9, 16, 'integer', 'signed', 'big') = 25187
subbits('abc', 9, 16, 'integer', 'signed', 'little') = 25442
1
2
3
4
5

# 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
schema_encodeEncode according to schema. This requires the schema registry1. 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. This requires the schema registry1. 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
bin2hexstrBinary to Hex StringThe binaryThe hex string
hexstr2binBinary to Hex StringThe hex stringThe binary
base64_encode('some val') = 'c29tZSB2YWw='
base64_decode('c29tZSB2YWw=') = 'some val'
json_encode(json_decode( '{ "a" : 1 }' )) = '{"a":1}'
bin2hexstr(hexstr2bin('ABEF123')) = 'ABEF123'
1
2
3
4