Functions

This topic lists functions, available in Skyvia.

General Functions

General functions allow you to create NULL values, check column values if they are NULL, and replace NULL values.

Function Parameters Description
isnull value - any type Checks whether the specified value is a NULL value.
null no arguments or value argument of any type Called with no arguments, returns a NULL value. Called with an argument, returns a NULL value of the argument type.
replace_null arg1 - any type,
arg2 - any type		 If the first argument is not NULL, returns the first argument; otherwise, returns the second argument.

Data Type Conversion Functions

Data type conversion functions allow converting values from one data type to another. They have overloads without parameters and with a paramter of types, conversion from which is supported. The overloads without parameters return a zero or empty values of the specified type. Thus, bool() returns false, int() or any other numeric type function returns 0, and string() returns an empty string.

Function Parameters Description
bool Accepts booloean, string, or numeric values. If an argument is false or 0, returns false. If an argument is true or a non-zero number, retuns true. It also accepts strings ‘true’ or ‘false’ (case insensitive) or strings that can be converted to a number. Other strings would cause an error.
currency Accepts boolean, string, or numeric values. Converts values to decimal type.
datetime Accepts datetime, string, or datetimeoffset values. Converts values to datetime type.
datetimeoffset Accepts datetime, string, datetimeoffset, or int8 values. Converts values to datetimeoffset type.
decimal Accepts boolean, string, or numeric values. Converts values to decimal type.
float4 Accepts boolean, string, or numeric values. Converts values to float4 type.
float8 Accepts boolean, string, or numeric values. Converts values to float8 type.
guid Accepts guid or string values Converts values to guid type.
int1 Accepts boolean, string, or numeric values. Converts values to int1 type.
int2 Accepts boolean, string, or numeric values. Converts values to int2 type.
int4 Accepts boolean, string, or numeric values. Converts values to int4 type.
int8 Accepts boolean, string, or numeric values. Converts values to int8 type.
string Accepts any values Converts values to strings.
time Accepts time, numeric, or string values Converts values to time type.

String Functions

String and binary functions perform actions over string values and also encode binary values into strings and vice versa.

Function Parameters Description
codepoint input - string Returns the Unicode code point of the leftmost character of the argument.
concat arg1 - string,
arg2 - string		 Concatenates two strings.
decode_base64 value - string Converts the specified string, which encodes binary data as base-64 digits, to an equivalent 8-bit unsigned integer array.
decode_hex value - string Converts hex string to byte array.
encode_base64 input - bytes Encodes byte array to base64-encoded string.
encode_hex input - bytes Encodes byte array to hexadecimal string.
find_string expression - string,
searchString - string,
occurrence - int8		 Returns the 1-based location of the specified occurrence of the search string in the specified string expression.
hex expression - int8 Converts an integer value to its equivalent string representation in hexadecimal format.
is_null_or_empty value - string Checks whether the specified string value is a NULL value or and empty string.
left expression - string,
number - int8		 Returns the specified number of characters from the leftmost part of the specified string.
len expression - string Returns the number of characters in the specified string.
length expression - bytes Returns the total number of elements of the byte array.
lower expression - string Returns a copy of the specified string converted to lowercase.
ltrim expression - string Removes all leading white-space characters from the specified string.
replace expression - string,
searchString - string,
replacementString - string		 Replaces all the occurrences of the search string in the expression string with the replacement string.
replicate expression - string,
times - int8		 Returns the expression string replicated the specified number of times in the expression string with the replacement string.
reverse expression - string Returns the specified string in reverse order.
right expression - string,
number - int8		 Returns the specified number of rightmost characters from the rightmost part of the specified string.
rtrim expression - string Removes all trailing white-space characters from the specified string.
sha256_encrypt data - string or bytes,
salt - string (base64 encoded) or bytes		 Encrypts the specified data with the salt added as a suffix using SHA-256 algorithm.
sha512_encrypt data - string or bytes,
salt - string (base64 encoded) or bytes		 Encrypts the specified data with the salt added as a suffix using SHA-512 algorithm.
substring expression - string,
position - int8,
number - int8		 Returns the specified number of the characters of the expression argument starting from the specified position.
token expression - string,
delimiter - string,
occurrence - int8		 Returns the specified token in the expression string, divided into tokens by the characters, specified in the delimiter string.
token_count expression - string,
delimiter - string		 Returns the number of tokens in the expression string, divided into tokens by the characters, specified in the delimiter string.
trim expression - string Removes all leading and trailing white-space characters from the specified string.
upper expression - string Returns a copy of the specified string converted to uppercase.

You can find some examples of using the string functions in the Examples topic.

Mathematical Functions

Mathematical functions perform various mathematical operations over numeric values. Some of them are analogs of arithmetic operators, like +, -, *, /, %.

Function Parameters Description
abs value - any numeric type Returns the absolute value of an argument. Value has the same type as the argument.
ceiling value - decimal or float8 Returns the smallest integer that is greater than or equal to the argument.
exp value - decimal or float8 Returns an exponent of the argument.
floor value - decimal or float8 Returns the largest integer that is less than or equal to the argument.
ln value - decimal or float8 Returns the natural logarithm of the argument.
log value - decimal or float8 Returns the base-10 logarithm of the argument.
power value - decimal or float8
power - decimal or float8		 Raises the first argument to the power of the second argument.
round value - decimal or float8,
precision - int4		 Rounds the value to the specified precision.
sign value - any numeric type If the argument is less than zero, returns -1. If the argument is greater than zero, returns 1. If the argument is zero, returns zero.
sqrt value - decimal or float8 Returns the square root of the argument.
square value - decimal or float8 Returns the square of the argument.
sub arg1 - any numeric type,
arg2 - any numeric type		 Subtracts the second argument from the first one.

Datetime Functions

Datetime functions perform actions over datetime values.

Function Parameters Description
convert_tz date - datetime,
sourceTimezone - string,
targetTimezone - string		 Converts the specified date from one timezone to another. See the list of timezones here and examples of using timezones here. Note that you need to pass values from the Timezone ID column of the above list to this function.
date_add datepart - string*,
number - int8,
date - datetime		 Adds the specified number to the specified part of the date.
date_diff datepart - string*,
startDate - datetime,
endDate - datetime		 Returns the number of the datepart interval boundaries, crossed between the specified start and end dates.
date_part datepart - string*,
date - datetime		 Returns an integer, representing the specified datepart of the date.
day date - datetime Returns an integer, representing the day datepart of the date.
get_date no parameters Returns the current date.
get_utc_date no parameters Returns the current date in UTC.
month date - datetime Returns an integer, representing the month datepart of the date.
year date - datetime Returns an integer, representing the year datepart of the date.

* The datepart argument is a string, specifying the datetime interval. The following table lists the time intervals and corresponding datepart values. Datepart values are case-insensitive.

Interval Acceptable datepart values
Year yy, yyyy
Quarter qq, q
Month mm, m
Dayofyear dy, y
Day dd, d
Week wk, ww
Weekday dw
Hour hh
Minute mi, n
Second ss, s
Millisecond ms