Built-in functions​

Use built-in functions to add complex logic into your queries

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.