HarperDB SQL Functions

This SQL keywords reference contains the SQL functions available in HarperDB.

Functions

Aggregate

KeywordSyntaxDescription
AVGAVG(expression)Returns the average of a given numeric expression.
COUNT SELECT COUNT(column_name)
FROM schema.table
WHERE condition;
Returns the number records that match the given criteria. Nulls are not counted.
GROUP_CONCATGROUP_CONCAT(expression) Returns a string with concatenated values that are comma separated and that are non-null from a group. Will return null when there are non-null values.
MAX SELECT MAX(column_name)
FROM schema.table
WHERE condition;
Returns largest value in a specified column.
MIN SELECT MIN(column_name)
FROM schema.table
WHERE condition;
Returns smallest value in a specified column.
SUMSUM(column_name)Returns the sum of the numeric values provided.

Conversion

KeywordSyntaxDescription
CASTCAST(expression AS datatype(length))Converts a value to a specified datatype.
CONVERT CONVERT(data_type(length), expression, style) Converts a value from one datatype to a different, specified datatype.

Date & Time

KeywordSyntaxDescription
CURRENT_DATECURRENT_DATE() Returns the current date in UTC in “YYYY-MM-DD” String format.
CURRENT_TIMECURRENT_TIME() Returns the current time in UTC in “HH:mm:ss.SSS” string format.
CURRENT_TIMESTAMPCURRENT_TIMESTAMP Referencing this variable will evaluate as the current Unix Timestamp in milliseconds. For more information, go here.
DATEDATE([date_string]) Formats and returns the date_string argument in UTC in ‘YYYY-MM-DDTHH:mm:ss.SSSZZ’ string format. If a date_string is not provided, the function will return the current UTC date/time value in the return format defined above. For more information, go here.
DATE_ADDDATE_ADD(date, value, interval) Adds the defined amount of time to the date provided in UTC and returns the resulting Unix Timestamp in milliseconds. Accepted interval values: Either string value (key or shorthand) can be passed as the interval argument. For more information, go here.
DATE_DIFFDATEDIFF(date_1, date_2[, interval]) Returns the difference between the two date values passed based on the interval as a Number. If an interval is not provided, the function will return the difference value in milliseconds. For more information, go here.
DATE_FORMATDATE_FORMAT(date, format) Formats and returns a date value in the String format provided. Find more details on accepted format values in the moment.js docs. For more information, go here.
DATE_SUBDATE_SUB(date, format) Subtracts the defined amount of time from the date provided in UTC and returns the resulting Unix Timestamp in milliseconds. Accepted date_sub interval values- Either string value (key or shorthand) can be passed as the interval argument. For more information, go here.
DAYDAY(date)Return the day of the month for the given date.
DAYOFWEEKDAYOFWEEK(date) Returns the numeric value of the weekday of the date given(“YYYY-MM-DD”).

NOTE: 0=Sunday, 1=Monday, 2=Tuesday, 3=Wednesday, 4=Thursday, 5=Friday, and 6=Saturday.
EXTRACTEXTRACT(date, date_part) Extracts and returns the date_part requested as a String value. Accepted date_part values below show value returned for date = “2020-03-26T15:13:02.041+000” For more information, go here.
GETDATEGETDATE()Returns the current Unix Timestamp in milliseconds.
GET_SERVER_TIMEGET_SERVER_TIME() Returns the current date/time value based on the server’s timezone in `YYYY-MM-DDTHH:mm:ss.SSSZZ` String format.
OFFSET_UTCOFFSET_UTC(date, offset) Returns the UTC date time value with the offset provided included in the return String value formatted as `YYYY-MM-DDTHH:mm:ss.SSSZZ`. The offset argument will be added as minutes unless the value is less than 16 and greater than -16, in which case it will be treated as hours.
NOWNOW()Returns the current Unix Timestamp in milliseconds.
HOURHOUR(datetime)Returns the hour part of a given date in range of 0 to 838.
MINUTEMINUTE(datetime) Returns the minute part of a time/datetime in range of 0 to 59.
MONTHMONTH(date)Returns month part for a specified date in range of 1 to 12.
SECONDSECOND(datetime) Returns the seconds part of a time/datetime in range of 0 to 59.
YEARYEAR(date)Returns the year part for a specified date.

Logical

KeywordSyntaxDescription
IF IF(condition, value_if_true, value_if_false) Returns a value if the condition is true, or another value if the condition is false.
IIF IIF(condition, value_if_true, value_if_false) Returns a value if the condition is true, or another value if the condition is false.
IFNULLIFNULL(expression, alt_value)Returns a specified value if the expression is null.
NULLIFNULLIF(expression_1, expression_2) Returns null if expression_1 is equal to expression_2, if not equal, returns expression_1.

Mathematical

KeywordSyntaxDescription
ABSABS(expression)Returns the absolute value of a given numeric expression.
CEILCEIL(number) Returns integer ceiling, the smallest integer value that is bigger than or equal to a given number.
EXPEXP(number)Returns e to the power of a specified number.
FLOORFLOOR(number) Returns the largest integer value that is smaller than, or equal to, a given number.
RANDOMRANDOM(seed)Returns a pseudo random number.
ROUNDROUND(number,decimal_places) Rounds a given number to a specified number of decimal places.
SQRTSQRT(expression)Returns the square root of an expression.

String

KeywordSyntaxDescription
CONCAT CONCAT(string_1, string_2, ...., string_n) Concatenates, or joins, two or more strings together, resulting in a single string.
CONCAT_WS CONCAT_WS(separator, string_1, string_2, ...., string_n) Concatenates, or joins, two or more strings together with a separator, resulting in a single string.
INSTRINSTR(string_1, string_2) Returns the first position, as an integer, of string_2 within string_1.
LENLEN(string)Returns the length of a string.
LOWERLOWER(string)Converts a string to lower-case.
REGEXP SELECT column_name
FROM schema.table
WHERE column_name REGEXP pattern;
Searches column for matching string against a given regular expression pattern, provided as a string, and returns all matches. If no matches are found, it returns null.
REGEXP_LIKE SELECT column_name
schema.table
WHERE REGEXP_LIKE(column_name, pattern);
Searches column for matching string against a given regular expression pattern, provided as a string, and returns all matches. If no matches are found, it returns null.
REPLACEREPLACE(string, old_string, new_string) Replaces all instances of old_string within new_string, with string.
SUBSTRING SUBSTRING(string, string_position, length_of_substring)Extracts a specified amount of characters from a string.
TRIMTRIM([character(s) FROM] string) Removes leading and trailing spaces, or specified character(s), from a string.
UPPERUPPER(string)Converts a string to upper-case.

Operators

Logical Operators

KeywordSyntaxDescription
BETWEEN SELECT column_name(s)
FROM schema.table
WHERE column_name BETWEEN value_1 AND value_2;
(inclusive) Returns values(numbers, text, or dates) within a given range.
IN SELECT column_name(s)
FROM schema.table
WHERE column_name IN(value(s))
Used to specify multiple values in a WHERE clause.
LIKE SELECT column_name(s)
FROM schema.table
WHERE column_n LIKE pattern;
Searches for a specified pattern within a WHERE clause.

Queries

General

KeywordSyntaxDescription
DISTINCT SELECT DISTINCT column_name(s)
FROM schema.table
Returns only unique values, eliminating duplicate records.
FROMFROM schema.table Used to list the schema(s), table(s), and any joins required for a SQL statement.
GROUP BY SELECT column_name(s)
FROM schema.table
WHERE condition
GROUP BY column_name(s)
ORDER BY column_name(s);
Groups rows that have the same values into summary rows.
HAVING SELECT column_name(s)
FROM schema.table
WHERE condition
GROUP BY column_name(s)
HAVING condition
ORDER BY column_name(s);
Filters data based on a group or aggregate function.
SELECTSELECT column_name(s)
FROM schema.table
Selects data from table.
WHERE SELECT column_name(s)
FROM schema.table
WHERE condition;
Extracts records based on a defined condition.

Joins

KeywordSyntaxDescription
CROSS JOIN SELECT column_name(s)
FROM schema.table_1
CROSS JOIN schema.table_2;
Returns a paired combination of each row from table_1 with row from table_2.

Note: CROSS JOIN can return very large result sets and is generally considered bad practice.
FULL OUTER SELECT column_name(s)
FROM schema.table_1
FULL OUTER JOIN schema.table_2
ON table_1.column_name = table_2.column_name
WHERE condition;
Returns all records when there is a match in either table_1 (left table) or table_2 (right table).
[INNER] JOIN SELECT column_name(s)
FROM schema.table_1
INNER JOIN schema.table_2
ON table_1.column_name = table_2.column_name;
Return only matching records from table_1 (left table) and table_2 (right table). The INNER keyword is optional and does not affect the result.
LEFT [OUTER] JOIN SELECT column_name(s)
FROM schema.table_1
LEFT OUTER JOIN schema.table_2
ON table_1.column_name = table_2.column_name;
Return all records from table_1 (left table) and matching data from table_2 (right table). The OUTER keyword is optional and does not affect the result.
RIGHT [OUTER] JOIN SELECT column_name(s)
FROM schema.table_1
RIGHT OUTER JOIN schema.table_2
ON table_1.column_name = table_2.column_name;
Return all records from table_2 (right table) and matching data from table_1 (left table). The OUTER keyword is optional and does not affect the result.

Predicates

KeywordSyntaxDescription
IS NOT NULL SELECT column_name(s)
FROM schema.table
WHERE column_name IS NOT NULL;
Tests for non-null values.
IS NULL SELECT column_name(s)
FROM schema.table
WHERE column_name IS NULL;
Tests for null values.

Statements

KeywordSyntaxDescription
DELETEDELETE FROM schema.table
WHERE condition;
Deletes existing data from a table.
INSERT INSERT INTO schema.table(column_name(s))
VALUES(value(s));
Inserts new records into a table.
UPDATE UPDATE schema.table
SET column_1 = value_1, column_2 = value_2, ....,
WHERE condition;
Alters existing records in a table.