Here you can find the complete list of built-in functions in Jetspike. You can use them on returned columns and in conditions. Please note that due to certain limitations, all functions must not behave the same and return the same values as their SQL counterparts with the same name.
SELECT name, ROUND(salary)
FROM users
Example of using a function on returned values
SELECT * FROM emulated.users
WHERE LOWER(username) == 'johndoe'
Example of using a function in the condition
String functions
LEFT(<STRING>,<INT>)
returns a substring of defined length from the left side
RIGHT(<STRING>,<INT>)
returns a substring of defined length from the right side
SUBSTRING(<STRING>,<INT>,<INT>)
returns a substring from defined starting position of defined length from the right side
MID(<STRING>,<INT>,<INT>)
equivalent to SUBSTRING
TRIM(<STRING>)
returns string without leading and trailing whitespaces
LOWER(<STRING>)
returns string in lowercase
UPPER(<STRING>)
returns string in uppercase
INITCAP(<STRING>)
capitalizes the first letter and converts the rest of the string to lowercase
CONCAT([<STRING>])
concatenates list of strings
Math functions
ABS(<NUMBER>)
returns absolute value of an number
ROUND(<NUMBER>)
rounds a number to closest integer
CEIL(<NUMBER>)
returns the first integer greater than a number
FLOOR(<NUMBER>)
returns the first integer smaller than a number
SIGN(<NUMBER>)
returns -1 if the number is less than 0, 0 if the number equals 0 and 1 if the number is greater than 0
MOD(<NUMBER>,<NUMBER>)
returns the remainder of a number divided by another number
RAND()
returns a random number between 0 (inclusive) and 1 (exclusive)
Logical functions
IF(<BOOL>,<VALUE>,<VALUE>)
returns a value depending on a condition
IFNULL(<VALUE>,<VALUE>)
if the first value provided is null, returns the second value
Date and time functions
GETDATE()
returns current DateTime
NOW()
equivalent to GETDATE()
DATETIME(<STRING>)
parses a string and returns it as DateTime. Accepts various formats, ie. “2020-01-02 03:04:05.000”
DATE(<DATETIME>)
returns the date part of the DateTime, ie. turns “2020-01-02 03:04:05.000” into “2020-01-02 00:00:00.000”
DATEDIFF(<DATETIME>,<DATETIME>)
returns the difference between two dates in days
DATEPART(<STRING>,<DATETIME>)
returns a part of the date as specified by the first parameter. Unlike in SQL, the first parameter is a string
- “year”, “yy”, “yyyy” – Year
- “month”, “mm”, “m” – Month
- “dayofyear”, “dy”, “y” – Day of year
- “day”, “dd”, “d” – Day of month
- “weekday”, “dw”, “w” – Day of week
- “hour”, “hh” – Hour
- “minute”, “mi”, “n” – Minute
- “second”, “ss”, “s” – Second
- “millisecond”, “ms” – Millisecond
- “microsecond”, “mcs” – Microsecond
- “nanosecond”, “ns” – Nanosecond
Aggregate functions
COUNT(<ARRAY>)
returns the number of non-null elements in an array
MIN([<NUMBER>])
returns the smallest numeric value in an array
MAX([<NUMBER>])
returns the largest numeric value in an array
AVG([<NUMBER>])
returns the average of elements in an array of numeric values
SUM([<NUMBER>])
returns the sum of elements in an array of numeric values
Functions for working with arrays
ARRAY_CONTAINS(<ARRAY>,<VALUE>)
returns true if array contains the value provided as the second parameter
ARRAY_CONTAINS_ANY(<NUMBER>)
returns true if the array provided as the first parameter contains any of the values value provided as the second parameter
ARRAY(<VALUE>,<VALUE>…)
returns an array containing all parameters
Functions for working with maps
MAP(<[STRING],[VALUE]>)
returns a new map from a list of keys and a list of values
DICTKEYS(<MAP>)
rounds the list of keys of a map
DICTVALUES(<MAP>)
rounds the list of values of a map
JSONENCODE(<MAP>)
returns a map encoded as a string
JSONDECODE(<STRING>)
returns a map decoded from a string
Misc functions
NEWID()
returns a new random UUID
Further reading
Built-in functions can be used to add complex logic into your queries. To further enhance your capabilities, you may like some of the following articles.